Update Trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update Trigger

Author

Topic

akashenk
Posting Yak Master

111 Posts

Posted - 2009-10-12 : 16:56:01

I am implementing some auditing functionality and would like to use an After Update trigger as opposed to inline T-SQL. When a particular database table has a record changed through an UPDATE statement, I wish to log some info in another table. Coding the after update trigger is straightforward enough, but I have a question regarding obtaining info about what was updated.

Is there a way, within the trigger to find out which column(s) if any were updated. At a minimum, I would like to be able to check if any data was actually updated. If not, then I would not log the event.

In a more extensive implementation, I would like to be able to make a note of A) what column(s)were changed, and B) what the change was.

Any help would be greatly appreciated. Thanks!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-12 : 17:08:01

You can either compare the values (column by column) between the virtual tables (Inserted and Deleted) or you can make use of these functions: UPDATE() and COLUMNS_UPDATED (see Books Online) http://technet.microsoft.com/en-us/library/ms186329%28SQL.90%29.aspx

I believe those functions will return true even if your value was set to itself if that is a possible scenario then you should use the inserted/deleted comparisons.

Be One with the Optimizer
TG

akashenk
Posting Yak Master

111 Posts

Posted - 2009-10-12 : 20:24:43

I am unfamiliar with any particularly efficient way of comparing the values in the deleted and inserted tables other than setting a variable for each column, setting its value from the deleted table, and comparing the variable to the value in the inserted table. It seems this would be a problematic way of doing the comparison, particularly in a trigger which could be called hundreds of times as a result of one statement. Am I missing something or is there a better way of comparing the values in the two tables to see if a change was made.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-13 : 10:32:22

sure - but first let me make one correction to your statement becuase it is an important distinction:
>>particularly in a trigger which could be called hundreds of times as a result of one statement<<

The trigger will only fire ONCE when multiple rows are affected. The inserted and/or deleted tables will have hundreds of rows in them but the trigger will only fire once. That is why you should not use variables in triggers. You would either only get one value or you would be forced to loop through the entire result set setting the variables for each iteration. Both options are bad.

The actual statement to insert your log table obviously depends on how the table is structured but basically (for an AFTER UPDATE trigger) you would JOIN the inserted and deleted tables by the primary key of the table with the trigger. Then in the WHERE clause you could compare the columns, the SELECT clause could include CASE expressions to conditionally populate your log with the appropriate values. If you post your table structures (trigger table and log table) as well as your code so far we can help if you need it...

Be One with the Optimizer
TG

akashenk
Posting Yak Master

111 Posts

Posted - 2009-10-13 : 11:00:12

Thanks for the clarification on the trigger functionality. I think it would suffice to use a simple example. Let's say the table being updated has the following columns:

Person_ID nchar(10) PRIMARY KEY
FirstName nvarchar(35)
LastName nvarchar(40)

I'm not quite sure how the join of the deleted and inserted tables within the trigger (on Person_Id) can be used to compare the values. Would it be:

"WHERE inserted.firstname <> deleted.firstname OR inserted.lastname <> deleted.lastname"

This is where some guidance would be great. If you could provide a simple example of the use of CASE in this scenario, that would be helpful as well. Thanks!

FYI, the log table has the following structure:

Record_ID nchar(10)
TableName varchar(40)
EventCode smallint
LoggedWhen datetime

An example update of the log for a given trigger execution would be
INSERT MylogTable (Record_ID, Tablename, eventcode, loggedwhen)
SELECT inserted.Person_ID, 'MyPersonsTable', 1, GETUTCDATE()
FROM inserted

The number 1 for event code corresponds to an "Updated" status. If the record wasn't updated, no log entry would be made. Obviously this code I have provided won't work because not all records in the inserted table need to have corresponding records created in the log table, but I assume your case example takes care of that.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-13 : 11:30:28

So are you using a single log table to log changes across multiple tables? Not sure how far along in the design process you are but that design may cause a lot more blocking especially when any of the tables have a lot of activity associated with it and even more if there are lots of reads of the log table. A more typical audit model is one audit table per subject table.

But anyway, to answer your question, since you have no columns in the log table that specify which subject table columns have changed and/or old and new values then you don't need a CASE expression. It is simply the WHERE clause to only include rows that have any ANY values change. So I would suggest something like:

Where NOT ( i.FirstName = d.FirstName AND i.LastName = d.LastName )

EDIT:
forgot to mention that you need to take into account NULLABLE columns.
ie:
Where NOT ( isNull(i.FirstName,'null') = isNull(d.FirstName,'null) AND...)

Be One with the Optimizer
TG

akashenk
Posting Yak Master

111 Posts

Posted - 2009-10-13 : 11:58:29

Actually I am near the begining of the design of the Audit table structure, so a modification would be ok. The only thing I would be concerned about with using a seperate audit table for each subject table is that it might lead to a very large number of tables, all of which have pretty much the same structure. I though I would stick to relational principles as much as possible (hense the single table), but you are right, it could cause blocking issues.

I omitted the part about tracking the specific changes from my example because I thought it would muddle things. However, in the not-to-distant future I would like the ability of tracking the specific change, so the CASE example would be appreciated. I think this would require the addition of two fields to the audit tabe (or tables if I change the architecture). One field for the original content and one for the changed content. I guess since this field will list changes for any of the rows, I will have to delimit the data in these fields per column of the subject table that can change.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-13 : 13:37:52

Ok, if you want to have one audit row for each column value change then that means we will be generating rows. ie: if one row has three columns updated then we need 3 rows of output from the one row of inserted/deleted. Since you have one after update trigger per subject table then this is pretty easy. Here's one way to audit all Insers/Updates/Deletes with one row per column changed with one trigger.

My advice would be to use one audit table per table and one row per changed row. That way you don't need to deal with converting datatypes (or sql_variant) for the changed values, you have less blocking and easier audit queries to read the log.


use tempdb
set nocount on
go

create table person 
       (personid int identity(1,1) primary key clustered
       ,FirstName varchar(20) null
       ,LastName varchar(20) not null)
go
create table logTable 
       (Record_ID nchar(10) not null
       ,TableName varchar(20) not null
       ,EventCode int not null
       ,changeColumn varchar(20) not null
       ,OldValue sql_Variant null
       ,NewValue sql_variant null
       ,LogDt datetime)
go

create trigger tr_iud_person on person after insert, update, delete
as
begin
       insert logTable (Record_ID, TableName, EventCode, changeColumn, OldValue, NewValue, LogDt)
       select convert(nchar(10), coalesce(i.personid, d.personid))    as Recored_ID
              ,'person'                                               as TableName
              ,case 
                     when d.personid is null then 1 --Insert
                     when i.personid is null then 2 --Delete
                     else 3 --Update
                     end                                              as EventCode
              ,case 
                     when n.number = 1 then 'LastName'
                     when n.number = 2 then 'FirstName'
                     end                                              as ChangeColumn
              ,case 
                     when n.number = 1 then d.LastName
                     when n.number = 2 then d.FirstName
                     end                                              as OldValue
              ,case 
                     when n.number = 1 then i.LastName
                     when n.number = 2 then i.FirstName
                     end                                              as NewValue
              ,getdate()                                              as LogDt
       from   inserted i
       full outer join deleted d
              on d.personid = i.personid
       inner join (--a [numbers] table or function so we can generate one row per changed column
              select 1 number union all select 2 union all select 3
                  ) as n
               on n.number <= 2 --number of colums we are comparing
       where  --oldValue
              coalesce(case 
                     when n.number = 1 then d.LastName
                     when n.number = 2 then d.FirstName
                     end, 'null')
              !=
              --newValue
              coalesce(case 
                     when n.number = 1 then i.LastName
                     when n.number = 2 then i.FirstName
                     end, 'null')
end 
go

insert person(LastName, firstName)
select 'LastName1', 'firstName1' union all
select 'LastName2', 'firstName2' union all
select 'LastName3', 'firstName3' union all
select 'LastName4', 'firstName4'
order by 2

waitfor delay '00:00:00.010'

update person set
       firstName = 'NewfirstName2'
      -- ,LastName = 'NewLastName2'
where  personid = 2

waitfor delay '00:00:00.010'

delete person where personid = 2

select * from LogTable order by LogDt, Record_ID, changeColumn
go
drop table person
drop table LogTable

output

Record_ID  TableName            EventCode   changeColumn         OldValue            NewValue           LogDt
---------- -------------------- ----------- -------------------- --------------------------------------------------------------
1          person               1           FirstName            NULL                firstName1         2009-10-13 13:36:30.267
1          person               1           LastName             NULL                LastName1          2009-10-13 13:36:30.267
2          person               1           FirstName            NULL                firstName2         2009-10-13 13:36:30.267
2          person               1           LastName             NULL                LastName2          2009-10-13 13:36:30.267
3          person               1           FirstName            NULL                firstName3         2009-10-13 13:36:30.267
3          person               1           LastName             NULL                LastName3          2009-10-13 13:36:30.267
4          person               1           FirstName            NULL                firstName4         2009-10-13 13:36:30.267
4          person               1           LastName             NULL                LastName4          2009-10-13 13:36:30.267
2          person               3           FirstName            firstName2          NewfirstName2      2009-10-13 13:36:30.283
2          person               2           FirstName            NewfirstName2       NULL               2009-10-13 13:36:30.300
2          person               2           LastName             NewLastName2        NULL               2009-10-13 13:36:30.300

EDIT:
modified the WHERE clause to only log changed columns.
And modified the OUTPUT to reflect the change

Be One with the Optimizer
TG

akashenk
Posting Yak Master

111 Posts

Posted - 2009-10-13 : 14:37:37

This is very cool. One thing though, you mention that it would be your suggesiton to use a single audit table row for each subject table event, even if multiple columns are changed. Your example creates a new row for each column that has a change. If I were to do it the other way, am I correct in assuming I simply need to change the OldValue and NewValue fields to something like nvarchar(max), and then update these fields with delimited strings representing each column's value. Here is an example

if my row starts off as:

'PER_1', 'Jane', 'Smith'

and after update becomes:

'PER_1', 'Jane', 'Jones'

Then my audit row might look like:

'PER_1', 'PersonTable', 1, 'Jane|Smith', 'Jane|Jones', '10/14/09...'

I would just have to choose a delimiter which could not appear in the column data (some sequence of characters a little more complex than just a single pipe, as in my example) and also make sure to include nulls within the delimited string so the number of segments is the same even if there is no data for a particular column

Please let me know if this was not what you had in mind by creating a single audit row for each update

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-13 : 14:49:49

Actually, the structure of the audit table is the same as the structure of the subject table. The only difference is some added columns like Audit_By, Audit_Dt, Audit_action. so rather than new and old values you compare the entire row to the previous row for the changes. So for your example the audit table might look like: ([userid] would be the ID of the person that made the change)


Audit_Person table:
AuditID   personid    LastName    firstName  userid      audit_dt                   audit_action
-------   ----------  --------    --------  ----------   ---------------------      ------------
1         1           Smith       Jane        1234       2009-10-13 14:47:12.000     Insert
2         1           Jones       Jane        1234       2009-10-13 14:49:11.000     Update
3         1           Jones       Jane        1234       2009-10-13 15:49:10.000     Delete

Be One with the Optimizer
TG

akashenk
Posting Yak Master

111 Posts

Posted - 2009-10-14 : 12:26:06

Thanks for the suggestions. Though your strategy seems like it would lead to a very large number of records and some duplicatavie data, it certainly is all-encompassing and would represent a complete audit of the goings on in a table. If you don't mind, I have a couple questions regarding your architecture..

1) In your trigger, your WHERE clause is used to determine if a change was made in one of the data columns. In my real implementation, there are a fair number more columns than in the simple example I used for demonstration purposes here. In my case, the WHERE clause actually looks more like the following:

WHERE NOT (coalesce(i.emailaddress_id, 'null') = coalesce(d.emailaddress_id, 'null')
        and coalesce(i.firstname, 'null') = coalesce(d.firstname, 'null')
		and coalesce(i.lastname, 'null') = coalesce(d.lastname, 'null')
		and coalesce(i.name, 'null') = coalesce(d.name, 'null')
		and coalesce(i.address, 'null') = coalesce(d.address, 'null')
		and coalesce(i.address2, 'null') = coalesce(d.address2, 'null')
		and coalesce(i.city, 'null') = coalesce(d.city, 'null')
		and coalesce(i.state, 'null') = coalesce(d.state, 'null')
		and coalesce(i.zipcode, 'null') = coalesce(d.zipcode, 'null')
		and coalesce(i.zipplus4, 'null') = coalesce(d.zipplus4, 'null')
		and coalesce(i.companyname, 'null') = coalesce(d.companyname, 'null'))

Should I have any concern about the performance of this trigger due to the lengthy WHERE clause?

2) In your architecture, you specify a user_ID corresponsing to the person who made the update/insert, etc. This is a good idea and certainly something I have thought about, however, I'm not quite sure how I would pass this information to the trigger. When the UPDATE/INSERT/DELETE is called, I have access to this id because it is a parameter that I pass to the stored procedure which implements the change. If I were executing an addition SP to store the audit info, I could obviously pass the userId that way, but in this architecture I am using a trigger instead. Can you suggest a way of obtaining the user id within the trigger?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-14 : 12:57:54

Now that I think about it, if you go with the 2 columns (oldValue, newValue) then I guess your WHERE clause should just check for differences between the oldValue expression and the newValue expression (each expression wrapped in a coalesce in case one value is null). That way only the specific column that changed would be recorded in the log.

I think there's two ways you can get the userid to the trigger. One is to add the column to the subject table (ie: LastModifiedBy). The other way is to have the SP encode the id into binary and SET Context_Info to the value. The trigger can then read the value using: select context_info()

Be One with the Optimizer
TG

akashenk
Posting Yak Master

111 Posts

Posted - 2009-10-15 : 23:32:40

I have implemented the LastModifiedBy Field and it works like a charm. Thanks for the suggestion.

I am a little lost as to your comments about the WHERE clause. I am getting ready to implement the change tracking functionality. Are you saying the code you suggested with the case statements would not work well with about 10 columns being tracked?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-16 : 10:15:17

>>I am a little lost as to your comments about the WHERE clause
I modified the trigger code in my earlier post to reflect the "new" WHERE clause criteria so only changed column value are put in the log.

>>Are you saying the code you suggested with the case statements would not work well with about 10 columns being tracked?
No. Audit logging by its nature will have some impact on performance but the CASE statement shouldn't be a big impact.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources