[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 378
  • Last Modified:

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?

0
just1coder
Asked:
just1coder
1 Solution
 
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now