例如我有这两个表
Employee(FirstName, LastName, JoinDate, DesignationID,UpdateDate)
Employee_Designation_History(EmployeeID, DesignationID)
如果我更新员工表(改变DestinationID
的Employee
),我需要检索该员工的具体数据,并保存到其他表Employee_Designation_History
。
我猜您EmployeeId
在第一个表中也有一个列。这可以通过触发器来轻松完成:
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
我从已删除数据中选择数据的原因是,我猜测您想在更改之前保留DesignationID的值。我已经在目标表上使用了LEFT JOIN,因此,如果您已经有一条记录,并且记录的数据相同,那么它将不会与触发器相乘。
我使用了UPDATE()函数来确保所讨论的列确实已更新。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句