Update Trigger questions / multiple rows in "inserted" table

Question

Post reply

Update Trigger questions / multiple rows in "inserted" table

Mark Rissmann

SSC Veteran

Points: 256
More actions
January 30, 2007 at 12:31 am

#118645

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

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

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

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.

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 3

Hi,

Something like this should do the trick:

IF EXISTS (SELECT name FROM sysobjects WHERE name = N'trgVendor' AND type = 'TR') 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

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

Mark Rissmann SSC Veteran Points: 256 More actions · Answer 4

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...

-Markus

Brian.cs Default port Points: 1407 More actions · Answer 5

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!

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 6

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)