Aulas práticas - Ficha 5

Bases de Dados (CC2005), Dep. Ciência de Computadores, FCUP

Eduardo R. B. Marques, DCC/FCUP

Objectivos: Exercícios sobre "triggers" em complemento aos exercícios de Mooshak da semana de 27/Abril/2020.

Referências:

1

Defina os seguintes "triggers":

Dicas:

Exemplos de teste: 

mysql> INSERT INTO STREAM(MovieId, CustomerId, StreamDate, Charge) VALUES(1, 1, '2018-01-01 19:30:00', -1);
ERROR 1644 (99999): Invalid charge value!
mysql> INSERT INTO STREAM(MovieId, CustomerId, StreamDate, Charge) VALUES(1, 1, '2016-01-01 19:30:00', 1);
ERROR 1644 (99999): Invalid year!
mysql> UPDATE STREAM SET Charge = -1 WHERE StreamId = 1;
ERROR 1644 (99999): Invalid charge value!
mysql> UPDATE STREAM SET StreamDate = '2016-01-01 19:30' WHERE StreamId = 1;
ERROR 1644 (99999): Invalid year!
mysql> UPDATE STREAM SET StreamId=20000 WHERE StreamId = 1;
ERROR 1644 (99999): Stream Id cannot be changed!

2

Os "triggers" beforedDepartmentInsert e beforeDepartmentUpdate já dados na aula téorica validam que um funcionário não pode ser gestor (DEPARTMENT.Manager) que mais do que um departamento.

Suponha que queremos ainda garantir que quando um funcionário é nomeado gestor de um departamento, passa automaticamente a ser supervisionado (atributo STAFF.Supervisor) pelo CEO da empresa (entrada em STAFF com Job='CEO'). Defina o código dos triggers afterDepartmentInsert e afterDepartmentUpdate para esse efeito.

Exemplos de teste:

mysql> INSERT INTO STAFF(Supervisor,Job, Name) VALUES(NULL, 'Misc. Operations Manager', 'Francisco Rocha');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT LAST_INSERT_ID() INTO @manager_id;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM STAFF WHERE StaffId = @manager_id; 
+---------+------------+--------------------------+-----------------+
| StaffId | Supervisor | Job                      | Name            |
+---------+------------+--------------------------+-----------------+
|      18 |       NULL | Misc. Operations Manager | Francisco Rocha |
+---------+------------+--------------------------+-----------------+
1 row in set (0.00 sec)

mysql> INSERT INTO DEPARTMENT(Name, Manager) VALUES('Misc. Operations', @manager_id);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM DEPARTMENT WHERE Manager = @manager_id;
+-------+------------------+---------+
| DepId | Name             | Manager |
+-------+------------------+---------+
|     5 | Misc. Operations |      18 |
+-------+------------------+---------+
1 row in set (0.00 sec)

mysql> SELECT * FROM STAFF WHERE StaffId = @manager_id;                         
+---------+------------+--------------------------+-----------------+
| StaffId | Supervisor | Job                      | Name            |
+---------+------------+--------------------------+-----------------+
|      18 |          1 | Misc. Operations Manager | Francisco Rocha |
+---------+------------+--------------------------+-----------------+
1 row in set (0.00 sec)

mysql> select * FROM STAFF WHERE Job = 'CEO';
+---------+------------+-----+-------------+
| StaffId | Supervisor | Job | Name        |
+---------+------------+-----+-------------+
|       1 |       NULL | CEO | João Pinto  |
+---------+------------+-----+-------------+
1 row in set (0.00 sec)

mysql> INSERT INTO STAFF(Supervisor,Job, Name) VALUES(NULL, 'Misc. Operations Manager', 'Francisca Silva');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT LAST_INSERT_ID() INTO @manager_id;                         
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM STAFF WHERE StaffId = @manager_id;                         
+---------+------------+--------------------------+-----------------+
| StaffId | Supervisor | Job                      | Name            |
+---------+------------+--------------------------+-----------------+
|      19 |       NULL | Misc. Operations Manager | Francisca Silva |
+---------+------------+--------------------------+-----------------+
1 row in set (0.00 sec)

mysql> UPDATE DEPARTMENT SET Manager = @manager_id WHERE Name = 'Misc. Operations';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM STAFF WHERE StaffId = @manager_id;                         
+---------+------------+--------------------------+-----------------+
| StaffId | Supervisor | Job                      | Name            |
+---------+------------+--------------------------+-----------------+
|      19 |          1 | Misc. Operations Manager | Francisca Silva |
+---------+------------+--------------------------+-----------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM DEPARTMENT WHERE Manager = @manager_id;
+-------+------------------+---------+
| DepId | Name             | Manager |
+-------+------------------+---------+
|     5 | Misc. Operations |      19 |
+-------+------------------+---------+
1 row in set (0.00 sec)