March 11, 2003 at 6:44 am
I need to determine the columns that are affected in an update trigger. I cannot write the Update() option within the trigger. I need to generically find out the column names. Does it get stored somewhere else? If Inserted table is the only place how can I get the column name programatically
March 11, 2003 at 9:45 am
Why can you not use the UPDATE() statement. I'd be really wary of having a trigger that did not know the columns of the table.
You can always get the columns from information_schema.columns for the table. The inserted and deleted tables mirror that table.
Perhaps you can explain what you are trying to do and we might have some better advice.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 11, 2003 at 11:49 am
Thanks Steve. The idea is to create an dynamic datamart which could be installed over any application database system. So the actual database will not have any triggers written on. The triggers will be created as part of the datamart installation.
The triggers while getting created will have to check for the user tables in the database in question and create (insert/update/delete) triggers on them. Whenever the triggers get executed they should be able to determine the columns affected dynamically and should pass the value to the datamart application which will then make changes in the datamart DB.
The quest for me now is to write a script that will create all the 3 triggers dynamically for all user tables and columns. Of which the first quest is to identify the columns.
Any suggestions?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply