Error update trigger after new row has inserted into same table

Question

I want to update OrigOrderNbr and OrigOrderType (QT type) because when I create first both of column are Null value. But after S2 was created (QT converted to S2) the OrigOrderType and OrigOrderNbr (S2) take from QT reference. Instead of that, I want to update it to QT also.

http://i.stack.imgur.com/6ipFa.png

http://i.stack.imgur.com/E6qzT.png

CREATE TRIGGER tgg_SOOrder
    ON dbo.SOOrder
    FOR INSERT
    AS
    DECLARE @tOrigOrderType char(2),
            @tOrigOrderNbr nvarchar(15) 
    SELECT  @tOrigOrderType = i.OrderType,
            @tOrigOrderNbr = i.OrderNbr 
    FROM inserted i

    UPDATE dbo.SOOrder 
    SET OrigOrderType = @tOrigOrderType,
        OrigOrderNbr = @tOrigOrderNbr 
    FROM inserted i
    WHERE dbo.SOOrder.CompanyID='2' 
    and   dbo.SOOrder.OrderType=i.OrigOrderType 
    and   dbo.SOOrder.OrderNbr=i.OrigOrderNbr 
GO

After I run that trigger, it showed the message 'Error #91: Another process has updated 'SOOrder' record. Your changes will be lost.'.

The trigger runs on every insert, but you only want it to run on insert of S2 type record, correct? — CoolBots
No, what I mean is when I convert the quotation (QT) to SOOrder (S2) it alert message error. So it means I was updating and inserting same time. — Elina
Your trigger has MAJOR flaw: you assume it'll be called once per row - that is not the case. The trigger will fire once per statement, so if your INSERT statement that causes this trigger to fire inserts 25 rows, the trigger fires once and the Inserted pseudo table will contain 25 rows. Which of those 25 rows will your code select here?? SELECT @tOrigOrderNbr = i.OrderNbr FROM inserted i - it's non-deterministic, you'll get one arbitrary row and you will be ignoring all other rows. You need to rewrite your trigger to take this into account! — marc_s
@marc_s Generally agreed; this is best handled by the procedure doing the insert of S2 record vs a trigger. I made that suggestion in my original answer (different post, same poster). I included code to check for multiple rows and throw an error as an active reminder of this rather unusual situation. If you have further improvements, please suggest an edit. Thank you! — CoolBots

CoolBots CoolBots · Accepted Answer · 2016-06-03T04:44:48

Per long string of comments, including some excellent suggestions in regards to proper trigger writing techniques by @marc_s and @Damien_The_Unbeliever, as well as my better understanding of your issue at this point, here's the re-worked trigger:

CREATE TRIGGER tgg_SOOrder
ON dbo.SOOrder
FOR INSERT
AS

--Update QT record with S2 record's order info
UPDATE  SOOrder
SET     OrigOrderType       =   'S2'
,       OrigOrderNbr        =   i.OrderNbr
FROM    SOOrder                 dest
JOIN    inserted                i
ON      dest.OrderNbr       =   i.OrigOrderNbr
WHERE   dest.OrderType      =   'QT'
AND     i.OrderType         =   'S2'
AND     dest.CompanyID      =   2   --Business logic constraint
AND     dest.OrigOrderNbr   IS  NULL
AND     dest.OrigOrderType  IS  NULL

Basically, the idea is to update any record of type "QT" once a matching record of type "S2" is created. Matching here means that OrigOrderNbr of S2 record is the same as OrderNbr of QT record. I kept your business logic constraint in regards to CompanyID being set to 2. Additionally, we only care to modify QT records that have OrigOrderNbr and OrigOrderType set to NULL.

This trigger does not rely on a single-row insert; it will work regardless of the number of rows inserted - which is far less likely to break down the line.

Error update trigger after new row has inserted into same table

1 Answers