Crearea declansatorilor
Un declansator reprezinta un obiect de la nivelul unei baze de date care este asociat unui tabel si care se activeaza la aparitia unui eveniment pentru tabelul respectiv. Declansatorii nu pot fi asociati tabelelor temporare sau vizualizarilor.
Declansatorii sunt structuri asemanatoare procedurilor stocate. In mod similar acestora, declansatorii contin secvente de cod SQL care se executa ca un intreg. Diferenta principala dintre declansatori si proceduri stocate este data de faptul ca decansatorii sunt executati implicit la aparitia unui eveniment, in timp ce procedurile stocate sunt executate explicit la nivelul unui apel CALL.
Crearea unui declansator se realizeaza cu ajutorul instructiunii CREATE TRIGGER, care prezinta urmatoarea sintaxa in MySQL:
CREATE [DEFINER = utilizator] TRIGGER nume_declansator moment_declansare eveniment_declansator ON nume_tabel FOR EACH ROW corp_declansator moment_declansare: { BEFORE | AFTER } eveniment_declansator: { INSERT | UPDATE | DELETE }
Clauza DEFINER specifica contul MySQL care urmeaza a fi utilizat pentru a verifica privilegiile la momentul activarii declansatorului.
Daca pentru clauza DEFINER este specificata o valoare, aceasta trebuie sa corespunda unui utilizator de pe serverul MySQL (user_name@host_name). Valoarea implicita pentru clauza DEFINER este aceeasi cu numele utilizatorului care executa instructiunea CREATE TRIGGER.
Momentul declansarii se implementeaza prin BEFORE sau AFTER si indica faptul ca declansatorul se activeaza inainte sau dupa fiecare inregistrare de la nivelul tabelului asociat, pentru a o modifica.
Evenimentul declansator indica un tip de operatie care permite activarea declansatorului. Trei valori sunt permise in acest caz:
• INSERT: declansatorul se activeaza la introducerea de noi inregistrari la nivelul tabelului asociat; o astfel de operatie apare la rularea instructiunilor INSERT, LOAD DATA si REPLACE;
• UPDATE: declansatorul se activeaza la modificarea valorilor inregistrarilor, situatie ce corespunde rularii de instructiuni UPDATE;
• DELETE: declansatorul se activeaza la stergerea inregistrarilor, adica in cazul rularii de instructiuni DELETE si REPLACE.
Corpul declansatorului este alcatuit din instructiunea care se executa la activarea declansatorului. Pentru a putea executa mai multe instructiuni, este necesara delimitarea lor prin intermediul constructiilor de tip BEGIN … END.
USE sakila; | |
DROP TRIGGER IF EXISTS ins_film; | |
DELIMITER $$ | |
CREATE TRIGGER ins_film | |
AFTER INSERT | |
ON film FOR EACH ROW | |
BEGIN | |
INSERT INTO film_text (film_id, title, description) | |
VALUES (new.film_id, new.title, new.description); | |
END $$ | |
DELIMITER ; |
La nivelul corpului declansatorului pot fi accesate valori ale coloanelor afectate de instructiunile de manipulare a datelor rulate la nivelul tabelului asociat. Din acest punct de vedere, se face diferenta intre valorile acestor coloane inainte (BEFORE) si dupa (AFTER) aparitia evenimentului declansator. Acest lucru este realizat prin intermediul unor modificatori, OLD, pentru valorile coloanelor inainte de realizarea evenimentului, si NEW, pentru valorile coloanelor dupa aparitia evenimentului.
Eveniment declansator | NEW | OLD |
---|---|---|
INSERT | Y | N |
UPDATE | Y | Y |
DETELE | N | Y |
Vizualizarea declansatorilor disponibili la nivelul unei baze de date poate fi realizata prin intermediul unor comenzi de tipul SHOW TRIGGERS. Si in acest caz, comanda SHOW ofera posibilitatea filtrarii inregistrarilor din rezultat pe baza operatorului LIKE sau a clauzei WHERE.
SHOW TRIGGERS [{FROM | IN} nume_baza_date] [LIKE 'model' | WHERE expresie]
mysql> SHOW TRIGGERS -> FROM sakila -> WHERE `Trigger`='ins_film'\G *************************** 1. row *************************** Trigger: ins_film Event: INSERT Table: film Statement: BEGIN INSERT INTO film_text (film_id, title, description) VALUES (new.film_id, new.title, new.description); END Timing: AFTER Created: 2020-04-05 09:44:17.73 Definer: airman@uncoded.ro character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8mb4_0900_ai_ci 1 row in set
Tipuri de declansatori
Pe baza combinatiilor posibile dintre valorile momentului declansarii si cele ale evenimentului declansator exista posibilitatea definirii urmatoarelor tipuri de declansatori:
• BEFORE INSERT: activat inainte de introducerea de inregistrari la nivelul unui tabel;
• AFTER INSERT: activat dupa introducerea de inregistrari la nivelul unui tabel;
• BEFORE UPDATE: activat inainte de modificarea valorilor inregistrarilor de la nivelul unui tabel;
• AFTER UPDATE: activat dupa modificarea valorilor inregistrarilor de la nivelul unui tabel;
• BEFORE DELETE: activat inainte de stergerea de inregistrari de la nivelul unui tabel;
• AFTER DELETE: activat dupa stergerea de inregistrari de la nivelul unui tabel;
Avem in vedere implementarea unui tabel de tip log, actor_log, la nivelul bazei de date sakila. Acesta va pastra o copie a fiecarei inregistrari de la nivelul tabelului actor care este introdusa, modificata sau stearsa.
mysql> DESCRIBE sakila.actor; +-------------+-------------------+------+-----+-------------------+-----------------------------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------------+------+-----+-------------------+-----------------------------------------------+ | actor_id | smallint unsigned | NO | PRI | NULL | auto_increment | | first_name | varchar(45) | NO | | NULL | | | last_name | varchar(45) | NO | MUL | NULL | | | last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | +-------------+-------------------+------+-----+-------------------+-----------------------------------------------+ 4 rows in set
La nivelul tabelului actor_log vom pastra valorile corespunzatoare coloanelor actor_id, first_name si last_name pentru inregistrarile din tabelul actor care au trecut prin operatii de tip INSERT, UPDATE si DELETE. In plus, pentru fiecare inregistrare vom specifica tipul evenimentului declansator, la nivelul coloanei event, respectiv momentul de timp ce corespunde introducerii tuturor datelor in tabelul actor_log (last_update).
mysql> DESCRIBE sakila.actor_log; +-------------+----------------------------------+------+-----+-------------------+-----------------------------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------------------------------+------+-----+-------------------+-----------------------------------------------+ | id | int unsigned | NO | PRI | NULL | auto_increment | | event | enum('insert','update','delete') | NO | | NULL | | | actor_id | smallint unsigned | NO | | NULL | | | first_name | varchar(45) | NO | | NULL | | | last_name | varchar(45) | NO | | NULL | | | last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | +-------------+----------------------------------+------+-----+-------------------+-----------------------------------------------+ 6 rows in set
Definim un prim declansator care are in vedere rularea unei operatii de tip INSERT la nivelul tabelului actor_log dupa inserarea unui nou actor.
USE sakila; | |
DROP TRIGGER IF EXISTS insert_after_actor; | |
DELIMITER $$ | |
CREATE TRIGGER insert_after_actor | |
AFTER INSERT | |
ON actor FOR EACH ROW | |
BEGIN | |
INSERT INTO actor_log (event, actor_id, first_name, last_name) | |
VALUES ('insert', new.actor_id, new.first_name, new.last_name); | |
END $$ | |
DELIMITER ; |
Daca rulam o operatie de tip INSERT la nivelul tabelului actor, operatie care are in vedere inregistrarea unui nou actor (ED DAVIS), dupa executia operatiei se activeaza declansatorul ins_after_actor care determina introducerea unei noi inregistrari la nivelul tabelului actor_log corespunzatoare aceluiasi actor ED DAVIS. Pa baza modificatorilor de tip NEW, de la nivelul tabelului actor sunt preluate valorile coloanelor actor_id, first_name si last_name.
mysql> INSERT INTO actor(first_name, last_name) -> VALUES('ED', 'DAVIS'); Query OK, 1 row affected
Putem verifica ultimele inregistrari introduse atat in tabelul actor, cat si in tabelul actor_log.
mysql> SELECT * -> FROM actor -> ORDER BY last_update DESC -> LIMIT 5; +----------+------------+-----------+---------------------+ | actor_id | first_name | last_name | last_update | +----------+------------+-----------+---------------------+ | 201 | ED | DAVIS | 2020-04-05 21:16:54 | | 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 | | 2 | NICK | WAHLBERG | 2006-02-15 04:34:33 | | 3 | ED | CHASE | 2006-02-15 04:34:33 | | 4 | JENNIFER | DAVIS | 2006-02-15 04:34:33 | +----------+------------+-----------+---------------------+ 5 rows in set
mysql> SELECT * -> FROM actor_log -> ORDER BY last_update DESC -> LIMIT 5; +----+--------+----------+------------+-----------+---------------------+ | id | event | actor_id | first_name | last_name | last_update | +----+--------+----------+------------+-----------+---------------------+ | 1 | insert | 201 | ED | DAVIS | 2020-04-05 21:16:54 | +----+--------+----------+------------+-----------+---------------------+ 1 row in set
In mod similar, putem defini la nivelul tabelului actor declansatorii update_after_actor si delete_before_actor.
USE sakila; | |
DROP TRIGGER IF EXISTS update_after_actor; | |
DELIMITER $$ | |
CREATE TRIGGER update_after_actor | |
AFTER UPDATE | |
ON actor FOR EACH ROW | |
BEGIN | |
INSERT INTO actor_log (event, actor_id, first_name, last_name) | |
VALUES ('update', new.actor_id, new.first_name, new.last_name); | |
END $$ | |
DELIMITER ; |
USE sakila; | |
DROP TRIGGER IF EXISTS delete_before_actor; | |
DELIMITER $$ | |
CREATE TRIGGER delete_before_actor | |
BEFORE DELETE | |
ON actor FOR EACH ROW | |
BEGIN | |
INSERT INTO actor_log (event, actor_id, first_name, last_name) | |
VALUES ('delete', old.actor_id, old.first_name, old.last_name); | |
END $$ | |
DELIMITER ; |
mysql> SHOW TRIGGERS -> FROM sakila -> LIKE '%actor'\G *************************** 1. row *************************** Trigger: insert_after_actor Event: INSERT Table: actor Statement: BEGIN INSERT INTO actor_log (event, actor_id, first_name, last_name) VALUES ('insert', new.actor_id, new.first_name, new.last_name); END Timing: AFTER Created: 2020-04-05 21:33:43.26 Definer: airman@uncoded.ro character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8mb4_0900_ai_ci *************************** 2. row *************************** Trigger: update_after_actor Event: UPDATE Table: actor Statement: BEGIN INSERT INTO actor_log (event, actor_id, first_name, last_name) VALUES ('update', new.actor_id, new.first_name, new.last_name); END Timing: AFTER Created: 2020-04-05 21:44:52.29 Definer: airman@uncoded.ro character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8mb4_0900_ai_ci *************************** 3. row *************************** Trigger: delete_before_actor Event: DELETE Table: actor Statement: BEGIN INSERT INTO actor_log (event, actor_id, first_name, last_name) VALUES ('delete', old.actor_id, old.first_name, old.last_name); END Timing: BEFORE Created: 2020-04-05 21:44:59.35 Definer: airman@uncoded.ro character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8mb4_0900_ai_ci 3 rows in set
Rularea unor instructiuni de tip UPDATE si DELETE la nivelul tabelului actor, va determina in acest moment activarea ultimilor doi declansatori.
mysql> UPDATE actor -> SET first_name = 'EDD' -> WHERE actor_id = 3; Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0
mysql> DELETE FROM actor -> WHERE actor_id = 201; Query OK, 1 row affected
mysql> SELECT * -> FROM actor -> ORDER BY last_update DESC -> LIMIT 5; +----------+------------+--------------+---------------------+ | actor_id | first_name | last_name | last_update | +----------+------------+--------------+---------------------+ | 3 | EDD | CHASE | 2020-04-05 21:53:21 | | 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 | | 2 | NICK | WAHLBERG | 2006-02-15 04:34:33 | | 4 | JENNIFER | DAVIS | 2006-02-15 04:34:33 | | 5 | JOHNNY | LOLLOBRIGIDA | 2006-02-15 04:34:33 | +----------+------------+--------------+---------------------+ 5 rows in set
mysql> SELECT * -> FROM actor_log -> ORDER BY last_update DESC -> LIMIT 5; +----+--------+----------+------------+-----------+---------------------+ | id | event | actor_id | first_name | last_name | last_update | +----+--------+----------+------------+-----------+---------------------+ | 4 | delete | 201 | ED | DAVIS | 2020-04-05 21:59:02 | | 2 | update | 3 | EDD | CHASE | 2020-04-05 21:53:21 | | 1 | insert | 201 | ED | DAVIS | 2020-04-05 21:16:54 | +----+--------+----------+------------+-----------+---------------------+ 3 rows in set
Stergerea declansatorilor
Sintaxa utilizata pentru stergerea unui declansator este urmatoarea:
DROP TRIGGER [IF EXISTS] [nume_baza_date.]nume_declansator
Pentru a utiliza aceasta instructiune, utilizatorul trebuie sa detina privilegiul TRIGGER pentru tabelul asociat declansatorului care urmeaza a fi sters.
Clauza IF EXISTS inhiba afisarea mesajului de eroare care apare la incercarea de stergere a unui declansator care nu exista. Daca aceasta clauza este prezenta, pentru orice procedura stocata care nu exista este generata o averizare (WARNING).
mysql> DROP TRIGGER IF EXISTS sakila.ins_film; Query OK, 0 rows affected, 1 warning
mysql> SHOW WARNINGS; +-------+------+------------------------+ | Level | Code | Message | +-------+------+------------------------+ | Note | 1360 | Trigger does not exist | +-------+------+------------------------+ 1 row in set
Declansatorii asociati unui tabel sunt stersi si in cazul in care are loc stergerea tabelului.