How to get value of updated column in SQL Trigger

Question

I have a trigger in SQL that works without errors. Hwoever it does not accomplish the desired behavior. What I am trying to do, is when the user updates the field1 column, take that value, and insert that value and the associated information with that value into a different database table. It does not insert as expected. Here is the Trigger:

ALTER TRIGGER [dbo].[Trigger1]
   ON  [dbo].[table1]
   AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @UpdatedVendId CHAR

    SELECT
      @UpdatedVendId = inserted.VendId
    FROM
      inserted


    IF UPDATE (VendID)
    BEGIN   INSERT INTO OtherTable (ClassID, Crtd_User, LUpd_DateTime, LUpd_User, VendId) SELECT ClassID, crtd_User, LUpd_DateTime, LUpd_User, VendId FROM dbo.table1 WHERE VendId = @UpdatedVendId

    END
END

Your trigger is not set based. Drop the scalar variables and simply do your insert from the inserted virtual table. — Sean Lange
– Sean Lange, Commented Sep 3, 2014 at 20:49

Sean Lange · Accepted Answer · 2014-09-03 20:54:11Z

1

To fix the problem you have with multiple row operation you can greatly simplify your trigger by using the inserted virtual table. The entire contents of your trigger can be shortened to this.

IF UPDATE (VendID)
    INSERT INTO OtherTable 
    (
        ClassID
        , Crtd_User
        , LUpd_DateTime
        , LUpd_User
        , VendId
    ) 
    SELECT t1.ClassID
        , t1.crtd_User
        , t1.LUpd_DateTime
        , t1.LUpd_User
        , t1.VendId 
    FROM dbo.table1 t1
    join inserted i on i.ClassID = t.ClassID

answered Sep 3, 2014 at 20:54

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jonathan Durda Over a year ago

This did not seem to work for me. It did not insert any record into the database as expected. I am trying to figure out if it did not like the JOIN in the insert statement

Collectives™ on Stack Overflow

How to get value of updated column in SQL Trigger

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related