1

I have 2 tables Employee and Employee_Backup

Employee has 3 columns

  • IDEmployee
  • Name
  • Status

and Employee_Backup also has 3 columns:

  • IDEmployee
  • Detail
  • Status

For every row inserted into or updated in table Employee, I want to set the Status in table Employee_Backup using this criteria

WHERE employee_backup.IDEmployee = employee.IDEmployee     (inserted / updated)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
azalikaEriya
  • 195
  • 1
  • 4
  • 17
  • Hi there is a clear outline on the TRIGGERS you are looking for here http://stackoverflow.com/questions/741414/insert-update-trigger-how-to-determine-if-insert-or-update – szakwani Jan 14 '15 at 05:10
  • i can't make it automatically, i have try many way in google but no one is works. – azalikaEriya Jan 14 '15 at 05:11

1 Answers1

0

Something like that??

CREATE TRIGGER tr_Employee_Insert
ON dbo.Employee
FOR INSERT 
AS
    UPDATE b
    SET Status = 'Inserted'
    FROM dbo.Employee_Backup b
    INNER JOIN Inserted i ON b.EmployeeID = i.EmployeeID


CREATE TRIGGER tr_Employee_Update
ON dbo.Employee
FOR UPDATE
AS
    UPDATE b
    SET Status = 'Updated'
    FROM dbo.Employee_Backup b
    INNER JOIN Inserted i ON b.EmployeeID = i.EmployeeID

You basically need to join the Inserted pseudo table which contains all rows that have been inserted (or updated) from the base table (dbo.Employee) and the Employee_Backup table - and then use that result set from the JOIN as the basis for your UPDATE statement.

Note: this will NOT insert any new rows into Employee_Backup when you add new rows to dbo.Employee - is that what you want? If not, you'd have to change the FOR INSERT trigger a bit ....

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • ya, I just need update status field from employee_backup whenever I insert or update data on table Employee. – azalikaEriya Jan 14 '15 at 06:28
  • if I implement your suggestion, the result is always 'inserted' not Updated. whenever i try to update, it's prohibited. It says 'the data not commited' before I implement your suggestion. it is fine to update – azalikaEriya Jan 14 '15 at 06:35