CODEWITHSUNDEEP
sql-serveracumatica
Elina
Elina

Reputation: 31

Error update trigger after new row has inserted into same table

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.

https://i.sstatic.net/6ipFa.png

https://i.sstatic.net/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.'.

Upvotes: 2

Views: 227

Answers (1)

CoolBots
CoolBots

Reputation: 4879

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.

Upvotes: 3

Related Questions