Need help with trigger - 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

General SQL Server Forums

New to SQL Server Programming

Need help with trigger

Author

Topic

Inno
Starting Member

33 Posts

Posted - 2007-06-19 : 05:41:57

Cant get this trigger to do the job I want it to. Either it doesnt notice that a change has happened in the table or it doesnt understand what it is supposed to do...Anyone see a problem I havent noticed?

CREATE TRIGGER test ON [Testdb].[Activity]
FOR UPDATE
AS

if @@nestlevel > 1

return

Declare
@LyftA int,
@LyftB int,
@LyftC int,
@Lyft int,
@ActivityNo int

Select @ActivityNo = ActivityNo, @LyftA = LyftA, @LyftB = LyftB, @LyftC = LyftC, @Lyft = Lyft from inserted

If @LyftA = 1
Begin
Update Testdb.Activity set Lyft = Lyft + 1, LyftA = 0 where ActivityNo = @ActivityNo
End

If @LyftB = 1
Begin
Update Testdb.Activity set Lyft = Lyft + 1, LyftB = 0 where ActivityNo = @ActivityNo
End

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-19 : 05:48:15

if @@nestlevel > 1
why that? Are you sure there aren't nested transactions for the update?

The trigger will only handle single row updates which may be a problem.
You are getting a few values from inserted but only using ActivityNo - is that correct - if so don't get the others to save confusinn.

how about

update Testdb.Activity
set Lyft = Lyft + 1, LyftA = 0
from Testdb.Activity a
join inserted i
on a.ActivityNo = i.ActivityNo

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources