martes, 2 de marzo de 2010

Cómo saber si es Insert o Update Trigger AFTER INSERT,UPDATE - SQL SERVER

Se tien dos tablas:Empleado y Empleado_Log, se quiere que cada vez que se haga un cambio o una nueva inserción de registro en la tabla Empleado se guarde el nuevo registro o el modificado en la tabla Empleado_Log se debe especificar si fue un insert o un update en una de las columnas, la cual en este ejemplo se llama: "accion".

Ejemplo:


--TABLAS:
create table Empleado(
id int not null,
nombre varchar(20),
apellido varchar(20),
edad int,
editable varchar(2)
)
--tabla log
create table Empleado_Log(
idEmpleado int,
nombre varchar(20),
apellido varchar(20),
edad int,
editable varchar(2),
accion varchar(20)
)

--trigger que se disparará cuando se haga un insert o un update
create trigger trg_Example
on Empleado
after insert,update
as
begin
declare
@idEmp int,
@nombre varchar(20),
@apellido varchar(20),
@edad int,
@editable varchar(2),
@accion varchar (20)

select @idEmp=id,@nombre=nombre,@apellido=apellido,@edad=edad,
@editable=editable
from inserted
begin
--Si hay valores en la tabla deleted y además todos(excepto
--editable y id) son diferentes a los nuevos valores insertados,
--significa que ha habido un update
if exists(select * from deleted d join inserted i on
d.id=i.id where d.nombre != i.nombre or
d.apellido != i.apellido or d.edad != i.edad)
begin
set @accion ='record updated'
insert into Empleado_Log values(@idEmp,@nombre,@apellido,
@edad,@editable,@accion)
end
--Si no ha habido un update hay valores sólo en la tabla inserted
--y eso significa que ha habido un insert.No se debe olvidar
--que un update es un delete y un insert.
else if exists(select id from inserted)
and not exists(select id from deleted)
begin
set @accion ='record inserted'
insert into Empleado_Log values(@idEmp,@nombre,@apellido,
@edad,@editable,@accion)
end
end
end

--probando
insert into Empleado values (1,'pedro','sanchez',30,'si')

insert into Empleado values (2,'Luis','Perez',32,'no')

update Empleado set nombre='josse',apellido='diaz',
edad=21,editable='si' where id=1

update Empleado set nombre='Juan' where id=2

delete from Empleado_Log

select * from Empleado_Log

--borrando tablas y trigger
drop table Empleado_Log
drop table Empleado
drop trigger trg_Example

1 comentario:

  1. Hace un tiempo necesitaba identificar cuando era un insert,update (incluso delete) como lo indica el titulo de tu mensaje he utilizado sumas, segun vi como reaccionaba las tablas inserted y deleted

    Te dejo un ejemplo usando tus tablas (solo cambiando el trigger):

    ------------[CODE]---------------
    CREATE TRIGGER trg_Example
    on Empleado
    after insert,update,delete
    as
    Declare @Accion Int
    SELECT @Accion = Sum(A) FROM(SELECT 1 'A',I.* FROM inserted I
    Union
    SELECT 2 'A',D.* FROM deleted D
    ) As Movimiento

    IF @Accion =1
    BEGIN
    INSERT INTO Empleado_Log
    SELECT I.*,'record inserted' 'Accion' FROM inserted I
    END
    ELSE
    BEGIN
    IF @Accion =2
    BEGIN
    INSERT INTO Empleado_Log
    SELECT D.*,'record deleted' 'Accion' FROM deleted D
    END
    ELSE
    BEGIN
    INSERT INTO Empleado_Log
    SELECT I.*,'record updated' 'Accion' FROM inserted I

    END
    END

    ------------[/CODE]---------------

    Tal vez pueda ser de utilidad

    Saludos

    ResponderEliminar