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` DATETIME NOT NULL,
  `accio` VARCHAR(100) NOT NULL,
  `taula` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`));

Taula alumnes

Trigger DELETE

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

Trigger UPDATE

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

Trigger INSERT

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

Taula assignatures

Trigger DELETE

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

Trigger UPDATE

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

Trigger INSERT

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

Taula notes

Trigger DELETE

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

Trigger UPDATE

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

Trigger INSERT

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

Execució

MariaDB [world]> call paisos_llengua("English");

+--------------------------------------+
| Name                                 |
+--------------------------------------+
| Anguilla                             |
| American Samoa                       |
| Antigua and Barbuda                  |
| Australia                            |
| Belize                               |
| Bermuda                              |
| Barbados                             |
| Canada                               |
| Cocos (Keeling) Islands              |
| Christmas Island                     |
| Cayman Islands                       |
| Falkland Islands                     |
| United Kingdom                       |
| Gibraltar                            |
| Guam                                 |
| Hong Kong                            |
| Ireland                              |
| Saint Kitts and Nevis                |
| Saint Lucia                          |
| Lesotho                              |
| Marshall Islands                     |
| Malta                                |
| Northern Mariana Islands             |
| Montserrat                           |
| Norfolk Island                       |
| Niue                                 |
| Nauru                                |
| New Zealand                          |
| Palau                                |
| Saint Helena                         |
| Seychelles                           |
| Turks and Caicos Islands             |
| Tokelau                              |
| Tonga                                |
| Tuvalu                               |
| United States Minor Outlying Islands |
| United States                        |
| Saint Vincent and the Grenadines     |
| Virgin Islands, British              |
| Virgin Islands, U.S.                 |
| Vanuatu                              |
| Samoa                                |
| South Africa                         |
| Zimbabwe                             |
+--------------------------------------+
44 rows in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)