How to update records of two tables through trigger?

Question

I have two tables as employee and employee_backup whenever i update employee table the trigger must update employee_backup table also : this is the piece of code i was trying to achieve specified goal:

 create trigger update_both on employee after update
    As
    Begin
    
        declare @EID varchar(10), 
                @Name varchar(20),
                @age int, 
                @salaray int, 
                @city varchar(20)

        select * from inserted 
    
        update employee_backup 
        set @eid=eid, @name=name, @age=age, @salaray=salaray, @city=city 
        where @eid=eid and @name=name and @age=age

    end

before this is able to update employee id and employee backup id but now it is messing up .. your suggestions matter alot :)

Answer 1

Your trigger doesn't update anything, you should join employee_backup and inserted tables and update affected rows only.

CREATE TRIGGER updEmployeeBackup ON employee AFTER UPDATE
AS
BEGIN

    UPDATE eb
    SET    name = i.name, age = i.age, salary = i.salary, city = i.city
    FROM   employee_backup eb
    JOIN   inserted i
           ON eb.eid;
END