Teoria triggers

De wikijoan
Salta a la navegació Salta a la cerca

Introducció

Un trigger (disparador) és un bloc de codi PL/SQL que és executat per un event que succeeix en una taula de la base de dades. Els triggers s'invoquen de forma implícita per comandes DML (INSERT, DELETE, UPDATE). Per millorar el rendiment no s'ha d'incloure molt codi dins els triggers, sinó que s'executen a partir de procediments i paquets (packages) prèviament guardats i compilats. No s'ha d'utilitzar COMMIT, ROLLBACK i SAVEPOINT en els blocs de trigger.

S'utilitzen per:

  • Implementar restriccions complexes de seguretat o integritat
  • Prevenir transaccions errònies
  • Implementar regles administratives complexes
  • Generar automàticament valors derivats
  • Auditar les actualitzacions i enviar alertes
  • Gestionar rèpliques remotes de la tau

La sintaxi bàsica és: 

CREATE [OR REPLACE] TRIGGER nom_trigger
{ BEFORE | AFTER }{ DELETE | INSERT | UPDATE [OF llista_cols]}
ON nom_taula
[ FOR EACH { STATEMENT | ROW [WHEN (condició) ] } ]

[ DECLARE
declaracions ]
BEGIN
sentències
[ EXCEPTION
gestió_excepcions]
END;

En un trigger podem distingir els següents elements:

  • Nom del trigger
  • event de tret-disparo: INSERT, DELETE, UPDATE. S'inclou la taula a la que el trigger queda associat.
  • Tipus de trigger:
    • segons el moment en què s'executa: BEFORE o AFTER
    • segons el número de vegades que s'executa pot ser: a nivell d'ordre (STATEMENT); a nivell de fila (FOR EACH ROW)
  • Restricció del trigger: clàusula WHEN. Només es pot utilitzar en triggers a nivell de fila. Es tracta d'una condició SQL, no PL/SQL. No pot incloure una consulta a altres taules o vistes.
  • cos del trigger

Recordar que els triggers poden executar-se milers de vegades en un llarg update, i això pot afectar seriosament el rendiment del motor SQL.

Els triggers es criden ABANS (BEFORE) i DESPRÉS (AFTER) dels següents esdeveniments: INSERT, UPDATE i DELETE.

Hi ha dos tipus de triggers: de SENTÈNCIA (STATEMENT) o de FILA (ROW). Els de sentència es disparen abans o després de la sentència que causa el tret del trigger. Els de fila es disparen abans o després que una fila es veu afectada (tantes vegades com files afectades).

Valors NEW i OLD

...IF :new.salari < :old.salari THEN

quan s'utiltza la condició dins la clàusula WHEN: WHEN new.salari < old.salari

  • DELETE: només :old.nomcolumna, doncs :new.nomcolumna IS NULL
  • INSERT: només :new.nomcolumna, doncs :old.nomcolumna IS NULL
  • UPDATE: tenen sentit :old.nomcolumna i :new.nomcolumna

Els triggers s'invoquen de forma implícita per comandes DML. Per millorar el rendiment més val no incloure molt codi dins els triggers, sinó que s'executen a partir de procediments i paquets (packages) prèviament guardats i compilats. No s'ha d'utilitzar COMMIT, ROLLBACK i SAVEPOINT en els blocs de trigger. Recordar que els triggers poden executar-se milers de vegades en un llarg update, i això pot afectar seriosament el rendiment del motor SQL.

Exemple bàsic

dos exemples bàsics: 

create table auditaremple(
camp1 varchar2(255)
);
/

CREATE OR REPLACE TRIGGER auditar_pujada_salari
AFTER UPDATE OF salari
ON emple
FOR EACH ROW
BEGIN
insert INTO auditaremple VALUES('Pujada del salari ' || :old.emp_no || ' ' || :old.salari || ' -> ' || :new.salari);
END;
/

select count(*) from auditaremple;

update emple set salari=salari+5 where emp_no=7902;

select count(*) from auditaremple;
select * from auditaremple where camp1 like '%7902%';

CREATE TABLE AUDITAR_EMPLE (
   TEXT VARCHAR2(50),
   DIA_HORA DATE
);

CREATE OR REPLACE TRIGGER audit_esborrar_emple
   BEFORE DELETE ON EMPLE FOR EACH ROW
BEGIN
   INSERT INTO AUDITAR_EMPLE 
      VALUES ('esborrat empleat: ' || :old.emp_no || '*' || :old.cognom || '*' || :old.dept_no, SYSDATE);
END;
/

Exemples MYTRIG1 i MYTRIG2

Les taules que necessites: 

CREATE TABLE LLIBRE (
ID NUMBER(2),
ISBN VARCHAR2(10),
TITOL VARCHAR2(20)
);

INSERT INTO LLIBRE VALUES(1,'ISBN001','llibre1');
INSERT INTO LLIBRE VALUES(2,'ISBN002','llibre2');
INSERT INTO LLIBRE VALUES(3,'ISBN003','llibre3');
INSERT INTO LLIBRE VALUES(4,'ISBN004','llibre4');
COMMIT;

CREATE TABLE DEL_LLIBRE (
PREVISBN VARCHAR2(10),
TITOL VARCHAR2(20),
DATABORRAT DATE
);

CREATE TABLE INS_LLIBRE (
ISBN VARCHAR2(10),
TITOL VARCHAR2(20),
AFEGIR_DATA DATE
);

CREATE TABLE MOD_LLIBRE (
ANTIC_ISBN VARCHAR2(10),
NOU_ISBN VARCHAR2(10),
TITOL VARCHAR2(20),
MOD_DATE DATE
);

CREATE TABLE ALTRE_LLIBRE (
ISBN VARCHAR2(10),
TITOL VARCHAR2(20),
MOD_DATE DATE
);

Un exemple de trigger de sentència (statement): 

CREATE OR REPLACE TRIGGER MYTRIG1 
BEFORE DELETE OR INSERT OR UPDATE ON LLIBRE
BEGIN
   IF (TO_CHAR(SYSDATE,'dy') IN ('sat','sun')) OR (TO_CHAR(SYSDATE,'hh24:mi') NOT BETWEEN '08:30' AND '18:30') THEN
      RAISE_APPLICATION_ERROR(-20500,'Table is secured');
   END IF;
END;
/

comentaris: compte amb l'idioma del servidor Oracle. Potser hem de posar 'sab' i 'dom'.

El nom del trigger és MYTRIG1. El trigger ens diu que abans de qualsevol canvi en la taula LLIBRE aquest codi PL/SQL es compilarà i s'executarà. L'usuari no tindrà permís per modificar la taula LLIBRE fora de l'horari normal de treball.

Un exemple de trigger de fila (row): 

CREATE OR REPLACE TRIGGER MYTRIG2 
AFTER DELETE OR INSERT OR UPDATE ON LLIBRE
FOR EACH ROW
BEGIN
   IF DELETING THEN
      INSERT INTO DEL_LLIBRE (PREVISBN, TITOL, DATABORRAT) VALUES (:OLD.ISBN, :OLD.TITOL, SYSDATE); 
   ELSIF INSERTING THEN
      INSERT INTO INS_LLIBRE (ISBN, TITOL, AFEGIR_DATA) VALUES (:NEW.ISBN, :NEW.TITOL, SYSDATE); 
   ELSIF UPDATING ('ISBN') THEN
      INSERT INTO MOD_LLIBRE (ANTIC_ISBN, NOU_ISBN, TITOL, MOD_DATE) VALUES (:OLD.ISBN, :NEW.ISBN, :NEW.TITOL, SYSDATE);
   ELSE /* MODIFICACIO DE CAMPS DIFERENTS QUE ISBN*/
      INSERT INTO ALTRE_LLIBRE (ISBN, TITOL, MOD_DATE) VALUES (:OLD.ISBN, :NEW.TITOL, SYSDATE); 
   END IF;
END;
/

En aquest cas hem especificat que el trigger s'executarà després de qualsevol modificació de dades en qualsevol fila afectada. In this case we have specified that the trigger will be executed after any data change event on any affected row. En el codi PL/SQL podem comprovar quina acció de modificació s'està produint en la fila afectada actual i prendre l'acció que es consideri oportuna. És de notar que podem especificar els valors antic i nou de les files afectades prefixant els noms de les columnes amb els qualificadors :OLD i :NEW.

Tipus de disparadors i ordre d'execució

Una mateixa taula pot tenir diferents disparadors. L'ordre de disparo és el següent:

a) abans d'executar l'ordre que produeix el disparo: BEFORE...FOR EACH STATEMENT

b) per cada fila afectada per l'ordre:

  • primer s'executa BEFORE...FOR EACH ROW
  • després s'executa les ordres INSERT, DELETE o UPDATE
  • finalment s'executa AFTER...FOR EACH ROW

c) Per acabar, AFTER...FOR EACH STATEMENT

Observació: quan es dispara un trigger, aquest forma part de l'operació d'actualització que l'ha disparat, de manera que si el trigger falla, Oracle dóna per fallida l'actualització completa. Encara que l'errada es produeix a nivell d'una sola fila, Oracle farà un ROLLBACK de tota l'actualització.

script demostratiu: (FOR EACH STATEMENT és el valor per defecte, per això no es posa) 

CREATE OR REPLACE TRIGGER auditar_before_statement
BEFORE UPDATE OF salari
ON emple
BEGIN
insert INTO auditaremple VALUES('Pujada salari - before statement');
END;
/


CREATE OR REPLACE TRIGGER auditar_before_each_row
BEFORE UPDATE OF salari
ON emple
FOR EACH ROW
BEGIN
insert INTO auditaremple VALUES('Pujada salari before each row: ' || :old.emp_no || ' ' || :old.salari);
END;
/


CREATE OR REPLACE TRIGGER auditar_after_each_row
AFTER UPDATE OF salari
ON emple
FOR EACH ROW
BEGIN
insert INTO auditaremple VALUES('Pujada salari after each row: ' || :old.emp_no || ' ' || :new.salari);
END;
/

CREATE OR REPLACE TRIGGER auditar_after_statement
AFTER UPDATE OF salari
ON emple
BEGIN
insert INTO auditaremple VALUES('Pujada del salari - after statement');
END;
/

delete from auditaremple;
commit;
rem desactivo el trigger que teníem creat
alter trigger auditar_pujada_salari disable;

select count(*) from auditaremple;

rem el update afecta a tres files: empleats 7900, 7902 i 7934
update emple set salari=salari+5 where emp_no>=7900;

select count(*) from auditaremple;
select * from auditaremple;

Restriccions per a la creació de triggers

El codi PL/SQL del cos del trigger pot contenir instruccions de consulta i de manipulació de dades, així com crides a altres subprogrames. No obstant, existeixen restriccions: 

   * No pot haver-hi control de transaccions: COMMIT, ROLLBACK, SAVEPOINT
   * no es pot fer crides a procediments que transgredeixin lo anterior
   * no es poden utilitzar comandes DDL
   * no es pot consultar o modificar les taules muntants (aquelles que són afectades per l'INSERT, DELETE o UPDATE que dispara el trigger) (només per a FOR EACH ROW)

Disparadors de substitució: trigger INSTEAD OF

són triggers que no s'executen ni abans ni després, sinó en comptes de (INSTEAD OF) l'ordre que dóna lloc al disparo del trigger. Actuen només sobre vistes, i la idea és poder realitzar accions d'inserció, delete i actualització sobre vistes tal com si fossin taules, i que amb SQL estricte no es podrien fer. El següent exemple és molt explícit.

Només funcionen sobre vistes.

L'exemple més senzill: 

CREATE VIEW EMPLEAT_SALARI AS
SELECT EMP_NO,SALARI FROM EMPLE;

CREATE OR REPLACE TRIGGER no_modificar_salari
INSTEAD OF UPDATE
ON EMPLEAT_SALARI
FOR EACH ROW
BEGIN
INSERT INTO AUDITAR_EMPLE 
      VALUES ('es vol modificar el salari de: ' || :old.emp_no, SYSDATE);
END;
/      

UPDATE EMPLEAT_SALARI SET SALARI=2000 WHERE EMP_NO=7934;
SELECT * FROM AUDITAR_EMPLE;
SELECT * FROM  EMPLEAT_SALARI WHERE EMP_NO=7934;

I l'exemple vist a classe: 

CREATE VIEW EMPLEAT AS
   SELECT emp_no, cognom, ofici, dnom, loc
   FROM EMPLE,DEPART
   WHERE EMPLE.DEPT_NO = DEPART.DEPT_NO;

Ens agradaria poder fer accions directament sobre la vista, però evidentment no podem fer-ho: 

INSERT INTO EMPLEAT VALUES(9000,'HOMS','VENEDOR','PRODUCCIÓ','SABADELL');

ERROR en línea 1:
ORA-01776: no se puede modificar más de una tabla base a través de una vista de unión

Explicació: la idea és que un insert en la vista empleat provoqui un insert en la taula emple, evidentment en el departament núm 40, que és el que es correspon a PRODUCCIÓ - SABADELL. No s'executa perquè la vista involucra dues taules, i necessito el dept_no.

Tampoc funcionaran: 

DELETE FROM EMPLEAT WHERE EMP_NO=7839;;

UPDATE EMPLEAT SET DNOM='COMPTABILITAT' WHERE COGNOM='Sala';

El trigger de substitució tindrà aquesta forma: 

CREATE OR REPLACE TRIGGER t_ges_empleat
INSTEAD OF DELETE OR INSERT OR UPDATE
ON empleat
FOR EACH ROW
DECLARE
   v_dept depart.dept_no%TYPE;
BEGIN
  IF DELETING THEN
     DELETE FROM EMPLE WHERE emp_no = :old.emp_no;
  
  ELSIF INSERTING THEN
     SELECT dept_no INTO v_dept FROM DEPART WHERE depart.dnom = :new.dnom
     AND loc= :new.loc;
     INSERT INTO EMPLE (emp_no, cognom, ofici, dept_no)
     VALUES(:new.emp_no, :new.cognom, :new.ofici, v_dept);
  
  ELSIF UPDATING('dnom') THEN
     SELECT dept_no INTO v_dept FROM DEPART WHERE depart.dnom = :new.dnom;
     UPDATE EMPLE SET dept_no = v_dept WHERE emp_no = :old.emp_no;

  ELSIF UPDATING('ofici') THEN
     UPDATE EMPLE SET ofici = :new.ofici WHERE emp_no = :old.emp_no;
  
  ELSE
     RAISE_APPLICATION_ERROR(-20500,'Error en lactualització');
  END IF;

END;
/

REM joc de proves

insert into empleat values(8000,'Martínez', 'VENEDOR','COMPTABILITAT','GRANOLLERS');
select * from empleat where emp_no=8000;

UPDATE EMPLEAT SET DNOM='PRODUCCIÓ' WHERE COGNOM='Martínez';
SELECT * FROM EMPLEAT WHERE COGNOM='Martínez';

delete from empleat where emp_no=8000;
select * from empleat where emp_no=8000;

Vistes amb informació sobre els triggers

Per obtenir informació dels triggers disposem de la vista DBA_TRIGGERS, que com els altres objectes del diccionari de la base de dades, el propietari és SYS, però tenim permisos de SELECT. 

SQL > DESCRIBE DBA_TRIGGERS
OWNER
TRIGGER_NAME
TRIGGER_TYPE [after/before]
TRIGGER_EVENT [insert/update/delete]
TABLE_OWNER
TABLE_NAME
REFERENCING NAMES
WHEN_CLAUSE
STATUS [enable/disable]
DESCRIPTION
TRIGGER_BODY -> LONG

Podem saber quins són els triggers que tenim en execució amb la sentència: 

select trigger_name,status from dba_triggers where owner='DAI2A12';

Per habilitar i deshabilitar triggers: 

ALTER TRIGGER auditar_salari [ENABLE/DISABLE/COMPILE]

creat per Joan Quintana Compte, octubre 2009

Obtingut de «http://wiki.joanillo.org/index.php?title=Teoria_triggers&oldid=248150»