:new :old values on update trigger

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 2000 Forums

Transact-SQL (2000)

:new :old values on update trigger

Author

Topic

GodSend
Starting Member

3 Posts

Posted - 2004-07-20 : 20:10:04

Hi Guys, first post, and its probably a question thats been answered many times, but my forum search proved unfruitful. I am attempting to log all the updates performed on a particular table by using a trigger. I have everything working except recording the old & new values. To complicate things slightly the software I am logging specifies EVERY field in the table, regarless of weather or not it actually changed. So my problem is two fold. Getting the new and old values, and then compairing them (which is the easy part).

Here is my code that competes everything except logging the new, old values. :

create trigger CustomerAuditUpdateTrigger
on dbo.Orders
for update
As
DECLARE @column VarChar(255)
DECLARE @old VarChar(255)
DECLARE @new VarChar(255)

declare @bit int ,
@field int ,
@char int ,
@table int

select @table = (select id from sysobjects where name = 'orders')

select @field = 0
while @field < (select max(colid) from syscolumns where id = @table)
begin
select @field = @field + 1
select @bit = (@field - 1 )% 8 + 1
select @bit = power(2,@bit - 1)
select @char = ((@field - 1) / 8) + 1

--select @char, @field, @bit -- debug code to check the bits that are tested.
if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0
select @field, name from syscolumns where colid = @field and id = (select id from sysobjects where name = 'orders')
Insert into Activity_Audit (date,computer_id, [user], [table], [action],identifier) values (getDate(), host_name(), User_Name(),'Orders','Update', COL_NAME(@table,@field))
end

I am sure there are better ways of performing the above code, so I will take suggestions on that as well :). Thanks in advance guys!

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-07-20 : 20:13:56

To get the old and new values, you need to query the special tables named inserted and deleted which only exist for the duration of the trigger. Also, I'm concerned that your trigger as it is written may not handle multiple records being updated in one shot. In this case, the trigger is executed only once with all the rows included in inserted and deleted.

-----------------------------------------------------
Words of Wisdom from AjarnMark, owner of Infoneering

GodSend
Starting Member

3 Posts

Posted - 2004-07-20 : 22:56:16

quote:
Originally posted by AjarnMark

To get the old and new values, you need to query the special tables named inserted and deleted which only exist for the duration of the trigger. Also, I'm concerned that your trigger as it is written may not handle multiple records being updated in one shot. In this case, the trigger is executed only once with all the rows included in inserted and deleted.

Understood about the inserted and deleted, however I can not seem to find schemas for those temporary tables (or does the schema match that of the table being updated?).

As far as the multiple record update, as far as I know a single entry per update is most likly what I will want, as all the updates SHOULD occur on only 1 record :).

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-20 : 23:26:56

The schema matches the tables being updated, inserted, or deleted from.

On the update part you never, ever want to plan on there being only one update. It doesn't take that much to plan for multiples. If you are going to do this, put a count in the table. If more then one record is updated, then raiserror and abort the transaction.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

GodSend
Starting Member

3 Posts

Posted - 2004-07-21 : 10:00:27

Thanks guys. I figured out the schema, however I seem to be stuck on grabbing that data out of the temp. tables. The following (place right before the insert into the log database) works fine :

select @new = (Select order_id from inserted)
select @old = (select order_id from deleted)

except that I want to dynamically populate the field (in this case order_id) in the above query. I tried the following two things :

select @new = (Select @field from inserted)
select @old = (select col_name(@table, @field) from deleted)

The first one set @new = 1 (the column location of order_id) and the second one set @old = "order_id" , the name of the column rather then the actual value contained within it.

Any suggestions on how I might accomplish this? Cheers.

Kristen
Test

22859 Posts

Posted - 2004-07-21 : 14:07:27

If I've understood this correctly you have a single table which stores Column Name, Old Value, New Value, and you want to establish which columns have changed on each row that is processed, and then insert them in a Log Table.

If so I did this for a client many years ago (against my better judgement! I prefer to store whole-rows on a change, rather than changed columns)

The approach I took was to script the trigger to process every column in the table (i.e. WITHOUT the loop logic). This performed very quickly because SQL was able to cache the query plan. The trigger scripts where hudge (about 20 tables were audited, and the script was many megabytes big)

I built the triggers by writing a SQL Query that looked at syscolumns and output "template formatted" SQL.

Might be a valid approach for you too, just thought I'd mention it in case.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources