C# - Oracle: Triggers

Triggers en ORACLE

Un "trigger" (disparador o desencadenador) es un bloque de código que se ejecuta automáticamente cuando ocurre algún evento (como inserción, actualización o borrado) sobre una determinada tabla (o vista); es decir, cuando se intenta modificar los datos de una tabla (o vista) asociada al disparador.

Se crean para conservar la integridad referencial y la coherencia entre los datos entre distintas tablas; para registrar los cambios que se efectúan sobre las tablas y la identidad de quien los realizó; para realizar cualquier acción cuando una tabla es modificada; etc.

Si se intenta modificar (agregar, actualizar o eliminar) datos de una tabla asociada a un disparador, el disparador se ejecuta (se dispara) en forma automática.

La diferencia con los procedimientos almacenados del sistema es que los triggers:

• no pueden ser invocados directamente; al intentar modificar los datos de una tabla asociada a un disparador, el disparador se ejecuta automáticamente.
• no reciben y retornan parámetros.
• son apropiados para mantener la integridad de los datos, no para obtener resultados de consultas.

Sintaxis general para crear un disparador:

create or replace trigger NOMBREDISPARADOR

MOMENTO-- before, after o instead of

EVENTO-- insert, update o delete of

CAMPOS-- solo para update on NOMBRETABLA

NIVEL--puede ser a nivel de sentencia (statement) o de fila (for each row)

when CONDICION--

opcional begin CUERPO DEL DISPARADOR--

sentencias end NOMBREDISPARADOR;

• Ejemplo Trigger accion Insert

CREATE OR REPLACE TRIGGER TR_PRODUCTOS_01

  AFTER INSERT ON PRODUCTOS  
  FOR EACH ROW
DECLARE
  -- local variables 
BEGIN
  INSERT INTO PRECIOS_PRODUCTOS
  (CO_PRODUCTO,PRECIO,FX_ACTUALIZACION)
  VALUES
  (:NEW.CO_PRODUCTO,100,SYSDATE);
END ; 

y para ejecutar este trigger se deben ingresar datos a la tabla PRODUCTOS:

INSERT INTO PRODUCTOS
(CO_PRODUCTO, DESCRIPCION)
VALUES('000100','PRODUCTO 000100'); 


Ejemplo Trigger accion Update




CREATE OR REPLACE TRIGGER log_salary_increase

  AFTER UPDATE OF salary ON employees

  FOR EACH ROW

BEGIN

  INSERT INTO Emp_log (Emp_id, Log_date, New_salary, Action)

  VALUES (:NEW.employee_id, SYSDATE,:NEW.salary, 'New Salary');

END;
y para ejecutar este Trigger:



UPDATE employees SET salary= salary + 1000.0 WHERE Department_id = 20;





Segundo Ejemplo Trigger accion Update


CREATE or REPLACE TRIGGER emp_after_update

AFTER UPDATE OF empid ON emp

FOR EACH ROW

DECLARE

BEGIN

update emp_backup set empid = :new.empid where empid = :old.empid;

DBMS_OUTPUT.PUT_LINE('empid
successfully updated into emp_backup table');

END;

Tercer Ejemplo Trigger accion Update



create or replace trigger tr_actualizar_precio_libros

 before update of precio on libros

 for each row




 begin

  if (:new.precio>50) then :new.precio:=floor(:new.precio);

  end if;

 insert into control values(user,sysdate,:new.codigo,:old.precio,:new.precio);

 end tr_actualizar_precio_libros;


Una misma tabla puede tener varios triggers.


En tal caso es necesario conocer el orden en el que se van a ejecutar.



CREATE OR REPLACE TRIGGER t


  BEFORE INSERT OR

    UPDATE OF salary, department_id OR

    DELETE

  ON employees


BEGIN

  CASE

    WHEN INSERTING THEN

      DBMS_OUTPUT.PUT_LINE('Inserting');




    WHEN UPDATING('salary') THEN

      DBMS_OUTPUT.PUT_LINE('Updating salary');




    WHEN UPDATING('department_id') THEN

      DBMS_OUTPUT.PUT_LINE('Updating departmentID');


    WHEN DELETING THEN

      DBMS_OUTPUT.PUT_LINE('Deleting');


  END CASE;

END;


Trigger con Insert-Inserting, Update-Updating y Delete-Deleting




CREATE OR REPLACE TRIGGER TR_AUDITA
BEFORE INSERT OR DELETE OR UPDATE
ON TABLA_REGISTRO
FOR EACH ROW
DECLARE
BEGIN


IF INSERTING  THEN 
:NEW.USUARIOMOD:=USER;
:NEW.FECHAMOD:=SYSDATE;
INSERT INTO TABLA_AUDITA VALUES(USER,SYSDATE,'INGRESO EN
TABLA_REGISTRO','NOMBRE '||:NEW.NOMBRE);
END IF;

IF UPDATING THEN 
:NEW.USUARIOMOD:=USER;
:NEW.FECHAMOD:=SYSDATE;
INSERT INTO TABLA_AUDITA VALUES(USER,SYSDATE,'ACTUALIZO EN
TABLA_REGISTRO' ,'NOMBRE '||:OLD.NOMBRE||'NOMBRE '||:NEW.NOMBRE);
END IF;

IF   DELETING THEN 

INSERT INTO TABLA_AUDITA VALUES(USER,SYSDATE,'BORRO EN
TABLA_REGISTRO','NOMBRE '||:OLD.NOMBRE);
END IF;

END;



Hay que recordar que si vamos a insertar (inserting) no ponemos :old. ya que es un nuevo registro y por ende no existe.
Para updating ponemos :new y :old, ya que si actualizamos va a ser de un registro existente y de ello se va a derivar un registro nuevo.
En el deleting solo ponemos :old debido a que vamos a eliminar un registro viejo, no va a ser un registro nuevo ovbiamente.


Utilización de :OLD y :NEW


Dentro del ambito de un trigger disponemos de las variables OLD y NEW . Estas variables se utilizan del mismo modo que cualquier otra variable PL/SQL, con la salvedad de que no es necesario declararlas, son de tipo %ROWTYPE y contienen una copia del registro antes (OLD) y despues(NEW) de la acción SQL (INSERT, UPDATE, DELTE) que ha ejecutado el trigger. Utilizando esta variable podemos acceder a los datos que se están insertando, actualizando  o borrando.

La siguiente tabla muestra los valores de OLD y NEW.


ACCION SQL

OLD

NEW

INSERT
No definido; todos los campos toman valor NULL.
Valores que serán insertados cuando se complete la orden.
UPDATE
Valores originales de la fila, antes de la actualización.
Nuevos valores que serán escritos cuando se complete la orden.
DELETE
Valores, antes del borrado de la fila.
No definidos; todos los campos toman el valor NULL.




Los registros OLD y NEW son sólo válidos dentro de los disparadores con nivel de fila.