SQL 触发器 - 如何获取更新的值？

SQL Triggers - how do I get the updated value?

提问by naspinski

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

如何在 SQL 触发器中获取更新记录的值 - 如下所示：

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.

显然这行不通，但你可以看到我想要的。

回答by MatBailie

Provide you are certainthat 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.)

如果可以一次更新多个值，则此代码只会处理其中一个值。（虽然它不会出错。）

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

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

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

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

回答by Turcogj

Based on my knowledge you would need to create a CURSOR to loop through all the updated values to execute the UpdateProfile procedure. Keep in mind this will slow down your update process.

根据我的知识，您需要创建一个 CURSOR 来循环所有更新的值以执行 UpdateProfile 过程。请记住，这会减慢您的更新过程。

Declare @UserID int --Assuming
Declare UpdateProfile_Cursor Cursor for Select UserID From inserted;

Open Cursor UpdateProfile_Cursor;

Fetch Next from UpdateProfile_Cursor Into @UserID;

While @@FETCH_STATUS == 0
Begin
  Exec UpdateProfile  @UserID
  Fetch Next from UpdateProfile_Cursor Into @UserID;
End
CLOSE UpdateProfile_Cursor 
DEALLOCATE UpdateProfile_Cursor 

My syntax may be a little off but this will give you the desired effect. Again, consider revising your logic to handle multiple updates as using cursors is resource intensive.

我的语法可能有点不对，但这会给你想要的效果。同样，考虑修改您的逻辑以处理多个更新，因为使用游标是资源密集型的。

回答by Nicholas Carey

You can do something like this example where I'm logging changes to a transaction history table:

您可以执行以下示例，其中我将更改记录到事务历史记录表：

create table dbo.action
(
  id          int         not null primary key ,
  description varchar(32) not null unique ,
)
go

insert dbo.action values( 1 , 'insert' )
insert dbo.action values( 2 , 'update' )
insert dbo.action values( 3 , 'delete' )
go

create table dbo.foo
(
  id    int          not null identity(1,1) primary key ,
  value varchar(200) not null unique ,
)
go

create table dbo.foo_history
(
  id            int          not null ,
  seq           int          not null identity(1,1) ,
  action_date   datetime     not null default(current_timestamp) ,
  action_id     int          not null foreign key references dbo.action ( id ),
  old_value     varchar(200)     null ,
  new_value     varchar(200)     null ,

  primary key nonclustered ( id , seq ) ,

)
go

create trigger foo_update_01 on dbo.foo for insert, update , delete
as
  set nocount                 on
  set xact_abort              on
  set ansi_nulls              on
  set concat_null_yields_null on

  --
  -- record change history
  --
  insert dbo.foo_history
  ( 
    id        ,
    action_id ,
    old_value ,
    new_value
  )
  select id = coalesce( i.id , d.id ) ,
         action_id = case
                       when i.id is not null and d.id is     null then 1 -- insert
                       when i.id is not null and d.id is not null then 2 -- update
                       when i.id is     null and d.id is not null then 3 -- delete
                     end ,
         old_value = d.value ,
         new_value = i.value
  from      inserted i
  full join deleted  d on d.id = i.id

go

But you can use the same sort of technique, mix it up a bit and pass the entire set of values to a stored procedure, like I do in the following example (using the table schema above).

但是您可以使用相同类型的技术，稍微混合一下并将整个值集传递给存储过程，就像我在下面的示例中所做的那样（使用上面的表模式）。

First, create a stored procedure that expects a particular temp table to exist at runtime, thus:

首先，创建一个期望特定临时表在运行时存在的存储过程，因此：

--
-- temp table must exist or the stored procedure won't compile
--
create table #foo_changes
(
  id        int          not null primary key clustered ,
  action_id int          not null ,
  old_value varchar(200)     null ,
  new_value varchar(200)     null ,
)
go
--
-- create the stored procedure
--
create procedure dbo.foo_changed
as

  --
  -- do something useful involving the contents of #foo_changes here
  --
  select * from #foo_changes

  return 0
go
--
-- drop the temp table
--
drop table #foo_changes
go

Once you've done that, create a trigger that will create and populate the temp table expected by the stored procedure and then execute the stored procedure:

完成后，创建一个触发器，该触发器将创建并填充存储过程所需的临时表，然后执行存储过程：

create trigger foo_trigger_01 on dbo.foo for insert, update , delete
as
  set nocount                 on
  set xact_abort              on
  set ansi_nulls              on
  set concat_null_yields_null on

  --
  -- create the temp table. This temp table will be in scope for any stored
  -- procedure executed by this trigger. It will be automagickally dropped
  -- when trigger execution is complete.
  --
  -- Any changes made to this table by a stored procedure — inserts,
  -- deletes or updates are, of course, visible to the trigger upon return
  -- from the stored procedure.
  --
  create table #foo_changes
  (
    id        int          not null primary key clustered ,
    action_id int          not null ,
    old_value varchar(200)     null ,
    new_value varchar(200)     null ,
  )

  --
  -- populate the temp table
  --
  insert #foo_changes
  ( 
    id        ,
    action_id ,
    old_value ,
    new_value
  )
  select id = coalesce( i.id , d.id ) ,
         action_id = case
                       when i.id is not null and d.id is     null then 1 -- insert
                       when i.id is not null and d.id is not null then 2 -- update
                       when i.id is     null and d.id is not null then 3 -- delete
                     end ,
         old_value = d.value ,
         new_value = i.value
  from      inserted i
  full join deleted  d on d.id = i.id

  --
  -- execute the stored procedure. The temp table created above is in scope
  -- for the stored procedure, so it's able to access the set of changes from
  -- the trigger.
  --
  exec dbo.foo_changed

go

That's about all there is to it. It's simple, it's easy, it works for change sets of any size. And, it's safe, with no race conditions or collisions with other users in the system.

这就是它的全部内容。这很简单，很容易，它适用于任何大小的变更集。而且，它是安全的，没有竞争条件或与系统中的其他用户发生冲突。