April 26, 2006 at 1:44 am
Hi,
I'm trying to set up an audit trail of amendments on fields that a customer chooses to monitor.
The field names for the fields that a customer wants to monitor are held in a config table. My intention was to create an UPDATE trigger on the Job table to recover the list of monitored fields then loop through them inserting a record into an audit trail table containing the old, new, user, date & time.
I ran into a problem when trying to dynamically recover the old and new values from INSERTED & DELETED. (See below for what I was trying to do).
Unfortunately I was only getting the fieldname value from the config table, now the actual old and new values from the deleted/inserted tables.
SELECT @OLD = @DBFieldName FROM DELETED
SELECT @NEW = @DBFieldName FROM INSERTED
Am I making any sense? I'd appreciate any feedback.
Thanks,
Kevin.
Windows 2008 Server | SQL Server 2008
April 26, 2006 at 6:05 am
The deleted and inserted tables are copies of the table the trigger is placed on that is accessible only from within a trigger. You use them just like any other normal table.
(ie select @var1 = col1 from inserted) etc.
Be careful when designing your triggers to be able to handle multiple rows if that is necessary though.
/Kenneth
April 26, 2006 at 6:26 am
Hi Kenneth, thanks for the reply.
I understand that part of how triggers work. Unfortunatley I wanted to be able to dynamically select the value of a column from the INSERTED/DELETED tables by passing it's name as a parameter. I wanted to do this because our customers wanted to monitor diffrerent fields therefore I couldn't hard code them into the trigger.
I've been doing a bit more reading and have come accross several people who have tried to do the same thing as me. It turns out that SQL Server 2000 doesn't allow to refer to a column name as a variable so I'll have to think of another method of doing what I want.
Thanks for your time. Much appreciated.
Kevin.
Windows 2008 Server | SQL Server 2008
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply