May 14, 2015 at 4:56 am
Hi All,
I have a problem described as follows: I have a table with one instead of insert trigger:
create table TMessage (ID int identity(1,1), dscp varchar(50))
GO
Alter trigger tr_tmessage on tmessage
instead of insert
as
--Set NoCount On
insert into tmessage
select dscp from inserted
GO
Alter proc P1
As
--Set NoCount On
Declare @T1 as Table(Id int)
insert into tmessage
(dscp)
output inserted.ID into @T1
values('test')
Select * from @T1
GO
When I execute P1 it returns 0 for Id field of @T1.
How can I get the Identity in this case?
Any help would be appreciated.
PS: I can not use Ident_Current or @@identity as the table insertion hit is very high and can be done concurrently.
Also there are some more insertion into different tables in the trigger code, so can not use @@identity either.
May 14, 2015 at 7:05 am
BOL:
Triggers
Columns returned from OUTPUT reflect the data as it is after the INSERT, UPDATE, or DELETE statement has completed but before triggers are executed.
For INSTEAD OF triggers, the returned results are generated as if the INSERT, UPDATE, or DELETE had actually occurred, even if no modifications take place as the result of the trigger operation.
https://msdn.microsoft.com/en-us/library/ms177564.aspx
So no identity is computed at the moment OUTPUT returns data in this case, because it is really computed in the instead of trigger.
May 14, 2015 at 7:33 am
Try move OUTPUT into trigger and use temp table created in the proc.
ALTER trigger tr_tmessage on tmessage
instead of insert
as
--Set NoCount On
insert into tmessage
OUTPUT inserted.ID INTO #tt
select dscp from inserted
GO
--
ALTER proc P1
As
--Set NoCount On
create table #tt ( id int);
--Declare @T1 as Table(Id int)
insert into tmessage (dscp)
values('test'),('moretest')
Select * from #tt
GO
May 14, 2015 at 8:04 am
Thanks for the answer. I had tried to create the temp table in the trigger and then use it up in the proc. but it did not work. but if we, as you said, create it on the caller proc and use it on the trigger, it works. great.
Can you just please tell me why it works in that way and does not work the other way. What are the rules for using temp tables across different scopes,like procs, triggers, etc, in the same session?
May 14, 2015 at 8:06 am
Thanks for the answer. I had tried to create the temp table in the trigger and then use it up in the proc. but it did not work. but if we, as you said, create it on the caller proc and use it on the trigger, it works. great.
Can you just please tell me why it works in that way and does not work the other way. What are the rules for using temp tables across different scopes,like procs, triggers, etc, in the same session?
May 14, 2015 at 8:27 am
See Remarks in CREATE TABLE https://technet.microsoft.com/en-us/library/ms174979.aspx
Trigger is executed within the scope where insert is emitted which is essentially scope of PROCEDURE here. And trigger has its own scope. Trigger has access to the outer scope temporary objects.
Temporary tables are automatically dropped when they go out of scope, so this one created in the trigger is lost when returned to procedure.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply