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.

Possible solució

DELIMITER //
CREATE OR REPLACE TRIGGER OFICINA_BI
    BEFORE INSERT ON OFICINA
    FOR EACH ROW
BEGIN
    SET NEW.darrerCompte = 0;
    SET NEW.saldoOficina = 0;
END;
//

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)

Possible solució

DELIMITER //
CREATE OR REPLACE TRIGGER OFICINA_BD
    BEFORE DELETE ON OFICINA
    FOR EACH ROW
BEGIN
    SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'ERROR: No es poden eliminar Oficines';
END;
//

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

Possible solució

DELIMITER //
CREATE OR REPLACE TRIGGER COMPTE_BI
    BEFORE INSERT ON COMPTE
    FOR EACH ROW
BEGIN
    DECLARE v_darrerCompte TYPE OF Oficina.darrerCompte;
    DECLARE v_missatge VARCHAR(100);
    -- Anem a buscar el darrerCompte a Oficina
    SELECT darrerCompte INTO v_darrerCompte
      FROM OFICINA
      WHERE cOficina = NEW.cOficina;
    IF v_darrerCompte IS NULL THEN
        SET v_missatge = CONCAT_WS(' ', "Oficina", LPAD(NEW.cOficina, 4, '0'), 'inexistent');
        SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = v_missatge;
    END IF;
    SET NEW.cCompte = v_darrerCompte + 1;
    SET NEW.numeroOperacionsAdd = 0;
    SET NEW.numeroOperacionsSub = 0;
    SET NEW.saldoCompte = 0;
END;
//

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

Possible solució

DELIMITER //
CREATE OR REPLACE TRIGGER COMPTE_AI
    AFTER INSERT ON COMPTE
    FOR EACH ROW
BEGIN
    UPDATE Oficina
      SET darrerCompte = darrerCompte + 1
      WHERE cOficina = NEW.cOficina;
END;
//

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.

Possible solució

DELIMITER //
CREATE OR REPLACE TRIGGER COMPTE_BU
    BEFORE UPDATE ON COMPTE
    FOR EACH ROW
BEGIN
    IF OLD.cCompte <> NEW.cCompte OR OLD.cOficina <> NEW.cOficina THEN
        SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'ERROR: No es pot modificar el codi de la Oficina ni del Compte';
    END IF;
    IF NEW.saldoCompte <0 THEN
        SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'ERROR: No es permetem saldos negatius';
    END IF;
END;
//

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

Possible solució

DELIMITER //
CREATE OR REPLACE TRIGGER OFICINA_BD
    BEFORE DELETE ON OFICINA
    FOR EACH ROW
BEGIN
    SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'ERROR: No es poden eliminar Oficines';
END;
//

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)

Possible solució

DELIMITER //
CREATE OR REPLACE TRIGGER COMPTE_BD
    BEFORE DELETE ON COMPTE
    FOR EACH ROW
BEGIN
    SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'ERROR: No es poden eliminar Comptes';
END;
//

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.

Possible solució

DELIMITER //
CREATE OR REPLACE TRIGGER MOVIMENT_BI
    BEFORE INSERT ON MOVIMENT
    FOR EACH ROW
BEGIN
    DECLARE v_numeroMoviment TYPE OF Moviment.numeroMoviment;

    IF NEW.import= 0 THEN
        SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = "L'import no pot ser 0";
    END IF;
    SELECT IFNULL(MAX(numeroMoviment), 0)
      INTO v_numeroMoviment
      FROM Moviment
      WHERE cOficina = NEW.cOficina AND cCompte = NEW.cCompte;
    SET NEW.numeroMoviment = v_numeroMoviment + 1;
    SET NEW.moment = CURRENT_TIMESTAMP();
    IF NEW.tipusMoviment IS NULL THEN
        IF NEW.import > 0 THEN
            SET NEW.tipusMoviment = 'Ingrés';
        ELSE
            SET NEW.tipusMoviment = 'Reintegre';
        END IF;
    END IF;
END;
//

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.

Possible solució

DELIMITER //
CREATE OR REPLACE TRIGGER MOVIMENT_AI
    AFTER INSERT ON MOVIMENT
    FOR EACH ROW
BEGIN
    IF NEW.import > 0 THEN
        UPDATE Compte
          SET saldoCompte = saldoCompte + NEW.import,
              numeroOperacionsAdd = numeroOperacionsAdd + 1
          WHERE cOficina = NEW.cOficina AND cCompte = NEW.cCompte;
    ELSE 
        UPDATE Compte
          SET saldoCompte = saldoCompte + NEW.import,
              numeroOperacionsSub = numeroOperacionsSub + 1
          WHERE cOficina = NEW.cOficina AND cCompte = NEW.cCompte;
    END IF;
END;
//

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)

Possible solució

DELIMITER //
CREATE OR REPLACE TRIGGER MOVIMENT_BU
    BEFORE UPDATE ON MOVIMENT
    FOR EACH ROW
BEGIN
    SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'ERROR: No es poden modificar Moviments';
END;
//

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)

Possible solució

DELIMITER //
CREATE OR REPLACE TRIGGER MOVIMENT_BD
    BEFORE DELETE ON MOVIMENT
    FOR EACH ROW
BEGIN
    SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'ERROR: No es poden eliminar Moviments';
END;
//

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`