trigger - Disparadores SQL: ¿cómo obtengo el valor actualizado?

trigger en sql server 2014

Question

¿Cómo obtengo el valor del registro actualizado en un desencadenador SQL? Algo como esto:

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

    EXEC UpdateProfile (SELECT UserId FROM updated AS U);

END
GO

Obviamente esto no funciona, pero pueden ver a lo que intento llegar.

Answer 1

Puede hacer algo como este ejemplo en el que estoy registrando cambios en una tabla de historial de transacciones:

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

Pero puede usar el mismo tipo de técnica, mezclarlo un poco y pasar todo el conjunto de valores a un procedimiento almacenado, como hago en el siguiente ejemplo (usando el esquema de la tabla anterior).

Primero, cree un procedimiento almacenado que espera que exista una tabla temporal particular en tiempo de ejecución, por lo tanto:

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

Una vez que haya hecho eso, cree un disparador que creará y rellenará la tabla temporal que espera el procedimiento almacenado y luego ejecutará el procedimiento almacenado:

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

Eso es todo lo que hay para eso. Es simple, es fácil, funciona para conjuntos de cambios de cualquier tamaño. Y es seguro, sin condiciones de carrera o colisiones con otros usuarios en el sistema.

Answer 2

Según mis conocimientos, necesitaría crear un CURSOR para recorrer todos los valores actualizados y ejecutar el procedimiento UpdateProfile. Tenga en cuenta que esto ralentizará su proceso de actualización.

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 

Mi sintaxis puede estar un poco desajustada, pero esto te dará el efecto deseado. Nuevamente, considere revisar su lógica para manejar múltiples actualizaciones ya que el uso de cursores consume muchos recursos.

Answer 3

Siempre que esté seguro de que solo se actualizará un solo valor, puede hacerlo ...

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

Si se pueden actualizar varios valores a la vez, solo uno de ellos será procesado por este código. (Aunque no va a error.)

Podría usar un cursor, o si es SQL Server 2008+ puede usar variables de tabla.

O, más comúnmente, simplemente mueva el código StoredProcedure al disparador.