Update a Record Using a Trigger with SELECT Statement

  • I'm trying to use a trigger to update the record that has been created with addtional details, however the statement below does not update the record as desired. I know the trigger is firing as I've modified the @condetails with the word 'Hello' and this worked. I think the problem is to do with the select statement - any ideas?

    ALTER TRIGGER TR_Update_Interaction

    ON dbo.cust_hist

    AFTER INSERT AS

    BEGIN

    SET NOCOUNT ON;

    DECLARE @interaction char(22)

    DECLARE @loadnote char(12)

    DECLARE @condetails char(100)

    SELECT @interaction = tkey FROM inserted

    SELECT @loadnote = primary_ref FROM inserted

    SELECT @condetails = consignment FROM VLoadnoteCarr WHERE load_note = @loadnote

    UPDATE cust_hist

    SET notes = @condetails

    WHERE tkey = @interaction

    END

  • Your trigger has one major flow.

    When INSERT operation is performed the TRIGGER is fired for an operation, not for every inserted record.

    The way your trigger is written it will only process your logic for one of the inserted records.

    You should never read from INSERTED or DELETED into variables in triggers.

    Here is example of how it should be done properly:

    ALTER TRIGGER TR_Update_Interaction

    ON dbo.cust_hist

    AFTER INSERT AS

    BEGIN

    SET NOCOUNT ON;

    UPDATE cust_hist

    SET notes = v.consignment

    FROM cust_hist ch

    JOIN inserted i

    ON i.tkey = ch.tkey

    JOIN VLoadnoteCarr v

    ON v.load_note = i.primary_ref

    END

    Now, if it still doesn't update as expected, you need to analyse JOINs. Are they using relevant key columns?

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Hi Damion,

    Have you tried the select and update queries separately.

    Run them and see if they are working fine.?

  • What's the UPDATE statement you are using, and is there any reason why it can't include VLoadnoteCarr?


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Viewing 4 posts - 1 through 3 (of 3 total)

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