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
        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


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

  • seriously??? can i ask for the reason behind down vote?? – ashiq pervez Jul 17 '20 at 16:57
  • As already indicated, your code has major problems. But this question is off-topic for dba and more properly belongs on SO. And the comments about "EID is primary key" and "what if no primary key" suggests a level of confusion that should be addressed first. I must question the presence of a table that contains duplicate information for no obvious purpose. Perhaps what you desire is a history table that shows changes over time? That is much different from what you have posted. – SMor Jul 17 '20 at 17:39

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

    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;
  • it only is updating value of employee table – ashiq pervez Jul 17 '20 at 14:35
  • No, it should be updating the employee backup table as well. Notice, though, that any actual change would only be noticeable on the salary and city columns, and also it would only work if the corresponding record already exists in the backup table. – Eitan Blumin Jul 17 '20 at 14:39
  • what is the primary key in both tables, it should be easy if you pass the CREATE TABLE statement. – McNets Jul 17 '20 at 14:41
  • EID IS THE PRIMARY KEY – ashiq pervez Jul 17 '20 at 16:01
  • Then you should remove eid from the SET part and let it alone in ON – McNets Jul 17 '20 at 16:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.