SQL触发器-如何获取更新的值？ [英] SQL Triggers - how do I get the updated value?

问题描述

如何获取SQL触发器中更新记录的值-像这样：

How do I get the value of the updated record in a SQL trigger - something like this:

CREATE TRIGGER TR_UpdateNew
   ON  Users
   AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    EXEC UpdateProfile (SELECT UserId FROM updated AS U);

END
GO

显然这是行不通的，但是您可以看到我想要得到的东西。

Obviously this doesn't work, but you can see what I am trying to get at.

推荐答案

只要您确定一定一个值将被更新，您可以执行此操作...

Provide you are certain that only one value will ever be updated, you can do this...

CREATE TRIGGER TR_UpdateNew
   ON  Users
   AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @user_id INT
    SELECT
      @user_id = inserted.UserID
    FROM
      inserted
    INNER JOIN
      deleted
        ON inserted.PrimaryKey = deleted.PrimaryKey
        -- It's an update if the record is in BOTH inserted AND deleted

    EXEC UpdateProfile @user_id;

END
GO

如果可以在更新多个值一次，此代码将只处理其中之一。 （尽管不会出错。）

If multiple values can be updated at once, only one of them will get processed by this code. (Although it won't error.)

您可以使用游标，如果是SQL Server 2008+，则可以使用表变量。

You could use a cursor, or if it's SQL Server 2008+ you can use table variables.

或更常见的是，只需将StoredProcedure代码移到触发器中即可。

Or, more commonly, just move the StoredProcedure code into the trigger.

这篇关于SQL触发器-如何获取更新的值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！