April 25, 2005 at 3:52 am
Hi I've been trying to find an example of an update trigger (having never used one before) that updates another table.
I have 2 fields (systemowner) and (systemuser) on table A which if changed I want to update 2 fields named the same on Table B on another server.
My initial question is how do I refer to the fields from table A on the update trigger on table A. I can see how this is done with an insert trigger using the inserted cursor but how do I specifically refer to to the values systemowner, systemuser that have changed in tableA in my UPDATE tableB statement
My current syntax is
CREATE TRIGGER tableA_update ON [server1].[dbo].[tableB]
FOR UPDATE
AS
IF UPDATE(systemowner) OR UPDATE(systemuser)
BEGIN DISTRIBUTED TRANSACTION
UPDATE tableB
SET systemowner = systemowner updated in tableA,
systemuser = systemuser updated in tableA FROM [remote].[server2].[dbo].[tableB] tableB
COMMIT TRANSACTION
Cheers
April 25, 2005 at 7:16 am
The inserted table contains the rows updated, so
CREATE TRIGGER tableA_update ON [server1].[dbo].[tableA]
FOR UPDATE
AS
IF UPDATE(systemowner) OR UPDATE([systemuser])
BEGIN
BEGIN DISTRIBUTED TRANSACTION
UPDATE [server2].dbo.tableB
SET systemowner = inserted.systemowner
[systemuser] = inserted.[systemuser]
COMMIT TRANSACTION
END
This assumes that each table only contains one record.
Far away is close at hand in the images of elsewhere.
Anon.
April 25, 2005 at 12:41 pm
Cheers David, in which case I can just join inserted into my update tableb to match it
April 26, 2005 at 4:49 am
Yes, the inserted table contains all the columns of the updated row, you can join to it like a normal table.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply