CODEWITHSUNDEEP
sql-server
Amir
Amir

Reputation: 69

How to move Specific row in a table to another table when that row is updated

For example I have these two tables 

Employee(FirstName, LastName, JoinDate, DesignationID,UpdateDate)
Employee_Designation_History(EmployeeID, DesignationID)

If I update employee table (changing the DestinationID of the Employee), I need to retrieve the specific data of that employee and save into the other table Employee_Designation_History.

Upvotes: 0

Views: 66

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82514

I'm guessing you have a column named EmployeeId in the first table as well. This can be done by a trigger fairly easy:

CREATE TRIGGER Employee_Update ON Employee FOR UPDATE
AS
BEGIN
    IF UPDATE(DesignationID) -- only if the relevant column changed
    BEGIN
    INSERT INTO Employee_Designation_History (EmployeeID, DesignationID)
    SELECT d.EmployeeID, d.DesignationID
    FROM deleted d -- That's not a typo, deleted is the correct pseudo table
    LEFT JOIN Employee_Designation_History edh 
    ON(d.EmployeeID = edh.EmployeeID 
       AND d.DesignationID = edh.DesignationID)
    WHERE edh.EmployeeID IS NULL
    END
END

The reason I selected the data from deleted is I'm guessing you want to keep the value of DesignationID before the change. I've used LEFT JOIN on target table so if you already have a record with the same data it will not be multiplied by the trigger.
I've used the UPDATE() function to make sure that the column in question was indeed updated.

Upvotes: 1

maja
maja

Reputation: 18054

What you need is a Trigger that recognises an update-statement on that table and automatically performs an insert in another table.

CREATE TRIGGER [update_history] ON Employee 
FOR UPDATE
AS
INSERT Employee_Designation_History (EmployeeID, DesignationID)
--Use the following insert to historicize the old (overwritten) data
SELECT ??, DesignationID,
FROM Employee 
    Join inserted
        On inserted.id = Employee.id
--Use the follong insert to historicize the new (updated) data
--INSERT Employee_Designation_History (EmployeeID, DesignationID)
--SELECT  ??, DesignationID
--FROM inserted

Source: Table history trigger in SQL Server?

Upvotes: 0

Related Questions