SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 trigger is called ^2 times
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

snezok
Starting Member

2 Posts

Posted - 11/28/2008 :  04:15:33  Show Profile  Reply with Quote
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

Sweden
30218 Posts

Posted - 11/28/2008 :  04:23:22  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8764 Posts

Posted - 11/28/2008 :  04:24:58  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8764 Posts

Posted - 11/28/2008 :  04:25:36  Show Profile  Visit webfred's Homepage  Reply with Quote
I am tooooooo slow


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

snezok
Starting Member

2 Posts

Posted - 11/28/2008 :  04:28:41  Show Profile  Reply with Quote
Thanks ^_^
that worked !
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000