Update Trigger Updates Correct Record In Another Table

  • Hi,

    I am quite new to SQL Server and wonder if the following is possible.

    I have an INSERT trigger on a table called "Table" and this is populating "Audit_Table" with the same information in another database.  This bit I can do no problem.

    I want to have an UPDATE trigger on the same tables so that if the user updates a record in "Table" the correct record in "Audit_Table" is updated.  I do not know how to return the unique identifier of the row that is to be updated  The primary key for this table is "Table_ID".  Is this possible and if so, how is it done?

  • You get the value from the "INSERTED" trigger. Meaning something like this:

    create trigger ...

    for update

    as

    update audit_table

    set val = i.val

    from inserted i

    where i.table_id = audit_table.table_id

  • Don't you normally want to insert the values into the audit table?  Updating a value in an audit table kind of defeats the purpose of an audit.  At least in the traditional sense of an audit.

  • Pop Rivett's SQL Server FAQ No.5: Pop on the Audit Trail is a popular introductory article on the technique

    Best wishes,
    Phil Factor

  • The "Table" table will potentially contain several thousand records before the batch process tidies up the table at the end of the week.  The solution offered will update every row in the "Audit_Table".  Is there a way to update one record in the "Audit_Table" instead of updating all records?

  • Usually in Audit_Table a timestamp column is maintained.

    So when a record is updated in "Table" the udpated record is inserted as new record

    along with the timestamp.

    This would help you to have the old value and also the new value.

    It could be done like this

    Create Trigger [trigger name] on

    for update

    as

    Insert into Audit_Table

    Select *, getdate() from Table

    Hope this helps... 🙂

  • Derek Allan Thomson (9/11/2007)


    The "Table" table will potentially contain several thousand records before the batch process tidies up the table at the end of the week. The solution offered will update every row in the "Audit_Table". Is there a way to update one record in the "Audit_Table" instead of updating all records?

    Sure you can update 1 record during a batch that updates millions if you have a way to identify the correct record. Something like:

    UPdate A

    Set Val = I.Val

    FRom

    audit_table A JOIN

    inserted I On

    A.id = I.id

    Where

    A.id = 10

  • [p]I think that everyone who has responed to this thread are correct, in that for a production system one would never want to update a record in an audit table, Every change to the table that is being audited must be represented in the audit table by a new date-stamped record. The audit table is really a special log for events on a table, and you don't update a log and subsequently get to heaven. After all, they are supposed to provide a cross-check to verify a transaction.[/p]

    [p]I can't see how you can comply with ITIL (IT Infrastructure Library) recommendations for an audit trail for financial transactions otherwise. If this is a system with any financial implications then you'd need to check with your auditors that whatever you are planning is OK. If not financial, you probably do not need an auditing mechanism![/p]

    Best wishes,
    Phil Factor

  • Phil Factor (9/11/2007)


    is a popular introductory article on the technique

    Well, I'll be damned... Now I know who to blame... 😀 (NOT Phil... Pop Rivet!)

    I've had a bit of a nightmare helping various folks overcome the problem of PK's in their audit tables that look like 'i=3 j=3' (couldn't show the brackets on this forum), that auditing Inserts is simply insane (more than doubles the storage required), that it takes over 4 seconds to do 3 inserts, 4 updates, and one full delete of 3 rows, and that GUI's are blowing up because SET NOCOUNT ON wasn't used in their triggers.

    Add to that the trigger will likely blow up if Text, NText, or Image columns are present and you have a complete package of things to avoid for Audit Triggers. :sick:

    If you want a generic solution for triggers, that code isn't it. Write some code that will generate STATIC code for the trigger and then use that static code... general purpose code will never be as fast as single purpose code.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Just one question on that Jeff. How do you track the information of When, what, who inserted the original row?

    I know this data should be kept separate from the audited data, so that's why I audit it with the rest (also I have about as many transactions in a day that you get in a sec, so I can afford :)).

  • Ninja's_RGR'us (9/29/2007)


    Just one question on that Jeff. How do you track the information of When, what, who inserted the original row?

    I know this data should be kept separate from the audited data, so that's why I audit it with the rest (also I have about as many transactions in a day that you get in a sec, so I can afford :)).

    Heh... it's IN the original table... why do you need to store it in another table for? If you want, add a LastModifiedBy/LastModifiedOn column... lot's better than the overhead and storage required to store the original data in an audit log.

    Consider the other thing... what is the purpose of an audit log? Is it to store the original data? No, that's what the original table is for... the purpose of an audit log is to store modifications to the data.

    And, please... no one create a trigger to store changes to LastModified columns, eh?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Playing Devil's Advocate >>

    You think it's better to keep 2-3-4 more columns in the base table for the original insert, then copy all that data everytime something gets updated into the audit log?

    Kind of costly on a 2 cols table don't you think?

  • Sorry... temporarily lost my mind... I meant CreatedBy and CreatedOn... not "Modified". 🙂

    You think it's better to store all data twice on insert?... Once "normal sized" in the original table and once 4 to 6 times the size in the audit table?

    Consider this... if I have a 6 column table of all INTs and the CreatedBy/On columns, the row size is "estimated" as follows (according to "Estimating the size of a table" in BOL) assuming each column is 50% "full" and not counting any indexes or keys...

    4 INT columns = 4 Bytes * 4 = 16 Bytes (fixed size, full or not)

    1 Varchar(32) for CreatedBy = 16 Bytes at 50% full

    1 DateTime for CreatedOn = 8 Bytes (fixed size, full or not)

    Null_Bitmap = 2+((NumFixedLenCols+7)/8) = 2+((5+7)/8) = 4 bytes(rounded up)

    Variable_Data_Size = 2+(Num_Variable_Cols x 2) = 2+(1x2) = 3 bytes

    Total Size of original table row = 16+16+8+4+3 = 47 bytes per row

    Let's calculate the same for the audit table...

    RowNum(PK) col = 4 bytes (absolutely required to guarantee uniqueness)

    PrimaryKey col = 4 bytes (assuming all primary keys are INT, could be worse)

    ColumnName = 16 bytes average in a VarChar(128) (NVarchar would be double)

    OrigValue = 1 bytes for a Null in, say, a VarChar(1000)

    NewValue = 7 bytes average in, say, a VarChar(1000)

    Type = 1 byte in a CHAR(1)

    ModifiedOn = 8 bytes in a DateTime

    ModifiedBy = 16 bytes average in a Varchar(32)

    Null_Bitmap = 2+((NumFixedLenCols+7)/8) = 2+((4+7)/8) = 4 bytes(rounded up)

    Variable_Data_Size = 2+(Num_Variable_Cols x 2) = 2+(4x2) = 10 bytes

    Total size of audit table row = 4+4+16+1+7+1+8+16+4+10 = 71 bytes per row

    BUT!!!!! There are 4 "siginificant" columns in the original table, 1 of which is the primary key. That means that if I insert just 1 new row in the original table, the audit table must record 3 rows, 1 for each non-PK significant column. That's 213 bytes in the audit table to record 47 bytes of information for a total of 260 bytes between the two tables.

    If I don't record inserts in the audit table, it's only 47 bytes instead of 260 bytes. That's almost a 5:1 savings in disk space and "write" time. Even if I added ModifiedBy/On to the original table, I still get a 4:1 savings in disk space and write time by NOT auditing Inserts.

    Like I originally said, auditing Inserts is insane :hehe:

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Sorry Jeff, but I only audit full rows, not columns sot he 5:1 ratio doesn't apply.

    2 what do you do about the rule that says that auditing data should be kept separate from the base table and not accessible to the users?

  • Jeff Moden (9/29/2007)


    Phil Factor (9/11/2007)


    is a popular introductory article on the technique

    Well, I'll be damned... Now I know who to blame... 😀 (NOT Phil... Pop Rivet!)

    I've had a bit of a nightmare helping various folks overcome the problem of PK's in their audit tables that look like 'i=3 j=3' (couldn't show the brackets on this forum), that auditing Inserts is simply insane (more than doubles the storage required), that it takes over 4 seconds to do 3 inserts, 4 updates, and one full delete of 3 rows, and that GUI's are blowing up because SET NOCOUNT ON wasn't used in their triggers.

    Add to that the trigger will likely blow up if Text, NText, or Image columns are present and you have a complete package of things to avoid for Audit Triggers. :sick:

    If you want a generic solution for triggers, that code isn't it. Write some code that will generate STATIC code for the trigger and then use that static code... general purpose code will never be as fast as single purpose code.

    You need to look at the system being audited. There are a lot of different methods that can be used.

    That code is for low volume tables and to give a simply implemented audit. It doesn't have to be used for all tables in the database - you can cherry pick. For anything that needs performance I would expect people to know how to create audit trails.

    Blobs are always going to cause problems with after triggers.

    There's some more about auditing options here

    http://www.nigelrivett.net/SQLTriggers/Triggers_2_Creating_Audit_Trails.html

    I usually wouldn't implement it in triggers if performance was an issue.


    Cursors never.
    DTS - only when needed and never to control.

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply