January 30, 2007 at 12:31 am
Good day,
I am relatively new to triggers. I thought I new what I was doing (first mistake) until an update trigger contained more than 1 row.
This is for a system that I did not design but inherited. I need to make the minimum changes necessary.
For historical reasons whenever an invoice is created a duplicate of the vendor records is created (and the duplicates are not visible in vendor dropdowns). Currently, when the user changes the vendor code to the visible record (the original) it is no longer linked to the copies and searching for invoices for the new vendor code will not reveal any old data.
So I added 2 columns, OrigVenCode and OrigVenID to setup the relationship. (I don't know if the origvencode will ever be of use but I want to retain it just in case)
Table layout (last 2 columns are new)
vendorID VendorCode IsCopy OrigVenCode OrigVenID
----------- ------------ ---------- ------------- -----------
1 ABC 0 ABC 1
2 ABC 1 ABC 1
3 ABC 1 ABC 1
4 DEF 0 HHH 4
5 DEF 1 HHH 4
So I need a trigger (update?) that when the vendor code of the original record (indicated by IsCopy = 0) is changed that all the copies are changed as well.
For instance if I edit vendorID 1 and set the vendorcode = xxx I want the vendorcode for vendorID 2 and vendorID 3 changed as well.
Too me this looked very easy until the case where multiple records are modified within a transaction and I get the beloved:
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.The statement has been terminated."
error. How do I take into account multiple rows in the update?
Here is my trigger:
CREATE TRIGGER Trig_vendor_code ON [vendor] FOR UPDATE
AS
DECLARE @newcode varchar(10)
DECLARE @venid int
DECLARE @iscopy int
SELECT @venid = (SELECT vendorID FROM Inserted)
SELECT @iscopy = (SELECT IsCopy FROM Inserted)
--only care if we are working on an original
IF (@iscopy = 1)
BEGIN
RETURN
END
--if vendor.code wasn't update, exit
IF NOT UPDATE(vendorCode)
BEGIN
RETURN
END
SELECT @newcode = (SELECT vendorCode FROM Inserted)
UPDATE vendor SET vendorcode = @newcode WHERE origvenid = @venid and IsCopy = 1
Any help would be much appreciated. I hope you understand my example. And sorry, I did not design the messy structure. Just trying to prevent further data loss.
-Markus
January 30, 2007 at 1:19 am
CREATE TRIGGER Trig_vendor_code ON [vendor] FOR UPDATE
AS
UPDATE V
SET vendorcode = i.vendorcode
FROM dbo.Vendor V
INNER JOIN inserted i ON V.origvenid = i.vendorID
WHERE V.vendorcode i.vendorcode -- add NULL checks if this column is nullable
AND V.IsCopy = 0 -- as I understand it was a typo in your code, right?
GO
_____________
Code for TallyGenerator
January 30, 2007 at 7:33 am
I'm not sure if the code Seygiy provided is right, but he has the correct idea for trigger design. Don't assign variables from your inserted/deleted tables. Instead use a query that captures all rows for your update.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 30, 2007 at 10:21 am
Hi,
Something like this should do the trick:
DROP TRIGGER trgVendor
GO
CREATE TRIGGER trgVendor
ON vendor
FOR UPDATE
AS
BEGIN
UPDATE vendor
SET vendor.VendorCode = inserted.VendorCode
FROM inserted
WHERE vendor.IsCopy = 1
AND vendor.OrigvenID = inserted.vendorID
END
GO
January 30, 2007 at 5:21 pm
Hey guys,
Thanks a lot for the help!
Here is the final outcome:
IF UPDATE(VendorCode)
BEGIN
UPDATE vendor SET vendorcode=inserted.vendorcode
FROM inserted JOIN deleted ON inserted.vendorID=deleted.vendorID
WHERE (inserted.vendorcode <> deleted.vendorcode) AND (inserted.iscopy=0) AND (inserted.ID = vendor.origvenid) AND (vendor.iscopy=1)
END
I added the checks to make sure that the vendorcode actually changed...
I added the checks to make sure that the vendorcode actually changed...
-Markus
October 31, 2008 at 7:27 am
I was running into the same issue, yet the issue only manifested when multiple rows were updated. All of the updated rows had a column that was being manipulated all get the same value which seemed impossible and incorrect until I read this about ensuring the trigger code was in a single update statement to catch each row in inserted. This cleared it up for me as well and I was able to modify my trigger accordingly. Thank you as well!
October 31, 2008 at 7:37 pm
Mark Rissmann (1/30/2007)
IF UPDATE(VendorCode)
BEGIN
UPDATE vendor SET vendorcode=inserted.vendorcode
FROM inserted JOIN deleted ON inserted.vendorID=deleted.vendorID
WHERE (inserted.vendorcode <> deleted.vendorcode) AND (inserted.iscopy=0) AND (inserted.ID = vendor.origvenid) AND (vendor.iscopy=1)
Nicely done, Mark... thanks for sharing your final solution! 🙂
--Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply