Salta el contingut

DAM - DAW - MP 0484 Bases de Dades - NF5

Triggers

Un disparador, TRIGGER a partir d'ara, és un objecte de la base de dades associat a una taula que s'activa quan es produeix un determinat esdeveniment a la taula.

Se solen utilitzar per a fer comprovacions dels valors que s'han d'inserir a la taula o bé per a realitzar càlculs dels valors implicats en una actualització.

Un trigger s'activarà quan una instrucció afegeix, modifica o elimina files de la taula associada.

L'execució del trigger es pot configurar per activar-lo abans o després de l'esdeveniment

Imagineu que voleu emmagatzemar, en una taula de registre, els canvis produïts en una altra taula determinada.

Cal que el programador que actualitzi la taula, ompli la taula de registre amb un INSERT o cridant un procediment emmagatzemat, cada vegada?

No! Provocarem que el SGBDR, cada vegada que es faci un UPDATE a la taula, executi, just després, l'INSERT a la taula de registre.

Això ho programarem en un TRIGGER AFTER UPDATE, que es cridarà sol, cada vegada que fem una operació UPDATE a la taula.

L'exemple que acabem d'explicar, el resoldrem amb un TRIGGER AFTER UPDATE, de la següent forma:

Exemple de Trigger
DELIMITER //
CREATE OR REPLACE TRIGGER T_DEPARTAMENTS_AU
 AFTER UPDATE ON departaments FOR EACH ROW
BEGIN
 DECLARE canvi VARCHAR(2000);
 SET canvi = CONCAT_WS("#",
     CONCAT_WS(':',OLD.DEPARTMENT_ID, OLD.DEPARTMENT_NAME,
                   IFNULL(OLD.MANAGER_ID, 'NULL'),
                   IFNULL(OLD.LOCATION_ID, 'NULL')),
     CONCAT_WS(':',NEW.DEPARTMENT_ID, NEW.DEPARTMENT_NAME,
                   IFNULL(NEW.MANAGER_ID, 'NULL'),
                   IFNULL(NEW.LOCATION_ID, 'NULL')));    
 INSERT INTO REGISTRE(taula, canvi, moment)
        VALUES ('departaments', canvi, DEFAULT);
END//
DELIMITER ;

Si no entens la comanda CREATE TRIGGER ja la veurem més endavant.

Per a provar-ho podem executar la següent comanda.

CREATE OR REPLACE TABLE REGISTRE(
  taula VARCHAR(64) NOT NULL,
  canvi VARCHAR(2000) NOT NULL,
  moment DATETIME DEFAULT CURRENT_TIMESTAMP());
START TRANSACTION; 
SELECT * FROM DEPARTAMENTS
         WHERE DEPARTMENT_NAME IN ('IT', 'TI'); 
UPDATE DEPARTAMENTS
       SET department_name = 'TI', MANAGER_ID = NULL 
       WHERE DEPARTMENT_NAME = 'IT'; 
SELECT * FROM DEPARTAMENTS
         WHERE DEPARTMENT_NAME IN ('IT', 'TI'); 
SELECT * FROM REGISTRE; 
ROLLBACK;

I podem observar com s'ha modificat la taula DEPARTAMENTS i a la vegada s'ha inserit un registre a la taula REGISTRE.

SELECT * FROM DEPARTAMENTS WHERE DEPARTMENT_NAME IN ('IT', 'TI');
+---------------+-----------------+------------+-------------+
|            60 | IT              |        103 |        1400 |
+---------------+-----------------+------------+-------------+
UPDATE DEPARTAMENTS SET department_name = 'TI', MANAGER_ID = NULL
       WHERE DEPARTMENT_NAME = 'IT';
Query OK, 1 row affected (0.001 sec)
SELECT * FROM DEPARTAMENTS WHERE DEPARTMENT_NAME IN ('IT', 'TI');
+---------------+-----------------+------------+-------------+
|            60 | TI              |       NULL |        1400 |
+---------------+-----------------+------------+-------------+
SELECT * FROM REGISTRE;
+--------------+--------------------------------+---------------------+
| taula        | canvi                          | moment              |
+--------------+--------------------------------+---------------------+
| departaments | 60:IT:103:1400#60:TI:NULL:1400 | AAAA-MM-DD 11:49:00 |
+--------------+--------------------------------+---------------------+

Sintaxis del CREATE TRIGGER

La Sintaxis del CREATE TRIGGER és:

CREATE [OR REPLACE]
    [DEFINER = { user | CURRENT_USER | role | CURRENT_ROLE }]
    TRIGGER [IF NOT EXISTS] trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
   [{ FOLLOWS | PRECEDES } other_trigger_name ]
   trigger_stmt;

Per a poder crear triggers cal tenir el privilegi TRIGGER sobre la taula en qüestió.

  • trigger_time és el moment d'acció del disparador. Pot ser BEFORE o AFTER per indicar que el disparador s'activa abans o després de tractar cada fila.

  • trigger_event indica l'acció, sobre la taula, que activa el trigger. Pot ser:

    • INSERT: El trigger s'activa sempre que s'insereix una nova fila a la taula

    • UPDATE: El disparador s'activa sempre que es modifica una fila de la taula

    • DELETE: El disparador s'activa sempre que s'elimina una fila de la taula

Per a què utilitzem triggers?

Podeu utilitzar activadors per ... :

  • Millorar les regles de seguretat de bases de dades complexes

  • Crear registres d'auditoria automàticament

  • Aplicar regles complexes d'integritat de dades

  • Crear registres de log automàticament

  • Evitar que s'eliminin taules accidentalment

  • Evitar que es produeixin transaccions DML no vàlides o no permeses

  • Generar valors de columna derivats automàticament

  • Mantenir la replicació de taules síncrones

  • Modificar les dades de la taula quan s'emeten declaracions DML contra vistes

Exemples: Integritat complexe

Imaginem una regla que indica que un empleat no pot realitzar una feina que ja havia tingut abans. El trobarem a la taula HISTORIAL que crearem amb la següent comanda.

Creem i omplim la taula HISTORIAL
CREATE TABLE HISTORIAL LIKE empresa.JOB_HISTORY;
INSERT INTO HISTORIAL SELECT * FROM empresa.JOB_HISTORY;

Just abans de modificar el JOB_ID d'un empleat, comprovarem que el nou no el tinguem dins la taula HISTORIAL. Si hi és provocarem un ERROR.

Exemple de trigger que comprova una regla complexe
DELIMITER //
CREATE OR REPLACE TRIGGER T_EMPLOYEES_BU
  BEFORE UPDATE ON empleats FOR EACH ROW 
BEGIN 
 DECLARE vQuantesFeines INT;
 IF OLD.JOB_ID <> NEW.JOB_ID THEN
  SELECT COUNT(*) INTO vQuantesFeines
   FROM historial
   WHERE employee_id = OLD.employee_id AND
         job_id = NEW.job_id;
  IF vQuantesFeines > 0 THEN
   SIGNAL SQLSTATE 'HY000' SET
MESSAGE_TEXT = 'Aquest empleat ja ha fet aquesta Feina abans';
  END IF; 
 END IF; 
END;
//
DELIMITER ;
Prova del trigger anterior
START TRANSACTION;
SELECT * FROM HISTORIAL;
+-------------+------------+------------+------------+---------------+
| EMPLOYEE_ID | START_DATE | END_DATE   | JOB_ID     | DEPARTMENT_ID |
+-------------+------------+------------+------------+---------------+
|         101 | 1989-09-21 | 1993-10-27 | AC_ACCOUNT |           110 |
|         101 | 1993-10-28 | 1997-03-15 | AC_MGR     |           110 |
UPDATE EMPLEATS SET JOB_ID = 'AC_MGR' WHERE EMPLOYEE_ID = 101;
ERROR 1644 (HY000): Aquest empleat ja ha fet aquesta Feina abans
ROLLBACK;

Al generar un ERROR ja no es realitza l'UPDATE que haviem intentat fer.

En el nostre cas amb un índex únic, com tenim, no cal el trigger. La norma diu: NO FACIS AMB TRIGGERS EL QUE PUGUIS FER D'ALTRA FORMA, però ens serveix d'exemple.

Temporització i esdeveniments

El moment en què s'executa el nostre TRIGGER pot ser justa abans, BEFORE, o just després, AFTER, de l'esdeveniment que tractem.

En Maria DB, els esdeveniments que poden llançar un TRIGGER són, només, les instruccions DML INSERT, UPDATE i DELETE.

Altres SBGD poden treballar sobre altres esdeveniments, com ara LOGIN, etc.

El moment en què s'executa el nostre TRIGGER pot ser justa abans (BEFORE) o just després (AFTER) de l'esdeveniment que tractem. En Maria DB, els esdeveniments que poden llançar un TRIGGER són, només, les instruccions DML INSERT, UPDATE i DELETE. MariaDB executa, sempre, el trigger per cadascuna de les files afectades. Si un UPDATE afecta 3 files, el trigger d'executarà 3 vegades, una per cada fila.

MariaDB executa, sempre, el trigger per cadascuna de les files afectades. Si un UPDATE afecta 3 files, el trigger d'executarà 3 vegades, una per cada fila.

Podem progamar més d'un esdeveniment en una sola taula pel mateix moment i esdeveniment. Per exemple, dos triggers AFTER UPDATE ON FEINES, i determinar l'ordre d'execució, amb el paràmetre { FOLLOWS | PRECEDES }.

Quan un trigger finalitza amb un error, SIGNAL, llavors l'operació que s'estava executant no es realitza.

Exemples: Integritat de camp

Exemple de trigger que comprova la integritat d'un camp
DELIMITER //
CREATE OR REPLACE TRIGGER T_EMPLOYEES_BI
 BEFORE INSERT ON empleats FOR EACH ROW 
BEGIN 
 IF DAYOFWEEK(NEW.HIRE_DATE) IN (1,7) THEN
  SIGNAL SQLSTATE 'HY000'
  SET MESSAGE_TEXT = "No es pot donar d\'alta un empleat en Cap de setmana";
 END IF;
END;
//
DELIMITER ;
INSERT INTO EMPLEATS(HIRE_DATE)
   VALUES(CURRENT_DATE() - 
          INTERVAL (DAYOFWEEK(CURRENT_DATE()) - 1) DAY);
-- ERROR 1644 (HY000): No es pot donar d'alta un empleat en Cap de setmana

Fixeu-vos com podem evitar donar d'alta un empleat en cap de setmana.

El trigger s'executa abans de fer les comprovacions de camps NULL, etc. És a dir s'executa abans de provar de fer l'INSERT.

NEW i OLD

Si heu mirat amb detall el codi dels triggers fets fins ara, haureu vist que alguns dels camps portaven, al seu davant, NEW. o OLD..

Dins el trigger tenim opció d'accedir a les dades del registre tractat a través del prefix NEW o OLD.

  • NEW és el valor nou que assignem des de la consulta. Podem modificar-lo si ens convé.

  • OLD és el valor que té el camp abans de la sentència.

El prefix NEW no pot ser actualitzat en els esdeveniments AFTER ja que, una vegada hem fet l'acció, no podem modificar-lo.

No podem executar SET NEW.camp = valor; en un trigger AFTER.

Per accedir al valor ho fem de la següent forma:

OLD.nomDelCamp
NEW.nomDelCamp

Cal saber, però, quan tenim accés a les variables NEW i OLD, segons el tipus d'instrucció:

  • INSERT: només existeixen les variables NEW

  • UPDATE: existeixen les variables NEW i OLD

  • DELETE: només existeixen les variables OLD

Si en un trigger BEFORE modifiquem la variable NEW.camp, l'INSERT o UPDATE es farà amb aquest valor.

Exemple de NEW

Així doncs, podem no permetre modificar algun camp en el UPDATE o inicialitzar valors en un INSERT o ...

DELIMITER //
CREATE OR REPLACE TRIGGER T_DEPARTAMENTS_BI
  BEFORE INSERT ON departaments FOR EACH ROW 
BEGIN 
  DECLARE vDepartment_id TYPE OF departaments.department_id;
  SELECT IFNULL(MAX(DEPARTMENT_ID), 0) + 1
     INTO vDepartment_id
  FROM departaments;
  SET NEW.department_id = vDepartment_id;
END; //
DELIMITER ;

Així doncs, al següent exemple:

  • al primer INSERT no es farà cas del DEPARTMENT_ID passat

  • al segon INSERT ja no li passem el valor.

Els dos INSERTs funcionen correctament ja que el trigger omple el camp EMPLOYEE_ID.

START TRANSACTION;
INSERT INTO DEPARTAMENTS(department_id, department_name)
       VALUES(1000, 'Nou Departament');
INSERT INTO DEPARTAMENTS(department_name)
       VALUES('Departament Més Nou');
SELECT * FROM DEPARTAMENTS;
ROLLBACK;

Exemple NEW i OLD

En el següent exemple, si volem posar un SOU menor al que té un usuari, deixarem l'antic sense provocar cap error.

DELIMITER //
CREATE OR REPLACE TRIGGER T_EMPLEATS_BU
  BEFORE UPDATE ON empleats FOR EACH ROW 
BEGIN 
  IF OLD.salary IS NOT NULL THEN
    IF NEW.salary IS NULL OR NEW.salary < OLD.salary THEN
      SET NEW.SALARY = OLD.SALARY;
    END IF;
  END IF;
END;
DELIMITER ;

Així doncs, al següent exemple:

  • al primer UPDATE no se'n realitzarà cap

  • al segon UPDATE només se'n realizaran uns quants

L'usuari no rep informació al respecte.

START TRANSACTION;
SELECT EMPLOYEE_ID, SALARY FROM EMPLEATS
  WHERE EMPLOYEE_ID < 150 ORDER BY 2;
UPDATE EMPLEATS SET SALARY = 0 WHERE EMPLOYEE_ID < 150;
SELECT EMPLOYEE_ID, SALARY FROM EMPLEATS
  WHERE EMPLOYEE_ID < 150 ORDER BY 2;
UPDATE EMPLEATS SET SALARY = 10000 WHERE EMPLOYEE_ID < 150;
SELECT EMPLOYEE_ID, SALARY FROM EMPLEATS
  WHERE EMPLOYEE_ID < 150 ORDER BY 2;
ROLLBACK;

Eliminació de triggers

Per eliminar un TRIGGER utilitzarem la comanda DROP TRIGGER.

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;

Per eliminar un trigger cal el permís TRIGGER.

Els triggers també s'eliminen automàticament en eliminar la taula associada.

No existeix cap comanda per modificar un trigger, per tant, si volem modificar un trigger caldrà usar la comanda CREATE OR REPLACE TRIGGER

Diccionari de dades

Per a consultar els triggers al diccionari de dades cal referir-nos a la taula TRIGGERS.

information_schema.TRIGGERS

  • Utilitza la comanda SHOW CREATE TRIGGER... per veure'n la definició.

  • Utilitza la comanda SHOW TRIGGERS per veure les característiques dels triggers.

  • Des de la shell, acaba aquestes instruccions amb \G en comptes de ;