Triggers and temp tables question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Triggers and temp tables question

Author

Topic

CodeChimp
Starting Member

1 Post

Posted - 2009-01-15 : 20:53:33

I have done some googling and been unable to find an answer to these so far.

What is the scope of the Inserted and Deleted temporary tables that are created when an insert/delete/update action is executed against a table? For instance, if I have two triggers attached to the same table and both are set to respond to an insert event and access data in the Inserted table, is there a common Inserted table that both access, or is an Inserted table created for each trigger to use?

Also, if one of the triggers that was fired as a result of an insert action then executes an update action against the table, is another temporary Inserted table generated for that action, or is the current temp Inserted table generated by the insert updated and used?

One more question. Is it possible for an endless loop situation to be caused by a trigger updating it's own table which fires the trigger again? Or are there safeguards in place to guard against this?

Thanks in advance,
CodeChimp

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-15 : 21:59:30

Books online has details:
http://msdn.microsoft.com/en-us/library/ms191300.aspx
and See Nested triggers.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-01-16 : 10:22:20

Also read about RECURSIVE_TRIGGERS:
http://msdn.microsoft.com/en-us/library/ms190739.aspx

the best thing to do in these cases is to test different scenarios for yourself.
For example:


set nocount on
go
create table junk_tb (i int primary key clustered)
go

create trigger tr_junk_tb_1 on junk_tb after insert
as
begin
       set nocount on
       declare @inserted_count int
              ,@junk_tb_count int
              ,@msg varchar(200)
              ,@inserted_maxVal int

       insert junk_tb (i)
       select i + i from inserted

       select @inserted_count = count(*), @inserted_maxVal = max(i) from inserted
       select @junk_tb_count = count(*) from junk_tb

       set @msg = 'tr_junk_tb_1 - @inserted_count=' 
              + convert(varchar, @inserted_count) + '; @junk_tb_count=' 
              + convert(varchar, @junk_tb_count) + '; @inserted_maxVal='
              + convert(varchar, @inserted_maxVal)
       raiserror(@msg, 10, 1) with nowait
end
go

create trigger tr_junk_tb_2 on junk_tb after insert
as
begin

       declare @inserted_count int
              ,@junk_tb_count int
              ,@msg varchar(200)
              ,@inserted_maxVal int

       select @inserted_count = count(*), @inserted_maxVal = max(i) from inserted
       select @junk_tb_count = count(*) from junk_tb

       set @msg = 'tr_junk_tb_2 - @inserted_count=' 
              + convert(varchar, @inserted_count) + '; @junk_tb_count=' 
              + convert(varchar, @junk_tb_count) + '; @inserted_maxVal='
              + convert(varchar, @inserted_maxVal)
       raiserror(@msg, 10, 1) with nowait
end
go
print 'tr_junk_tb_2 is last'
exec sp_settriggerorder @triggername = 'tr_junk_tb_2', @order = 'last', @stmttype = 'insert'
go
insert junk_tb (i)
values (1)

select * from junk_tb
truncate table junk_tb

print 'tr_junk_tb_2 is first'
exec sp_settriggerorder @triggername = 'tr_junk_tb_2', @order = 'first', @stmttype = 'insert'
insert junk_tb (i)
values (1)

select * from junk_tb

go
drop table junk_tb
go

OUTPUT:
tr_junk_tb_2 is last
tr_junk_tb_2 - @inserted_count=1; @junk_tb_count=2; @inserted_maxVal=2
tr_junk_tb_1 - @inserted_count=1; @junk_tb_count=2; @inserted_maxVal=1
tr_junk_tb_2 - @inserted_count=1; @junk_tb_count=2; @inserted_maxVal=1
i
-----------
1
2

tr_junk_tb_2 is first
tr_junk_tb_2 - @inserted_count=1; @junk_tb_count=1; @inserted_maxVal=1
tr_junk_tb_2 - @inserted_count=1; @junk_tb_count=2; @inserted_maxVal=2
tr_junk_tb_1 - @inserted_count=1; @junk_tb_count=2; @inserted_maxVal=1
i
-----------
1
2

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources