更新SQL Server，特别是SQL Server 2005

Question

如何在SQL Server 2005中检测更新的值?

Answer 1

请注意，您不应该尝试获取该值，因为应该对触发器进行编码以处理多行更改

也就是说，您可以使用特殊的DELETED和insert表

CREATE TRIGGER TRG_SomeTable_U ON dbo.SomeTable AFTER UPDATE 
AS 
-- DECLARE @ TABLE ...
-- INSERT HistoryTable
-- whatever
SELECT
    ...
FROM
   INSERTED I
   JOIN
   UPDATED U ON I.PK 0 U.PK
WHERE
  I.SomeColumn <> U.SomeColumn -- does not handle NULLs
GO

Answer 2

IF UPDATE (mycol1) 

。

CREATE TRIGGER reminder
ON Person.Address
AFTER UPDATE 
AS 
IF ( UPDATE (StateProvinceID) OR UPDATE (PostalCode) )
BEGIN
    RAISERROR (50009, 16, 10)
END;
GO

Ref .

如果您引用的是实际值，则这些值保存在inserted和deleted表中。参见使用插入和删除的表

Answer 3

我发现这个答案是真的!!UPDATE触发器用于在对表进行更新后执行操作。

CREATE TRIGGER tr_Orders_UPDATE
ON Orders
AFTER UPDATE
AS
--Make sure Priority was changed
IF NOT UPDATE(Ord_Priority)
RETURN
--Determine if Priority was changed to high
IF EXISTS (SELECT *
       FROM INSERTED a
   JOIN DELETED b ON a.Ord_ID=b.Ord_ID
   WHERE b.Ord_Priority <> 'High' AND
   a.Ord_Priority = 'High')
BEGIN
DECLARE @Count tinyint
SET @Count = (SELECT COUNT(*)
            FROM INSERTED a
    JOIN DELETED b ON a.Ord_ID=b.Ord_ID
    WHERE b.Ord_Priority <> 'High' AND
    a.Ord_Priority = 'High')
PRINT CAST(@Count as varchar(3))+' row(s) where changed to a priority of High'
END
go

在第一部分中，INSERT触发器监视优先级为'High '的订单。UPDATE触发器监视那些优先级从其他更改为High的订单。

IF语句检查Ord_Priority值是否被更改。如果没有，我们可以通过立即退出触发器来节省一些时间。

删除的表保存update前的值，插入的表保存新值。当表连接时，很容易判断优先级何时从其他级别更改为高级别。

参考