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.