Triggers

Question

Triggers

lanky_doodle

SSCrazy

Points: 2378
More actions
April 1, 2015 at 8:27 am

#296531

Hey,
I want to create a trigger so that when a column is updated in one table in one database, another column in another table in another database is updated.
This seems to update the whole destination table:
CREATE TRIGGER tr_User_Forename
ON [dbo].[tbl_Users]
AFTER UPDATE
AS
SET NOCOUNT ON;
IF UPDATE (Forename)
BEGIN
update<dest_db>.dbo.tbl_Users
set<dest_db>.dbo.tbl_Users.Forename = x.Surname
from<source_db>.dbo.tbl_Users x
where<dest_db>.dbo.tbl_Users.[User_ID] = x.[User_ID]
END
How do I get only the row that was updated?
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

toddasd SSCertifiable Points: 6298 More actions · Answer 1

Change "<source_db>.dbo.tbl_Users" to "inserted" in the from clause and that should get what you want.

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

lanky_doodle SSCrazy Points: 2378 More actions · Answer 2

Thanks. I had read a bit about the 'inserted' pseudo table but interpreted it as being explicitly for inserts, not updates.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

inserted = new rows for an insert and new values for an update.

deleted = deleted rows for a delete and old values for an update.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass