Desencadenadores en SQL Server ( Trigger Instead Of )

Regresar al índice

Trigger Instead Of

Es un tipo de trigger, que asociado a una vista, cuando se intenta realizar el tipo de consulta que indica el trigger (insertar, modificar, o eliminar), una vez están los registros en las tablas inserted o deleted, la consulta se interrumpe y salta el trigger, con lo que podemos manejar los datos que hay en estas tablas temporales mediante el trigger, esto es muy práctico cuando queremos insertar en varias tablas pertenecientes a una vista, ya que con una simple consulta no podremos, tendremos que trabajar con un trigger Instead Of y usar las filas incluidas en la tabla inserted.

Sintaxis:

Create Trigger NombreTrigger
On Vista
Instead Of
As
Sentencias SQL

Un ejemplo práctico sería el siguiente:

Tenemos una vista que hace referencia a Departamentos y empleados, y contiene el nº del departamento y nombre de la tabla departamentos y el nombre y apellidos de la tabla empleados. Si intentamos hacer un insert sobre la vista, sin el trigger dará error y no dejará insertarlo, pero en cambio, con el triger: Cuando hagamos la inserción salta el trigger. Mediante el trigger, accedemos a la tabla inserted, e insertamos los valores necesarios en la tabla departamentos y empleados.

Ejemplo 1:

Creamos una vista que combine la tabla Doctor y Plantilla

CREATE View Emp_Hospital
As
Select Doctor_no, Apellido, Especialidad, Hospital_Cod
From Doctor
Union
Select Empleado_no, Apellido, Funcion, Hospital_Cod
From Plantilla

Creamos un trigger para esta vista.

Create Trigger Inserta_Emp
On Emp_Hospital
Instead Of Insert
As
If (Select count(*) from Inserted) > 1
Print 'Solo se puede insertar una fila cada vez'
If (Select Len([Doctor_no]) from Inserted) = 3
Insert into Doctor(Doctor_no, Apellido, Especialidad, Hospital_cod)
Select * from Inserted
Else
Insert into Plantilla(Empleado_no, Apellido, Funcion, Hospital_Cod)
Select * from Inserted
Return

Para ver un trigger de una vista mediante el explorador de objetos, seguir la siguiente ruta: BASE DE DATOS > VISTAS > [Nombre Vista] > Triggers > [Nombre Trigger].

La imagen ilustra el ejemplo anterior.

Ejemplo2:

Nº EMPLEADO, APELLIDO, OFICIO Y NOMBRE DE DEPARTAMENTO

create view VistaEmpDept
as
select Emp_No, Apellido, Oficio, DNombre
from Emp e inner join Dept d
on e.Dept_No = d.Dept_No 

La idea es que al insertar sobre la vista el siguiente registro se grabe los datos en la table Emp:

insert into VistaEmpDept (Emp_no, Apellido, Oficio, Dnombre) Values
('8888','Angulo','Vendedor','Investigación')

El trigger instead of saltará e insertará el empleado con su correspondiente nº de departamento, ya que en empleados no hay nombre de departamento, con lo que averiguaremos el nº de departamento haciendo una select sobre la tabla de departamentos.

Create Trigger Inserta_Emple
On VistaEmpDept
Instead Of Insert
As
declare @Dept int
select @dept = dept_no from dept,inserted
where dept.dnombre = inserted.dnombre
if @dept is null
Begin
print 'No existe departamento con ese nombre'
print 'No se ha realizado inserción'
End
else
insert into emp (emp_no,apellido,oficio,dept_no)
select inserted.emp_no,inserted.apellido
,inserted.oficio,@dept from inserted

Si realizo ahora la inserción sobre la vista, el empleado se va a insertar primero sobre la tabla temporal, yo evaluaré los datos que se han insertado en la tabla temporal para decidir si me sirven o no. En este caso en concreto no me sirven porque estoy insertando un departamento que no existe, Investigación está sin acento en la tabla.

insert into VistaEmpDept (Emp_no, Apellido, Oficio, Dnombre)
Values (8888,'ANGULO','VENDEDOR','Investigacion' )

No existe departamento con ese nombre
No se ha realizado inserción

(1 fila afectada)

Si cumplo los requisitos que yo he puesto en el trigger sobre la vista, me permitirá insertar el empleado.

insert into VistaEmpDept (Emp_no, Apellido, Oficio, Dnombre)
Values (8888,'ANGULO','VENDEDOR','Investigación')

(1 fila afectada)
(1 fila afectada)

Otro Ejemplo :

Create Trigger Inserta_Empleado
On Emp
Instead Of Insert
As
declare @dir int
declare @emp_no int
declare @oficio nvarchar(30)
select @dir = inserted.dir from inserted
select @emp_no = emp_no, @oficio = oficio from emp
where emp_no = @dir
if @emp_no is null
begin
print 'Fallo, no se ha realizado la inserción'
print 'El empleado insertado no tiene director adecuado'
end
else
begin
if @oficio = 'DIRECTOR' OR @OFICIO = 'PRESIDENTE'
BEGIN
insert into emp
select * from inserted
END
ELSE
BEGIN
print 'Fallo, no se ha realizado la inserción'
print 'El dir no es director'
END
end
Return

Espero haber ayudado en algo. Hasta la próxima oportunidad!

DESCARGAR PUBLICACIÓN

Regresar al índice

Twittear