SQL Triggers - how do I get the updated value?

Question

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.

Answer 1

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.)

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

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

Answer 2

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.