December 4, 2008 at 7:51 am
Hi,
I am trying to write a generic trigger that builds a message about the changed a user made in an edit. So, in the code that deals with updates, I need to compare field by field. As the trigger doesn't know which fields to look at (generic), I decided to use syscolumns and sysobjects to find out the names of all relevant fields. That is working and I'm now looping where a variable @field holds the fieldname to examine.
And the idea is to look at the old value (table 'deleted') and the new value ('inserted'), see if they are different and, if they are, add to a status-msg. But my only idea to get the value of a field whose name is stored in a variable was dynamic SQL:
EXEC sp_executesql N'SELECT @new= @field FROM inserted' , N'@new nvarchar(4000), @field nvarchar(50)',@new,@field
But when doing updates on the table, I now get an error:
>[Error] Script lines: 1-3 --------------------------
> Invalid object name 'inserted'.
I assume the reason is the use of a sp here in which that temp-table is not available.
Is that so, or is there anohther prob? What else could I do to achieve that?
Thanks
Michael
December 4, 2008 at 8:00 am
The inserted and deleted tables are only available within the scope of the trigger and once you do dynamic SQL or call a stored procedure you have created a new scope so the inserted and deleted tables are no longer available.
Because you are using dynamic SQL you would have to either create a global temporary table which wouldn't work well because you could have multiple triggers trying to create or use it at once or a real table.
Could explain a bit more about what you need to accomplish with the trigger? In my opinion, you are usually better off creating specific triggers from a performance and maintenance perspective than trying to do a generic trigger that you place on all tables.
GSquared wrote a couple of good articles on auditing that include some trigger information. You can find the articles here[/url]. I also wrote a basic article on triggers found here[/url].
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 4, 2008 at 8:31 am
Thanks Jack, for the quick reply and also the good links 🙂
The idea is write a log-message that shows the old and new values of the modified fields. There is one trigger for every table, the advantage of having it 'dynamic' would be that any changes to the data-structure would be immediately effecting the trigger, without having to think about subsequently adjusting the trigger.
But I see your point, so maybe I'll write some dynamic SQL instead to build 'static' triggers based on the current definitions... 😉
Thanks
Michael
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply