Trigger update - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Trigger update

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-25 : 12:22:32

Monys writes "I´ve got a trigger (Sql server 2000) who does not fire complete, it has two updates in different tables, it works in the first table, but when it go to the second update, it doesn´t fire.

The mistake that appears when it tries to execute the second update is the following:

Maximun stored procedure resting level exceed (limit 32)

And the trigger is like this:

create trigger active on movs
for update
as
declare @a integer
declare @b smallint
declare @c integer
declare @d smallint

select @a=a from inserted
select @b=b from inserted
select @c=c from id_mov from inserted
select @d=d from t_m where id_tm=@c

if (@mov=102)
begin
update idc set sa=sa+((select m from inserted)*d)where a=@a and b=@b
update movs set s=(select sa from edc where a=@a and b=@b) where cont=(select cont from inserted)
end"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-04-25 : 12:40:48

when you update movs, it causes the trigger to fire again . . . its like writing a stored procedure that calls itself. This is called nesting. Books on Line has a good section on this.

<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources