February 12, 2009 at 3:55 pm
Consider the following within the Insert Trigger on a table:
declare @key_id int
select @key_id = col1 from inserted
exec sp_add_to_log @key_id, 'Added'
Is there any way to directly access the INSERTED column on the EXEC and not have to use the interim variable?
I tried variations such as these without any success:
exec sp_add_to_log inserted.col1, 'Added'
exec sp_add_to_log (select col1 from inserted), 'Added'
Any thoughts?
February 12, 2009 at 4:32 pm
Two things. First off, the syntax you've proposed will not work. You cannot pass a DML statement as a parameter to a stored procedure. Secondly, you don't want to go the route of assigning the value from the INSERTED table into a variable either. This method will not work like to you want it to as soon as you attempt to insert multiple rows into your table.
What does your add to log SP do?
February 12, 2009 at 5:08 pm
[font="Verdana"]Ah...
One thing you may not realise is that a trigger only fires the once for a set of data. So if you insert 10 rows in one transaction, the trigger will only fire once, and inserted will contain 10 rows.
So if you absolutely need to process each row by calling a stored procedure, you will need to loop through the rows in inserted, in which case you will need to load them into variables anyway.
Personally, I think you should reconsider your design and try to use gateway stored procedures to do the work, rather than relying on triggers.
[/font]
February 12, 2009 at 5:10 pm
If you explain what you want to do, we might have other ideas as well.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply