CODEWITHSUNDEEP
sqlsql-servert-sqltriggers
user1435281
user1435281

Reputation: 123

T-SQL Trigger - Updating a Table using a separate table, multi-row insert possible

I'm new to triggers and am working on setting up a trigger to update a table (REQ_L) when a record is inserted there that meets specific parameters. The value to update in REQ_L is pulled from a separate table that has a matching key.

create table REQ_L (item_number varchar(20), 
                    commodity_code varchar(20), 
                    vendor_id varchar(20), 
                    item_source varchar(20));

create table XREF_C (item_number varchar(20), 
                     commodity_code varchar(20), 
                     xref_type varchar(20));

I'd like it for when a record is inserted into REQ_L, if it meets the criteria in the trigger it will be update the COMMODITY_CODE from REQ_L with the COMMODITY_CODE in XREF_C using the ITEM_NUMBER as the key.

No longer locking and switched over to using the inserted tables. The good news is no more deadlock, the bad is that it's still not updating the table. Updated SQL Trigger attempt:

CREATE TRIGGER WBM
    ON REQ_L
    AFTER INSERT
    AS
    IF EXISTS (SELECT * FROM inserted WHERE VENDOR_ID = 'W7315'
                AND ITEM_SOURCE = 'XML'
                AND COMMODITY_CODE NOT LIKE '%-%')
    BEGIN
        UPDATE REQ_L
            SET COMMODITY_CODE = (SELECT distinct CODE_2 
                                    FROM XREF_C xc, inserted i 
                                    WHERE i.ITEM_NUMBER = xc.CODE_1 
                                    AND xc.XREF_TYPE = 'WBM')
        FROM XREF_C xc, inserted i
        WHERE i.ITEM_NUMBER = xc.CODE_1
        END
GO

Upvotes: 0

Views: 476

Answers (2)

John Bingham
John Bingham

Reputation: 2006

I agree with the general comments above, but I'm thinking the logic your using looks dodgy. You have:

    UPDATE REQ_L
        SET COMMODITY_CODE = (SELECT distinct CODE_2 
                                FROM XREF_C xc, inserted i 
                                WHERE i.ITEM_NUMBER = xc.CODE_1 
                                AND xc.XREF_TYPE = 'WBM')
    FROM XREF_C xc, inserted i
    WHERE i.ITEM_NUMBER = xc.CODE_1

Well I may be wrong, but I can't help thinking that the table you're updating should appear in the from clause of the statement.

How about this:

UPDATE rl
SET Commodity_Code = x.Code_2
FROM Req_L rl
    INNER JOIN Inserted i ON --limit the join clause to the cols that have been inserted
        rl.item_number = i.item_number and  
        rl.commodity_code = i.commodity_code and
        rl.vendor_id = i.vendor_id and 
        rl.item_source = i.item_source
    INNER JOIN XREF_C x ON L.item_number = x.item_number
WHERE x.xref_type = 'WBM'

Upvotes: 0

Dale M
Dale M

Reputation: 2473

Try simplyfing your query structure. I still don't know what CODE_1 and CODE_2 are but this worked for me.

ALTER TRIGGER [dbo].[WBM]
    ON [dbo].[REQ_L]
    AFTER INSERT
    AS
    UPDATE  REQ_L
    SET     commodity_code = xc.commodity_code  FROM    INSERTED i          INNER JOIN          XREF_C xc ON i.Item_Number=xc.item_number
                         AND
                         xc.xref_type = 'WBM'
                         AND
                         i.VENDOR_ID = 'W7315'
                         AND
                         i.ITEM_SOURCE = 'XML'
                         AND
                         i.COMMODITY_CODE NOT LIKE '%-%'

Remember, IF statements belong in C# and Visual Basic - if you are using them in SQL you are not writing your queries with a dataset mentality. Most of the time that is; sometimes they can't be avoided.

Upvotes: 0

Related Questions