Posted on 2004-11-18
Last Modified: 2010-03-19
is it possible to catpture the UPDATED value of a column with a trigger - then use that value to update another table on a different database?

Question by:just1coder
    LVL 6

    Expert Comment

    by:Duane Lawrence

    select * from inserted
    --where (put condition here)

    --Then insert into the other table

    LVL 50

    Expert Comment

    basically like this...

    update OtherDatabase.dbo.TableName
      Set Column = U.Column
     From OtherDatabase.dbo.TableName as X
     Inner Join (select I.Column,I.Key from Inserted as I
                        inner Join Deleted as D on I.key=D.Key and I.column <> D.Column and
                        i.column is not null and d.column is not null) as U
       on X.Key = U.Key

    LVL 2

    Author Comment

    could you explain that? thanks.
    LVL 2

    Author Comment

    where does that go?

    create trigger tr_test
    on dbo.table1.test
    for update
    LVL 8

    Expert Comment

    Create Trigger trtest on dbo.Test
    After UPDATE
    Set Nocount on
    update dbo.OtherTable
    set OtherColumn  = ThisColumn
    from dbo.OtherTable d join
          inserted i
    on d.ID  = i.ID
    LVL 50

    Accepted Solution


    create trigger tr_test
    on dbo.table1
    for update

    -- an update statement
    update OtherDatabase.dbo.TableName
    -- the columns you wish to update
      Set Column = U.Column
           ,col2 = U.col2
    -- the table where going to update
     From OtherDatabase.dbo.TableName as X
    -- the join uses the INSERTED and DELETED tables which contain the
    -- AFTER and BEFORE images of the rows which your "External" SQL has Modified
    -- on the dbo.table1 table
    -- The Join on the PK (primary key of the Data) allows us to compare the contents of the
    -- Columns you want to monitor for changes to be compared.

     Inner Join (select I.Column,I.PKey,I.Col2
                       from Inserted as I
                        inner Join Deleted as D
                              on I.Pkey=D.PKey
    -- the check for a change in the "columns" data...
    -- if a column is Nullable then NULL never equates to NULL  
    -- so need to test explicitly that  that both the before and after values aren't still NULL
                       Where (I.column <> D.Column and
                        i.column is not null and d.column is not null)
                    or     (I.col2 <> D.Col2 and
                        i.col2 is not null and d.col2 is not null)
                       ) as U
       on X.PKey = U.PKey


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now