Triggers

Triggers
=========

El modificador FOR EACH ROW indica que el trigger se disparará cada vez que se desee hacer operaciones sobre una fila de la tabla. Si se acompaña del modificador WHEN, se establece una restricción; el trigger solo actuará, sobre las filas que satisfagan la restricción.

CREATE OR REPLACE TRIGGER "INSERCIONNUMEROMAYOR1000"
AFTER INSERT ON "PRUEBA"
FOR EACH ROW WHEN (new.CAMPO2 > 1000)
BEGIN
insert into resultadodisparador (fecha, aviso, tabla)
values (Sysdate, 'Registro con CAMPO2 superior a 1000','PRUEBA');
END;

Con este disparador, cuando un usuario inserte un registro en la tabla PRUEBA cuyo CAMPO2 tenga un valor superior a 1000 se insertará automáticamente (transparente para el usuario) otro registro en la tabla "resultadodisparador" con la fecha de la inserción (sysdate), el aviso "Registro con CAMPO2 superior a 1000" y el nombre de la tabla origen del disparador "PRUEBA".

Obviamente, para que este disparador funcione correctamente deberán existir las tablas PRUEBA (origen del disparador) y "resultadodisparador" (donde se insertará el registro si se cumple la condición CAMPO2 > 1000.

La consulta SQL necesaria para crear la tabla "PRUEBA":

CREATE TABLE "PRUEBA" (
"CAMPO1" VARCHAR2(10) NOT NULL,
"CAMPO2" NUMBER)

La consulta SQL necesaria para crear la tabla "resultadodisparador":

CREATE TABLE "RESULTADODISPARADOR" (
"FECHA" DATE NOT NULL,
"AVISO" VARCHAR2(100) NOT NULL,
"TABLA" VARCHAR2(50) NOT NULL)

insert into PRUEBA values ('Primer', 100);
insert into PRUEBA values ('Segundo', 110);
insert into PRUEBA values ('Tercero', 1001);
insert into PRUEBA values ('Cuarto', 1200);

ALGUNAS CONSULTAS SQL PARA MODIFICAR EL ESTADO DE UN TRIGGER
=============================================================

para conocer los triggers creados

select trigger_name
from user_triggers;

Para eliminar un trigger mediante SQL:

drop trigger nombretrigger

Para deshabilitar temporalmente un trigger (dejará de realizar su función):

alter trigger nombretrigger disable

Para habilitar un trigger deshabilitado:

alter trigger nombretrigger enable

Para deshabilitar todos los triggers asociados a una tabla:

alter table nombretabla disable all triggers

Para habilitar todos los triggers asociados a una tabla:

alter table nombretabla enable all triggers



Este trigger impide que se agregue o modifique un empleado con el sueldo mayor o menor que los valores maximo y minimo respectivamente para su cargo. Se agrega la restricción de que el trigger no se dispararán si el cargo es PRESIDENTE.



CREATE TRIGGER salary_check
BEFORE
INSERT OR UPDATE OF sal, job
ON employee
FOR EACH ROW
WHEN (new.job <> 'PRESIDENT')
DECLARE
minsal NUMBER
maxsal NUMBER
BEGIN
/* Se obtienen los valores minimo y maximo para el salario de */
/* un cargo determinado, usando la tabla sal_guide */
SELECT minsal, maxsal
INTO minsal, maxsal
FROM sal_guide
WHERE job = :new.job
/* Si el salario del empleado a insertar/modificar esta por */
/* debajo del minimo, o por encima del maximo, se genera */
/* un error. */
IF (:new.sal <> maxsal)
THEN raise_application_error(-20601, 'Salary '||:new.sal||
' out of range for job '||:new.job||' for employee '||
:new.ename);
END IF;
END;


" Programar un disparador que calcule el campo código de pieza (P#) cada vez que se inserte una nueva pieza"

Disponenos de tres tablas que son:

Suministrador
---------------
S# NombreS Dirección Ciudad
2 Juan C/ Pelayo Málaga
3 Luis C/ Pato Málaga
4 Pablo C/Alfonso X Granada

Pieza
-----
P# NombreP Peso Cantidad
2 Tornillo 16 20
4 Tuerca 8 20
8 Clavos 7 30

Sumimistros
-----------
S# P#
2 4
2 8
4 2
3 2

CREATE OR REPLACE TRIGGER NuevaPieza
BEFORE INSERT ON Pieza FOR EACH ROW
BEGIN
-- Establecer el nuevo número de pieza:
SELECT MAX(P#)+1 INTO :new.P# FROM Pieza;
IF :new.P# IS NULL THEN
:new.P# := 1;
END IF;
END NuevaPieza;

Hay muchas circunstancias en las que el uso de un trigger PL/SQL puede ser extremadamente útil, por ejemplo:

- Cuando los datos de una tabla son generados desde otro tipo de procedimientos y se necesita controlar los valores que toman algunos campos determinados de la tabla en cuestión.

- Para duplicar los contenidos de una tabla automáticamente y en tiempo real.

- Para implementar complejas restricciones sobre los valores que pueden tomar los campos de una tabla Oracle, es decir, cuando los CONSTRAINTS que se pueden definir sobre una tabla son insuficientes.

- Para controlar las modificaciones de los valores de los campos de una tabla (auditorías).

- Para incrementar automáticamente los valores de un campo.

- Para realizar actualizaciones de una tabla en cascada.

- Para modificar campos o registros de una tabla que un usuario no puede modificar directamente.

Los triggers PL/SQL constituyen una potente herramienta para mantener la integridad de la base de datos, ya que pueden llevar a cabo cualquier acción que sea necesaria para el mantenimiento de dicha integridad.

Los triggers PLSQL pueden llamar a otros procedimientos y disparar otros triggers, pero no admiten parámetros y no pueden ser invocados desde otros procedimientos PLSQL.

Los triggers están almacenados en la tabla catálogo del sistema como parte de las propiedades de una tabla.

Tipos de triggers PLSQL

Los triggers PLSQL pueden actuar antes o después de que se realice una inserción, un borrado o una actualización de un registro de la tabla Oracle involucrada.

Se pueden definir triggers PL/SQL diferentes para cada tipo de evento (INSERT, UPDATE, DELETE) pero lo más lógico es crear un único trigger para todos los eventos y usar una sentencia IF para distinguir que acción realizar dependiendo del evento.

Por otro lado los triggers pueden ser a nivel de registro (row) o a nivel de sentencia (statement).

- A nivel de registro o fila el trigger PL/SQL se ejecuta cada vez que un registro es actualizado, insertado o borrado.

- A nivel de sentencia, el trigger se ejecuta una vez que la sentencia PL/SQL INSERT, UPDATE o INSERT se completa. Obviamente en este caso el trigger sólo puede ser ejecutado después (AFTER) de que se ejecute dicha sentencia.

Ejemplo

CREATE or replace TRIGGER tr1_empleado
BEFORE INSERT OR UPDATE OF salario
ON empleado
FOR EACH ROW
WHEN (new.salario > 5000)
BEGIN
UPDATE empleado
SET salario = 5000
WHERE numEmpleado = :new.numEmpleado;
END;







Otro ejemplo:


create table Autor (
idAutor integer,
nomAutor varchar2(30)
)

Insert into Autor values (1, 'Juan');
insert into autor values (2, 'pedro');
insert into autor values (3, 'Diego');

create table libro (
idAutor integer,
idlibro integer,
nomLibro varchar2(25)
)

Insert into Libro values (1, 1, 'Salmo 25');
Insert into Libro values (1, 2, 'La iliada');
Insert into Libro values (1, 3, 'El Quijote');
Insert into Libro values (2, 4, 'La Araucana');
Insert into Libro values (2, 5, 'Damian');
Insert into Libro values (3, 6, 'Sidharta');


CREATE TRIGGER t1
Before DELETE
ON Autor
FOR EACH ROW
Begin
Delete Libro
where Libro.IdAutor = :old.IdAutor;

End;


Otro Ejemplo
============

Create table Compra(
idCompra Integer,
fecCompra Date
);

Insert into Compra values (1, '25-01-2009');
Insert into Compra values (2, '28-02-2009');
Insert into Compra values (3, '24-03-2009');

Create table Producto(
idProducto integer,
nombreProducto varchar2(25),
Stockactual integer
);

insert into Producto values (1, 'Tornillo', 0);
insert into Producto values (2, 'Tuerca', 0);
insert into Producto values (3, 'Golilla', 0);


Create table detalleCompra(
idcompra Integer,
idProducto integer,
cantidad integer
);


Create trigger ActualizaStockCompra
before insert
ON DetalleCompra
FOR EACH ROW
Begin
Update Producto
Set StockActual = StockActual + :new.Cantidad
where Producto.IdProducto = :new.IdProducto;

End;

insert into detalleCompra values (1, 1, 10);
insert into detalleCompra values (1, 2, 100);
insert into detalleCompra values (1, 3, 1000);
insert into detalleCompra values (2, 1, 20);
insert into detalleCompra values (2, 2, 10);
insert into detalleCompra values (3, 1, 10);



Create table Merma(
idMerma integer,
fecmerma date,
idProducto integer,
cantidad integer
);


Create trigger ActualizaStockMerma
before insert
ON Merma
FOR EACH ROW
Begin
Update Producto
Set StockActual = StockActual - :new.Cantidad
where Producto.IdProducto = :new.IdProducto;

End;


insert into merma values (1, '25-09-2009', 1, 10);
insert into merma values (2, '28-09-2009', 1, 1);
insert into merma values (3, '25-10-2009', 2, 10);


Otro triggers

create table Person (age int);

CREATE or replace TRIGGER PersonCheckAge
BEFORE INSERT OR UPDATE OF age
ON Person
FOR EACH ROW
WHEN (new.age < 0)
BEGIN
RAISE_APPLICATION_ERROR(-20000, 'No se permiten edades negativas');
END;

CREATE or replace TRIGGER PersonCheckAge
BEFORE INSERT OR UPDATE OF age
ON Person
FOR EACH ROW
BEGIN
if (:new.age < 0) then
RAISE_APPLICATION_ERROR(-20000, 'No se permiten edades negativas');
end if;
END;


Si intentamos ejecutar la inserción:

insert into Person values (-3);