Update Trigger Updates Correct Record In Another Table

Question

Post reply

Update Trigger Updates Correct Record In Another Table

Derek Allan Thomson

SSC Enthusiast

Points: 145
More actions
September 10, 2007 at 7:59 am

#177457

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?

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

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

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 1

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

Anders Pedersen SSChampion Points: 11410 More actions · Answer 2

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.

Phil Factor SSC-Insane Points: 20244 More actions · Answer 3

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

Derek Allan Thomson SSC Enthusiast Points: 145 More actions · Answer 4

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?

nirmal.m SSC Enthusiast Points: 176 More actions · Answer 5

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... 🙂

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

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

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Phil Factor SSC-Insane Points: 20244 More actions · Answer 7

[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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

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)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

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 :)).

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 10

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)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

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?

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 12

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)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

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?

nigelrivett SSCertifiable Points: 5472 More actions · Answer 14

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.