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.
| Author |
Topic |
|
Moussie
Starting Member
20 Posts |
 Posted - 2002-04-25 : 10:55:36
|
| Hello!I need to write an Update trigger that tells me whichfield (that is, a string with the name of the field)was updated in the table. It will basically justlog this field name, the old and new values (converted into varchars) and the variable type into a logtable.Now, this is not a problem if you know the names ofthe fields in the table. But I need the trigger tobe "generic", meaning it has to work on many differenttables, that has a high probability of changing theirschema. I made a table with the output of sp_describe_cursor_columns(this gives you the names of the columns) of the insertedtable (could of course also have used the deleted table,they are equal).Now, what I thought to do was to loop through (yeah,yeah,horribly ineffecient) every column of inserted witha dynamic sql, something along the lines of:set @sql = 'insert #diffs select count(*) from inserted join deleted on deleted.' + @col_name + ' <> inserted.' + @col_name exec (@sql)if (select cols_diff from #diffs) > 0 INSERT @col_name,etc INTO LOG (of course the dynamic sql is different when we have a multiline update)The problem is of course that the dynamic sql is inits own scope and does not know the triggersinserted and updated tables... so much for analready bad idea...Any better ones ?-----Don't think - and you'll walk alot |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-04-25 : 11:23:05
|
| You can try using the COLUMNS_UPDATED() function, but it will give you a bitmask that tells which column NUMBER was updated, not its name. You'd have to do some tricky work, probably with some dynamic SQL and temp tables, to get it to do the job.IMHO, there really isn't any proper way to make a generic trigger to do this, and I don't think you should use them even if you can do it. Making changes to a table's design is not a trivial thing. If people are changing table structures, they SHOULD be forced to modify the triggers accordingly. It's NOT an inconvenience. It forces design changes to be carefully considered, and the side-effects too. Not to mention that someone could add columns that are updated A LOT that you DON'T want audited; you could flood an audit table with meaningless data very quickly.My personal preference is to have a separate audit table for each regular table I want to audit. This audit table has the same structure as the regular table. I know you're thinking "but if I only update one column, all of them get audited". But that gives a record of THE WHOLE ROW at a particular point in time, and it gets ALL of the updated values on one row, instead of several. Trust me when I say, you'll appreciate that more whenever you try to piece together an updated row where each column's value is on separate rows.My 2¢ |
 |
|
|
sgtwilko
Starting Member
23 Posts |
Posted - 2002-12-02 : 14:34:22
|
| I tried to do this about a year ago.It CAN be done, BUT it is very difficult.You need to get the schema of the table you are updating.What is by far easier is this...As you are going to be placing this trigger on many tables, you might as well script the creation of the trigger.So you can customise the trigger so it already knows.The trigger has to be different for every table so you can write it so it knows the names.Basically write a stored proc to write the triggers for you.Another method is insert inserted and deleted into temp tables (## to be global) and then use those in your dynamic sql...This is more difficult as you either need one temp table per table or find a way of making tables on the fly with unique names with out using dynamic sql (because you fall foul of the inserted and deleted problemsIan-- Eagles may soar,but Weasels aren't sucked into jet engines. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-12-03 : 06:44:57
|
| If you look atwww.nigelrivett.comColumns_updated()I think the sample trigger there does something like that.I wrote it a long time ago so can't really remember.Give it a try and let me know how close it is.(Maybe someone would like to add it as a script on this site too).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.Edited by - nr on 12/03/2002 06:47:11 |
|
|
|
|
|
|
|
|
Trigger: Need fieldnames for unknown tableschema