Triggers en PL/SQL

Hola a todos, hoy os voy a explicar como funcionan los triggers en PL/SQL.

Los triggers son como procedimientos que se ejecutan automáticamente al insertar, actualizar o borrar registros de una tabla en concreto. Según lo que nosotros le indiquemos.

Un ejemplo práctico de trigger, es cuando tenemos una tienda con pedidos y productos con stock, al hacer un pedido, podemos hacer que el stock de productos disminuya automáticamente.

CREATE [OR REPLACE ] TRIGGER trigger_name  
{BEFORE | AFTER | INSTEAD OF }  
{INSERT [OR] | UPDATE [OR] | DELETE}  
[OF col_name]  
ON table_name  
[REFERENCING OLD AS o NEW AS n]  
[FOR EACH ROW]  
WHEN (condition)   
DECLARE 
   Declaration-statements 
BEGIN  
   Executable-statements 
EXCEPTION 
   Exception-handling-statements 
END; 

Veamos un pequeño ejemplo con nuestra base de datos jardinería (puedes descargarla aquí):

create or replace trigger productos_actualizar_stock
after insert on detallepedidos FOR EACH ROW
declare 
begin
   
   UPDATE productos 
   SET cantidadenstock = cantidadenstock - :new.cantidad 
   where codigoproducto = :new.codigoproducto;
   
end;

Lo que estoy haciendo en este trigger es que después (AFTER) de insertar (INSERT) en la tabla detallepedidos (on detallepedidos) actualizo el stock de cada producto que haya insertado en la tabla (FOR EACH ROW). Este FOR EACH ROW significa que es un disparador a nivel de fila.

Seguramente, os estaréis preguntando que es :new, hace referencia al elemento que hemos insertado, si hemos insertado 3 filas, esto lo hace 3 veces, donde en cada uno tiene acceso a todo lo que insertado, en este caso, la cantidad.

IMPORTANTE: si usas :new y :old, tienes que usar por fuerza FOR EACH ROW

Ya tengo el trigger, ¿Ahora qué?

Voy a insertar un detallepedido a un producto ya existente.

Si hago esta consulta:

select codigoproducto, nombre ,cantidadenstock from productos where codigoproducto = 'AR-010';

Devuelve esto:

Si hago este insert:

insert into detallepedidos values (1, 'AR-010', 5, 10, 6);

Si hago de nuevo la consulta, fijaos que se ha actualizado:

Veamos otro ejemplo más:

create or replace trigger actualizar_estado_pedido
before update of fechaentrega on pedidos
FOR EACH ROW
declare

begin
 
 if :new.fechaentrega > :old.fechaesperada then
    :new.comentarios := :old.comentarios || ' Pedido entregado con retraso.';
 else
    :new.comentarios :=  :old.comentarios || ' Pedido entregado antes de lo esperado.';
 end if;
 
    
end;
/

En este trigger, antes de actualizar (UPDATE) la columna fechaentrega (of fechaentrega) de la tabla pedidos (on pedidos) para cada fila que actualicemos (FOR EACH ROW).

Si queréis actualizar la tabla donde referencia el trigger, tenéis que hacerlo de esa forma. Sino lo hacéis así y ponéis un update, os va a decir que la tabla esta mutando:

ORA-04091: la tabla X está mutando, puede que el disparador/la función no puedan verla.

Vamos a actualizar un pedido:

update pedidos set fechaentrega = to_date('01/01/05') where codigopedido = 1;

Si esto se hace bien y vemos la tabla de pedidos:

Si lo actualizamos de esta forma:

update pedidos set fechaentrega = to_date('01/01/15') where codigopedido = 1;

Se actualizara los comentarios de esta forma:

Os dejo estos vídeos:

Espero que os sea de ayuda. Si tenéis duda, preguntad, estamos para ayudarte.