SQL Trigger - How to update a table whenever another table in another database is being updated?

Question

So I have a table called "persons" in a database test1:

CREATE TABLE [dbo].[persons](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [LastName] [varchar](50) NULL,
    [FirstName] [varchar](50) NULL,
    [Job] [varchar](50) NULL,
    [Extension] [char](10) NULL,
    [EMail] [varchar](50) NULL,
    [Dept] [int] NULL,
    [MobileNumber] [char](10) NULL,
    [District] [int] NULL,
    [Status] [varchar](50) NULL,
    [InOutStatus] [varchar](5) NULL,
    [InOutDescr] [varchar](50) NULL,
    [InOutDirections] [varchar](255) NULL,
    [InOutReturn] [varchar](50) NULL,
    [login] [varchar](50) NULL,
    [StatusExpiry] [datetime] NULL
) ON [PRIMARY]

And a table called "Users" in a database test2:

CREATE TABLE [dbo].[Users](
    [User_ID] [int] NOT NULL,
    [Firstname] [nvarchar](50) NULL,
    [Lastname] [nvarchar](50) NULL,
    [Username] [nvarchar](50) NULL,
    [Status] [nvarchar](50) NULL
) ON [PRIMARY]

What I'm trying to do is create a Trigger where if the FirstName, LastName, login, and/or Status column in the persons table is updated, then the same value is updated in its identical column in the Users table. This is a Trigger I came up with but it doesn't work:

ALTER TRIGGER [dbo].[UserUpdteTrig]
   ON  [dbo].[persons]
   AFTER UPDATE
AS 
BEGIN
    DECLARE @uid int;
    DECLARE @fname nvarchar(50);
    DECLARE @lname nvarchar(50);
    DECLARE @uname nvarchar(50);
    DECLARE @stat nvarchar(50);


    SELECT @fname = i.FirstName, @lname = i.LastName, @uname = i.login, @stat = i.Status
    FROM inserted i

    UPDATE [test2].[dbo].[Users]
    SET Firstname = @fname , Lastname = @lname, Username = @uname, Status = @stat
    FROM [test2].[dbo].[Users] u INNER JOIN inserted i ON u.User_ID = i.ID

END

I keep getting an error that says: The row value updated and deleted either do not make the row unique or they alter multiple rows, which the PK in both tables are all unique and the PK shouldn't be altered anyway.

Possible duplicate of [How to use update trigger to update another table?](http://stackoverflow.com/questions/5252802/how-to-use-update-trigger-to-update-another-table) — Ken White, Oct 01 '15 at 18:30
I believe I saw that and it didn't solve my problem. I keep getting an error that says: The row value updated and deleted either do not make the row unique or they alter multiple rows, which the PK in both tables are all unique and the PK shouldn't be altered anyway. — Kristin Vernon, Oct 01 '15 at 19:44

score 0 · Accepted Answer · answered Oct 01 '15 at 20:23

You dont need any variables at all just a simple update should be enough.

ALTER TRIGGER [dbo].[UserUpdteTrig]
   ON  [dbo].[persons]
   AFTER UPDATE
AS 
BEGIN
 SET NOCOUNT ON;

     UPDATE u 
    SET u.Firstname = i.FirstName
      , u.Lastname  = i.Lastname
      , u.Username  = i.Username
      , u.[Status]  = i.[Status]
    FROM [test2].[dbo].[Users] u 
    INNER JOIN inserted i ON u.[User_ID] = i.ID


END

SQL Trigger - How to update a table whenever another table in another database is being updated?

1 Answers1