Trigger

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?

LVL 2
just1coderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Duane LawrenceCommented:
Yes

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

--Then insert into the other table

Duane
0
LowfatspreadCommented:
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


 
0
just1coderAuthor Commented:
could you explain that? thanks.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

just1coderAuthor Commented:
where does that go?

create trigger tr_test
on dbo.table1.test
for update
as
....
....
....
0
SimonLarsenCommented:
Create Trigger trtest on dbo.Test
After UPDATE
AS
Set Nocount on
            
update dbo.OtherTable
set OtherColumn  = ThisColumn
from dbo.OtherTable d join
      inserted i
on d.ID  = i.ID
0
LowfatspreadCommented:
ok

create trigger tr_test
on dbo.table1
for update
as

-- 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

hth
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.