DAM - DAW - MP 0484 Bases de Dades

El sistema bancari és un dels que més protecció ha de tenir, de manera que no podem deixar a qualsevol programador realitzar tasques directes sobre les taules de la base de dades.

Avui dia, la majoria d'aplicacions treballen amb crides API per tal d'independitzar les aplicacions del SGBD, de minimitzar els errors en programació i maximitzar la seguretat del sistema.

Exercici proposat – Triggers

La proposta és anar creant triggers que automatitzin algunes de les tasques a realitzar amb les operacions bancàries.

Posteriorment, podrem programar procediments i funcions que faran ús d'aquests triggers i seran més senzills de programar.

Podem anar realitzant els triggers en el mateix ordre en què es proposen i, així, podrem provar-los fent operacions en les diferents taules.

Farem els següents triggers:

OFICINA: BEFORE INSERT i BEFORE DELETE
COMPTE: BEFORE INSERT, AFTER INSERT, BEFORE UPDATE, AFTER UPDATE i BEFORE DELETE
MOVIMENT: BEFORE INSERT, AFTER INSERT, BEFORE UPDATE i BEFORE DELETE

Una imatge val més que mil paraules

Taula `Oficina`

`INSERT`

BEFORE INSERT

Al crear el compte els camps darrerCompte i saldoOficina SEMPRE seran 0 => Posem els valors a zero.
AFTER INSERT

No el programem. Res a fer.

Podem provar

START TRANSACTION;
INSERT INTO oficina(dOficina, adrOficina)
VALUES ('Una', 'Avda. Montilivi');
INSERT INTO oficina(dOficina, adrOficina, darrerCompte, saldoOficina)
VALUES ('Dues', 'Pl. Catalunya', 10, 900);
INSERT INTO oficina(dOficina, adrOficina, darrerCompte, saldoOficina)
VALUES ('Tres', 'C. Amunt', NULL, -1000);
SELECT * FROM oficina;
ROLLBACK;

I el resultat hauria de ser més o menys

INSERT INTO oficina(dOficina, adrOficina) VALUES ('Una', 'Avda. Montilivi');
Query OK, 1 row affected (0.034 sec)

INSERT INTO oficina(dOficina, adrOficina, darrerCompte, saldoOficina) VALUES ('Dues', 'Pl. Catalunya', 10, 900);
Query OK, 1 row affected (0.001 sec)

INSERT INTO oficina(dOficina, adrOficina, darrerCompte, saldoOficina) VALUES ('Tres', 'C. Amunt', NULL, -1000);
Query OK, 1 row affected (0.010 sec)

SELECT * FROM oficina;
+----------+----------+-----------------+--------------+--------------+
| cOficina | dOficina | adrOficina      | darrerCompte | saldoOficina |
+----------+----------+-----------------+--------------+--------------+
|        1 | Una      | Avda. Montilivi |            0 |         0.00 |
|        2 | Dues     | Pl. Catalunya   |            0 |         0.00 |
|        3 | Tres     | C. Amunt        |            0 |         0.00 |
+----------+----------+-----------------+--------------+--------------+
3 rows in set (0.000 sec)

Els valors darrerComptei saldoOficina són sempre 0.

`UPDATE`

BEFORE UPDATE

No el programem. Res a fer.
AFTER UPDATE

No el programem. Res a fer.

`DELETE`

BEFORE DELETE

No permetem que s'eliminin registres de oficina => Provoquem ERROR (SIGNAL)
AFTER DELETE

No té sentit si no permetem DELETEs

Podem provar

START TRANSACTION;
DELETE FROM oficina WHERE cOficina = 1;
ROLLBACK;

I el resultat hauria de ser més o menys

delete from oficina where cOficina = 1;
ERROR 1643 (02000): ERROR: No es poden eliminar Oficines

Fixeu-vos en l'ERROR que ens ha donat!

Taula `Compte`

`INSERT`

BEFORE INSERT

Al crear el compte els camps numeroOperacionsAdd, numeroOperacionsSub i saldoCompte SEMPRE seran 0 => Posem els valors a zero

Pel valor pel número de Compte (cCompte), anem a buscar a Oficina el camp darrerCompte i assignem aquest incrementat en un => Agafem el valor calculat.

Opcional: Ja que hem anat a buscar el valor darrerCompte a la taula Oficina, en cas que no existeixi la Oficina, podem provocar un ERROR personalitzat. En cas contrari donarà error el SGBDR per no trobar la PK del camp cOficina.

No modifiquem, encara, el campdarrerCompte de la taula Oficina
AFTER INSERT

Modificarem la taula Oficina segons els valors de l'INSERT. Els camps a modificar són:
- darrerCompte
Ara sí, quan ja tenim la conformitat que tot és correcte, modifiquem el campdarrerCompte de la taula Oficina

Podem provar

START TRANSACTION;
INSERT INTO Oficina(cOficina) VALUES(9999);
INSERT INTO Compte(cOficina, cCompte, numeroOperacionsAdd, numeroOperacionsSub, saldoCompte)
VALUES (1,1,1,2,30.00);
INSERT INTO Compte(cOficina, cCompte, numeroOperacionsAdd, numeroOperacionsSub, saldoCompte)
VALUES (2,NULL,DEFAULT,0,250.00);
INSERT INTO Compte(cOficina) VALUES(2);
SELECT * FROM Oficina;
SELECT * FROM Compte;
ROLLBACK;

I el resultat hauria de ser més o menys

INSERT INTO Oficina(cOficina) VALUES(9999);
ERROR 1643 (02000): Oficina 9999 inexistent
INSERT INTO Compte(cOficina, cCompte, numeroOperacionsAdd, numeroOperacionsSub, saldoCompte)
VALUES (1,1,1,2,30.00);
Query OK, 1 row affected (0.000 sec)
INSERT INTO Compte(cOficina, cCompte, numeroOperacionsAdd, numeroOperacionsSub, saldoCompte)
VALUES (2,NULL,DEFAULT,0,250.00);
Query OK, 1 row affected (0.037 sec)
INSERT INTO Compte(cOficina) VALUES(2);
Query OK, 1 row affected (0.001 sec)
SELECT * FROM Oficina;
+----------+----------+-----------------+--------------+--------------+
| cOficina | dOficina | adrOficina      | darrerCompte | saldoOficina |
+----------+----------+-----------------+--------------+--------------+
|        1 | Una      | Avda. Montilivi |            1 |         0.00 |
|        2 | Dues     | Pl. Catalunya   |            2 |         0.00 |
|        3 | Tres     | C. Amunt        |            0 |         0.00 |
+----------+----------+-----------------+--------------+--------------+
SELECT * FROM Compte;
+----------+---------+---------------------+---------------------+-------------+
| cOficina | cCompte | numeroOperacionsAdd | numeroOperacionsSub | saldoCompte |
+----------+---------+---------------------+---------------------+-------------+
|        1 |       1 |                   0 |                   0 |        0.00 |
|        2 |       1 |                   0 |                   0 |        0.00 |
|        2 |       2 |                   0 |                   0 |        0.00 |
+----------+---------+---------------------+---------------------+-------------+

Mireu l'ordre en què s'executen els triggers amb les comandes

El primer INSERT, tot i no tenir totes les dades, executa el trigger BEFORE INSERT abans de fer les comprovacions i ja dona ERROR.

Fixeu-vos que per crear un Compte, només cal indicar el Codi d'oficina cOficina

Ignora la resta de camps que li passem.

També s'ha actualitzat darrerComte de la taula Oficina

`UPDATE`

BEFORE UPDATE

No permetem canviar ni el cOficina ni el cCompte. En el cas d'intentar modificar-los cal generar un ERROR.

Com que no admetem comptes amb saldo negatiu, en cas de posar un saldo inferior a 0 generarem un ERROR.
AFTER UPDATE

En el cas de modificar-se el saldoCompte, modificarem la taula Oficina segons els valors de l'UPDATE. El camp a modificar és:
- saldoOficina

Podem provar

START TRANSACTION;
UPDATE Compte SET cCompte = 10 where cCompte = 2;
UPDATE Compte SET saldoCompte = -10 where cCompte = 2;
UPDATE Compte SET saldoCompte = 30.00 WHERE cOficina = 1 AND cCompte = 1;
UPDATE Compte SET saldoCompte = 250.00 WHERE cOficina = 2 AND cCompte = 1;
UPDATE Compte SET saldoCompte = 200.00 WHERE cOficina = 2 AND cCompte = 2;
UPDATE Compte SET saldoCompte = 50.00 WHERE cOficina = 2 AND cCompte = 1;
SELECT * FROM Oficina;
SELECT * FROM Compte;
ROLLBACK;

I el resultat hauria de ser més o menys

UPDATE Compte SET cCompte = 10 where cCompte = 2;
ERROR 1643 (02000): ERROR: No es pot modificar el codi de la Oficina ni del Compte
UPDATE Compte SET saldoCompte = -10 where cCompte = 2;
ERROR 1643 (02000): ERROR: No es permetem saldos negatius
UPDATE Compte SET saldoCompte = 30.00 WHERE cOficina = 1 AND cCompte = 1;
Query OK, 1 row affected (0.001 sec)
UPDATE Compte SET saldoCompte = 250.00 WHERE cOficina = 2 AND cCompte = 1;
Query OK, 1 row affected (0.001 sec)
UPDATE Compte SET saldoCompte = 200.00 WHERE cOficina = 2 AND cCompte = 2;
Query OK, 1 row affected (0.000 sec)
UPDATE Compte SET saldoCompte = 50.00 WHERE cOficina = 2 AND cCompte = 1;
Query OK, 1 row affected (0.000 sec)
SELECT * FROM Oficina;
+----------+----------+-----------------+--------------+--------------+
| cOficina | dOficina | adrOficina      | darrerCompte | saldoOficina |
+----------+----------+-----------------+--------------+--------------+
|        1 | Una      | Avda. Montilivi |            1 |        30.00 |
|        2 | Dues     | Pl. Catalunya   |            2 |       250.00 |
|        3 | Tres     | C. Amunt        |            0 |         0.00 |
+----------+----------+-----------------+--------------+--------------+
SELECT * FROM Compte;
+----------+---------+---------------------+---------------------+-------------+
| cOficina | cCompte | numeroOperacionsAdd | numeroOperacionsSub | saldoCompte |
+----------+---------+---------------------+---------------------+-------------+
|        1 |       1 |                   0 |                   0 |       30.00 |
|        2 |       1 |                   0 |                   0 |       50.00 |
|        2 |       2 |                   0 |                   0 |      200.00 |
+----------+---------+---------------------+---------------------+-------------+
ROLLBACK;

També s'ha actualitzat saldoOficina de la taula Oficina

Vigileu que el saldoOficina ha de ser la suma dels saldos dels Comptes

La següent consulta no hauria de mostar cap fila

SELECT cOficina
FROM Oficina o 
WHERE saldoOficina <> (SELECT SUM(saldoCompte)
                        FROM Compte c 
                        WHERE c.cOficina = o.cOficina);

Empty set (0.042 sec)

`DELETE`

BEFORE DELETE

No permetem que s'eliminin registres de compte => Provoquem ERROR (SIGNAL)
AFTER DELETE

No té sentit si no permetem DELETEs

Podem provar

START TRANSACTION;
DELETE FROM Compte WHERE cOficina = 1 AND cCompte = 1;
ROLLBACK;

I el resultat hauria de ser més o menys

DELETE FROM Compte WHERE cOficina = 1 AND cCompte = 1;
ERROR 1643 (02000): ERROR: No es poden eliminar Comptes

Podriem canviar el criteri i permetre eliminar només Comptes amb Saldo 0 o bé, abans d'eliminar un Compte, guardar les dades en una altra taula

Taula `Moviment`

`INSERT`

BEFORE INSERT

El valor del número de moviment (numeroMoviment) el busquem escollint el màxim + 1. Si és el primer, li posem 1.

Podem aprofitar per comprovar si existeix el Compte i en cas contrari generar un ERROR personalitzat.

Sempre ha de quedar gravat com a moment, el dia actual: CURRENT_DATE())

No permetem que l'import d'una operació sigui 0 => Provoquem ERROR (SIGNAL)

Si no ens passen el camp tipusMoviment llavors:
- si l'import és positiu valdrà Ingrés
- si l'import és negatiu valdrà Reintegre
No permetem que l'import d'una operació faci que el saldo del compte sigui negatiu => Provoquem ERROR (SIGNAL)

Aquest darrer cas no cal contemplar-lo ja que, en modificar el saldoCompte amb el trigger AFTER UPDATE, el trigger BEFORE UPDATE de la taula Compte generarà un error.
AFTER INSERT

Modificarem la taula Compte segons els valors de l'INSERT. Els camps a modificar són:
- numeroOperacionsAdd: quan l'import sigui positiu l'incrementem en 1.
- numeroOperacionsSub: quan l'import sigui negatiu l'incrementem en 1.
- saldoCompte: l'actualitzem segone l'import.

Podem provar

START TRANSACTION;
INSERT INTO Moviment(import) VALUES(0);
INSERT INTO Moviment(cOficina, cCompte, import)
VALUES(1,1,-100);
INSERT INTO Moviment(cOficina, cCompte, import)
VALUES(1,1,0);
INSERT INTO Moviment(cOficina, cCompte, numeroMoviment, import, moment, tipusMoviment)
VALUES(1,1,999,150, '1900-01-01', 'Ingres 1' );
INSERT INTO Moviment(cOficina, cCompte, import)
VALUES(1,1,-75);
INSERT INTO Moviment(cOficina, cCompte, import)
VALUES(2,1,175);
INSERT INTO Moviment(cOficina, cCompte, import)
VALUES(2,1,250);
INSERT INTO Moviment(cOficina, cCompte, import)
VALUES(2,2,750);
INSERT INTO Moviment(cOficina, cCompte, import)
VALUES(2,2,-250);
SELECT * FROM Oficina;
SELECT * FROM Compte  ORDER BY cOficina, cCompte;
SELECT * FROM Moviment ORDER BY cOficina, cCompte, numeroMoviment;
ROLLBACK;

I el resultat hauria de ser més o menys

START TRANSACTION;
INSERT INTO Moviment(import) VALUES(0);
ERROR 1643 (02000): L´import no pot ser 0
INSERT INTO Moviment(cOficina, cCompte, import)
VALUES(1,1,-100);
ERROR 1643 (02000): ERROR: No es permetem saldos negatius
INSERT INTO Moviment(cOficina, cCompte, import)
VALUES(1,1,0);
ERROR 1643 (02000): L´import no pot ser 0
INSERT INTO Moviment(cOficina, cCompte, numeroMoviment, import, moment, tipusMoviment)
VALUES(1,1,999,150, '1900-01-01', 'Ingres 1' );
Query OK, 1 row affected (0.001 sec)
INSERT INTO Moviment(cOficina, cCompte, import)
VALUES(1,1,-75);
Query OK, 1 row affected (0.001 sec)
INSERT INTO Moviment(cOficina, cCompte, import)
VALUES(2,1,175);
Query OK, 1 row affected (0.000 sec)
INSERT INTO Moviment(cOficina, cCompte, import)
VALUES(2,1,250);
Query OK, 1 row affected (0.000 sec)
INSERT INTO Moviment(cOficina, cCompte, import)
VALUES(2,2,750);
Query OK, 1 row affected (0.000 sec)
INSERT INTO Moviment(cOficina, cCompte, import)
VALUES(2,2,-250);
Query OK, 1 row affected (0.001 sec)
SELECT * FROM Oficina;
+----------+----------+-----------------+--------------+--------------+
| cOficina | dOficina | adrOficina      | darrerCompte | saldoOficina |
+----------+----------+-----------------+--------------+--------------+
|        1 | Una      | Avda. Montilivi |            1 |        75.00 |
|        2 | Dues     | Pl. Catalunya   |            2 |       925.00 |
|        3 | Tres     | C. Amunt        |            0 |         0.00 |
+----------+----------+-----------------+--------------+--------------+
SELECT * FROM Compte ORDER BY cOficina, cCompte;
+----------+---------+---------------------+---------------------+-------------+
| cOficina | cCompte | numeroOperacionsAdd | numeroOperacionsSub | saldoCompte |
+----------+---------+---------------------+---------------------+-------------+
|        1 |       1 |                   1 |                   1 |       75.00 |
|        2 |       1 |                   2 |                   0 |      425.00 |
|        2 |       2 |                   1 |                   1 |      500.00 |
+----------+---------+---------------------+---------------------+-------------+
SELECT * FROM Moviment ORDER BY cOficina, cCompte, numeroMoviment;
+----------+---------+----------------+---------+------------+---------------+
| cOficina | cCompte | numeroMoviment | import  | moment     | tipusMoviment |
+----------+---------+----------------+---------+------------+---------------+
|        1 |       1 |              1 |  150.00 | 2023-02-25 | Ingres 1      |
|        1 |       1 |              2 |  -75.00 | 2023-02-25 | Reintegre     |
|        2 |       1 |              1 |  175.00 | 2023-02-25 | Ingrés        |
|        2 |       1 |              2 |  250.00 | 2023-02-25 | Ingrés        |
|        2 |       2 |              1 |  750.00 | 2023-02-25 | Ingrés        |
|        2 |       2 |              2 | -250.00 | 2023-02-25 | Reintegre     |
+----------+---------+----------------+---------+------------+---------------+
ROLLBACK;

Fixeu-vos com no agafa els valors de numeroMoviment i moment que li passem nosaltres.

Els únics camps necessaris són cOficina, cCompte, import.

S'han actualitzat els saldos de l'oficina i del compte.

El saldo negatiu no es permet, però pel trigger del Compte

`UPDATE`

BEFORE UPDATE

No permetem que es modifiquin registres de moviment => Provoquem ERROR (SIGNAL)
AFTER UPDATE

No té sentit si no permetem UPDATEs

Podem provar

START TRANSACTION;
UPDATE Moviment SET import = 0;
ROLLBACK;

I el resultat hauria de ser més o menys

UPDATE Moviment SET import = 0;
ERROR 1643 (02000): ERROR: No es poden modificar Moviments

No permetem modificar moviments.

En cas de poder-los modificar caldria:

vigilar si modifiquem o no el codi d'Oficina o de Compte
actualitzar el Compte antic i actualitzar el nou.

les oficines ja s'actualitzaran automàticament.

`DELETE`

BEFORE DELETE

No permetem que s'eliminin registres de moviment => Provoquem ERROR (SIGNAL)
AFTER DELETE

No té sentit si no permetem DELETEs

Podem provar

START TRANSACTION;
DELETE FROM Moviment;
ROLLBACK;

I el resultat hauria de ser més o menys

DELETE FROM Moviment;
ERROR 1643 (02000): ERROR: No es poden eliminar Moviments

No permetem eliminar moviments.

DAM - DAW - MP 0484 Bases de Dades

Exercici proposat – Triggers

Taula Oficina

INSERT

UPDATE

DELETE

Taula Compte

INSERT

UPDATE

DELETE

Taula Moviment

INSERT

UPDATE

DELETE

Taula `Oficina`

`INSERT`

`UPDATE`

`DELETE`

Taula `Compte`

`INSERT`

`UPDATE`

`DELETE`

Taula `Moviment`

`INSERT`

`UPDATE`

`DELETE`