December 15, 2005 at 8:25 am
This is a SQL Server 2000 question.
I'm creating a generalised trigger which loops through all the columns of a changed table to find which have changed. I then want to generate an XML construct which encapsulates only those changed columns.
I'm ok up to the time when I have detected one of the changed columns but when I want to extract the before and after values I'm having trouble. I'm using the syntax.....
SELECT @OldVal = (SELECT @ColName FROM Deleted)
SELECT @NewVal = (SELECT @ColName FROM Inserted)
But, of course this is wrong because it simply returns the value in @ColName rather than the value in the column named by @ColName.
Can anyone help please? btw @ColName is derived from .....
SELECT @ColName = col_name(Object_ID('MyTable'), @ix)
(where @ix is a column counter in a WHILE loop)
Regards
Tony B
December 15, 2005 at 9:02 am
I would not recommend you do it the way you are trying, You Should not create a looping structure. Plus you should always create your triggers to accept datasets, you cannot / should not expect there to be only 1 row inserted at a time.
Triggers are unique to each table, so you do not/should not create a "Dynamic loop"
You did not specify what your output needed to be, but here's a guess.
Select i.pk, 'col1' as changedcolumn, i.col1 as newvalue, d.col1 as oldvalue
from inserted i
join deleted d on i.pk = d.pk (primary key)
where i.col1 <> d.col1
union all
Select i.pk, 'col2', i.col2, d.col2
from inserted i
join deleted d on i.pk = d.pk (primary key)
where i.col2 <> d.col2
union all
Select i.pk, 'col3, i.col3, d.col3
from inserted i
join deleted d on i.pk = d.pk (primary key)
where i.col3 <> d.col3
union all
Select i.pk, 'col4', i.col4, d.col4
from inserted i
join deleted d on i.pk = d.pk (primary key)
where i.col4 <> d.col4
etc......
that will get you the column changed, and the old, and new values.
December 15, 2005 at 10:13 am
Thanks Ray, understand your suggested approach and accept your advice about multi-row updates, but all the same.....
...this is a generalised approach and I have no idea what the column names will ever be, which is why I adopted a loop approach so that it could be retrieved. Could this be implemented in your suggested approach?
At the end of the day, I'm hoping to generate a trigger template which only needs the table name to be configured each time. All these triggers will write to the same audit table with a record-by-record change of every audited table.
I'm new(ish) to triggers so maybe I've missed some other technique that might be more appropriate here e.g. a command which performs a row comparison rather than performing a UNION with all defined columns. I wish, I wish...
Tony B
December 15, 2005 at 12:38 pm
Have been looking for a way to do this myself. So if someone has a solution please post it. Thanks.
BTW, where i.col2 <> d.col2 will NOT give the expected results if one of the columns is null. The statement will always evaluate to FALSE.
ron
December 15, 2005 at 4:43 pm
Generate triggers on fly - not really the best idea.
And how you gonna control datatype matching?
You better create SP to create audit triggers for listed tables. This SP can analyse table(s) schema, generate "CREATE TRIGGER ..." script and execute it.
You can include call to this SP to every deployment script or just set it to be executed every night.
_____________
Code for TallyGenerator
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply