Problem with 2 triggers on the table

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

Problem with 2 triggers on the table

Author

Topic

tmorawiec
Starting Member

2 Posts

Posted - 2009-10-29 : 07:28:14

TableA has 2 triggers "AFTER UPDATE"
First trigger updates the set of rows of TableA with exception of itself:

(...)
set @id = (select @id from inserted)

update TableA
set Nr = Nr + 1
where id <> @id

The second trigger inserts @id to another table:

(...)
set @id = (select @id from inserted)
insert into DiagnosticTable select @id

----
Table has 10 records. I changed record with Id = 1.
I expected 10 rows added to DiagnosticTable. In the table was 2 records only, with Id = 1 and Id = 2.

Why?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-31 : 02:03:34

becuse you're storing @id in a variable so that it stores only a single value. what you need is to use a temporary table with id column to store the ids of all the updated records or take join with inserted table.

something like:-


update a
set a.Nr = a.Nr + 1
from TableA a
left join inserted i
on i.id=a.id
where i.id is null


and

insert into DiagnosticTable
select id from inserted

tmorawiec
Starting Member

2 Posts

Posted - 2009-11-04 : 14:43:26

Thanks. Your answer is very helpful.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources