| Author |
 Topic  |
|
|
snezok
Starting Member
2 Posts |
 Posted - 11/28/2008 : 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
Sweden
29138 Posts |
Posted - 11/28/2008 : 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
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 11/28/2008 : 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
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 11/28/2008 : 04:25:36
|
I am tooooooo slow 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
snezok
Starting Member
2 Posts |
Posted - 11/28/2008 : 04:28:41
|
Thanks ^_^
that worked ! |
|
|
| |
Topic |
|
|
|
trigger is called ^2 times
