ASIX/M10/UF1/A07/E0

ASIX M10 UF1

Pt7. Pràctica de disparadors

Guillem Solà i Boeck (host: PC05)

Enunciat

En aquesta pràctica s'ha de crear uns disparadors per que quedi reflectit l'usuari que ha fet qualssevol operació sobre totes les taules de la base de dades (NotesInstitut). És a dir s'ha d'emmagatzemar l'usuari que fa l'operació, la data i hora en la que la fa, l'operació que és fa INSERT, DELETE,UPDATE), sobre quina taula i què s'ha fet..

Això serà una mena de logs.

El primer que s'ha de fer es afegir una nova taula, per emmagatzemar tota aquesta informació.

Solució

Creació de la taula que emmagatzemarà la informació

CREATE TABLE `NotesInstitut`.`logs` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `usuari` VARCHAR(45) NOT NULL,
  `data` TIMESTAMP NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `accio` VARCHAR(100) NOT NULL,
  `taula` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`));

Nota: El camp data el poso com a tipus TIMESTAMP amb un valor per defecte que retorna la data i hora actual. D'aquesta manera, quan afegeixo un registre nou a la taula de logs sense passar valor per al camp data, s'hi afegirà automàticament el moment que s'ha registrat l'esdeveniment.

---

Taula alumnes

Trigger DELETE

CREATE TRIGGER delete_alumnes AFTER DELETE
	ON alumnes FOR EACH ROW INSERT INTO logs (usuari,accio,taula) VALUES (user(),CONCAT("DELETE ", old.idAlumne),"alumnes")

Trigger UPDATE

CREATE TRIGGER update_alumnes AFTER UPDATE
	ON alumnes FOR EACH ROW INSERT INTO logs (usuari,accio,taula) VALUES (user(),CONCAT("UPDATE ", old.idAlumne, " --> ", new.idAlumne),"alumnes")

Trigger INSERT

CREATE TRIGGER insert_alumnes AFTER INSERT
	ON alumnes FOR EACH ROW INSERT INTO logs (usuari,accio,taula) VALUES (user(),CONCAT("INSERT ", new.idAlumne),"alumnes")

Taula assignatures

Trigger DELETE

CREATE TRIGGER delete_assignatures AFTER DELETE
	ON assignatures FOR EACH ROW INSERT INTO logs (usuari,accio,taula) VALUES (user(),CONCAT("DELETE ", old.idAssignatura),"assignatures")

Trigger UPDATE

CREATE TRIGGER update_assignatures AFTER UPDATE
	ON assignatures FOR EACH ROW INSERT INTO logs (usuari,accio,taula) VALUES (user(),CONCAT("UPDATE ", old.idAssignatura, " --> ", new.idAssignatura),"assignatures")

Trigger INSERT

CREATE TRIGGER insert_assignatures AFTER INSERT
	ON assignatures FOR EACH ROW INSERT INTO logs (usuari,accio,taula) VALUES (user(),CONCAT("INSERT ", new.idAssignatura),"assignatures")

Taula notes

Trigger DELETE

CREATE TRIGGER delete_notes AFTER DELETE
	ON notes FOR EACH ROW INSERT INTO logs (usuari,accio,taula) VALUES (user(),CONCAT("DELETE ", old.idAssignatura, " - ", old.idAlumne, " - ", old.nota),"notes")

Trigger UPDATE

CREATE TRIGGER update_notes AFTER UPDATE
	ON notes FOR EACH ROW INSERT INTO logs (usuari,accio,taula) VALUES (user(),CONCAT("UPDATE (", old.idAssignatura, " - ", old.idAlumne, " - ", old.nota,") --> (",new.idAssignatura, " - ", new.idAlumne, " - ", new.nota,")"),"notes")

Trigger INSERT

CREATE TRIGGER insert_notes AFTER INSERT
	ON notes FOR EACH ROW INSERT INTO logs (usuari,accio,taula) VALUES (user(),CONCAT("INSERT (", new.idAssignatura, " - ", new.idAlumne, " - ", new.nota,")"),"notes")

Execució

1. INSERT alumne nou

MariaDB [NotesInstitut]> insert into alumnes (idAlumne,nom) VALUES (12345678,"Guillem2");
Query OK, 1 row affected (0.04 sec)

2. DELETE alumne nou

MariaDB [NotesInstitut]> delete from alumnes where nom="Guillem2";
Query OK, 1 row affected (0.04 sec)

3. INSERT assignatura nova

MariaDB [NotesInstitut]> insert into assignatures VALUES ('M1', 'Sistemes Operatius');
Query OK, 1 row affected (0.03 sec)

4/5. UPDATE nota existent

MariaDB [NotesInstitut]> update notes set nota = 9.9 where idAssignatura = 'M10';
Query OK, 2 rows affected (0.12 sec)
Rows matched: 2  Changed: 2  Warnings: 0

6/7. DELETE notes d'un alumne

MariaDB [NotesInstitut]> delete from alumnes where idAlumne="47852734S";
Query OK, 1 row affected (0.06 sec)

8. DELETE alumne

MariaDB [NotesInstitut]> delete from alumnes where idAlumne="47852734S";
Query OK, 1 row affected (0.06 sec)

Contingut de la taula logs al final de l'execució

MariaDB [NotesInstitut]> select * from logs;
+----+----------------+---------------------+---------------------------------------------------------------+--------------+
| id | usuari         | data                | accio                                                         | taula        |
+----+----------------+---------------------+---------------------------------------------------------------+--------------+
|  1 | root@localhost | 2018-02-23 16:32:55 | INSERT 12345678                                               | alumnes      |
|  2 | root@localhost | 2018-02-23 16:34:31 | DELETE 12345678                                               | alumnes      |
|  3 | root@localhost | 2018-02-23 16:46:40 | INSERT M1                                                     | assignatures |
|  4 | root@localhost | 2018-02-23 16:51:14 | UPDATE (M10 - 47821459C - 3.50) --> (M10 - 47821459C - 9.90)  | notes        |
|  5 | root@localhost | 2018-02-23 16:51:14 | UPDATE (M10 - 47852734S - 10.00) --> (M10 - 47852734S - 9.90) | notes        |
|  6 | root@localhost | 2018-02-23 16:54:47 | DELETE M10 - 47852734S - 9.90                                 | notes        |
|  7 | root@localhost | 2018-02-23 16:54:47 | DELETE M2 - 47852734S - 6.50                                  | notes        |
|  8 | root@localhost | 2018-02-23 16:55:12 | DELETE 47852734S                                              | alumnes      |
+----+----------------+---------------------+---------------------------------------------------------------+--------------+
8 rows in set (0.00 sec)