Northwind中的SQL Server触发器：不工作的代码

Question

下面是说明：创建Changelog表后，在Employee表上创建一个用于更新的触发器。使用此触发器监视Payrate列，如果员工的Payrate发生更改，请让触发器在Changelog表中插入一行，其中包含适当的数据。[使用函数suser_sname()让用户进行更改，并将其插入到用户列中。]

CREATE TRIGGER PayRate_Change 
ON Employees
AFTER UPDATE
AS
BEGIN
    DECLARE @EmpID int;
    DECLARE @OldRate money;
    DECLARE @NewRate money;

    IF UPDATE (PsyRate)
        SELECT @EmpID = (SELECT @EmpID FROM inserted)

    SELECT @OldRate = (SELECT PsyRate FROM deleted)
    SELECT @NewRate = (SELECT PsyRate FROM inserted)

    INSERT INTO [dbo].[ChangeLog](EmpID, ChangedBy, DateChanged, OldRate, NewRate)
    VALUES (@EmpID, suser_sname(), getdate(), @OldRate, @NewRate);

    PRINT 'The PayRate was changed from "' + @OldRate + '" to "' + @NewRate + '"'
END

UPDATE Employees
SET PsyRate = PsyRate * 1.2
WHERE LastName ='King';

有人能帮忙吗？

Answer 1

触发器应该更像这样：

CREATE TRIGGER PayRate_Change ON Employees AFTER UPDATE
AS BEGIN
    INSERT INTO ChangeLog (EmpID, CHangedBy, DateChanged, OldRate, NewRate)
        SELECT i.empid, suser_sname(), getdate(), d.PsyRate, i.PsyRate
        FROM inserted i JOIN
             deleted d
             ON i.empid = d.empid AND
                d.PsyRate <> i.PsyRate;  -- does not take `NULL`s into account

END;

最重要的变化是，您不再假设只更改一行--因此代码不再有等待发生的bug。这是在SQL Server中编写触发器的一个非常重要的部分。不要假设inserted或deleted只有一行。