trigger is called ^2 times - 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 2005 Forums

Transact-SQL (2005)

trigger is called ^2 times

Author

Topic

snezok
Starting Member

2 Posts

Posted - 2008-11-28 : 04:15:33

Hello everyone !

i got a problem with a sql server trigger,
for example when i make update with statement like this :

update table1 set name='WakisakiShukufuku' where id=1

everything works perfectly.
but when i try to run something like this :

update table1 set name='WakisakiShukufuku' where id in (1,2,3)
trigger writes to 'versions' table 9 rows.

can it be something wrong with trigger ?
Would be glad to hear any advice.
Here is trigger code:
--------------------------------------------------------
ALTER TRIGGER [dbo].[table1_UPDATE] ON [dbo].[table1]
INSTEAD OF UPDATE AS UPDATE [dbo].[table1] SET
Name=I.Name,
FROM INSERTED I WHERE table1.ID=I.ID;

INSERT INTO [dbo].versions(time, old_content, new_content)
SELECT 'table1',
getdate(),
b.Name,
a.Name
FROM inserted a, deleted b;
--------------------------------------------------------

Thank You in advance ! :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-28 : 04:23:22

Of course!
You can a CROSS JOIN (cartesian product, all possible combinations) at the INSERT query.

Change last INSERT to

INSERT INTO [dbo].versions(time, old_content, new_content)
SELECT 'table1',
getdate(),
b.Name,
a.Name
FROM inserted a, deleted b
where a.id = b.id;

E 12°55'05.63"
N 56°04'39.26"

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-11-28 : 04:24:58

Your insert joins all together, try this:
INSERT INTO [dbo].versions(time, old_content, new_content)
SELECT 'table1',
getdate(),
b.Name,
a.Name
FROM inserted a
INNER JOIN deleted b ON (a.ID = b.ID)

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-11-28 : 04:25:36

I am tooooooo slow