Tricky triggers
-
Hi,
I am trying to update a child table using a trigger on the master table. However, if more than one record is inserted into the master, my trigger errors out...saying the subquery is returning more than one value. And I see the problem but don't know how to solve it. I'd appreciate any guidance anyone can provide.
-
Can you post your trigger code?
Cheers,
- Mark
Cheers,
- Mark -
Thanks for responding. Here's the code that I'm using. Table1 is the master.
if update (col1)
begin
update table2
set col1=(select col1 from inserted)
end
or
if update(col1)
begin
while (select count(col1) from inserted)>0
update tabel2
set col1=(select inserted.col1 from inserted where inserted.col1=deleted.col1)
end
-
You can try the following code (nb. "id" is the primary key field of your master table)
if update (col1)
begin
update a2
set col1 = inserted.col1
from a2, inserted
where a2.id = inserted.id
endCheers,
- Mark
Cheers,
- Mark -
Hi peygham,
quote:
Hi,I am trying to update a child table using a trigger on the master table. However, if more than one record is inserted into the master, my trigger errors out...saying the subquery is returning more than one value. And I see the problem but don't know how to solve it. I'd appreciate any guidance anyone can provide.
I might be wrong, but I think a trigger is fired once per inser operation, and not once per inserted row, if there are more than one.
If you do this to enforce data (referential) integrity, I would use a PK - FK constraint
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thank you. I'll try your solutions.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply