Reputation: 101
I'm having the following issue: I have a trigger on a table A, whose purpose is to compute some values and insert them in a completely different table B.
The problem is that, somewhere in that logic, there is a loop that requires the values that would have been freshly inserted into table B.
I've noticed that SQL Server executes all the INSERT
commands at once, after exiting the trigger.
ALTER TRIGGER [dbo].[InsertTrade]
ON [dbo].[Blotter]
AFTER INSERT
AS
BEGIN
/* compute @Variables */
INSERT INTO [dbo].[CompletelyUnrelatedTableWithoutTriggersOnIt]
VALUES @Variables
Is there any way of COMMMIT
-ing that INSERT
and being able to read those values while still in the trigger?
Thanks, D.
Upvotes: 0
Views: 199
Reputation: 24032
First of all, be very careful with how you are constructing your trigger. If you're using INSERT...VALUES()
in a trigger, it's a good indication that you're assuming there will only ever be one record in the INSERTED
table. Never make that assumption. Instead your logic should be INSERT...SELECT <computed cols> FROM INSERTED
Second, if you want to get out the values you just put in, you could use the OUTPUT clause but I'm not sure that's what you mean (it's not entirely clear what you want to do with the values) then you will have access to the final values that were inserted "while still in the trigger"
If that's not what you want, perhaps it would be better to encapsulate all this functionality into a proc.
Upvotes: 1