Pràctica botiga bicicletes. Versió triggers

Introducció

A classe vam fer l'exercici de procediments.

• Teoria: Empresa bàsica: compra de clients i compra a proveïdors
• Pràctica: Pràctica botiga de bicicletes

Es tractava d'una botiga de venda de bicicletes, que tenien els seus proveïdors i clients, i controlàvem el stock de manera que no ens quedem sense bicicletes.

Teníem 6 versions, i en aquesta pràctica farem la versió v7 utilitzant triggers/disparadors.

Procediment compraBicicleta_v7

En el següent codi partim de la v6, i hem comentat les línies que s'executaran des dels disparadors. Per exemple, totes les insercions a la taula AUDIT les fan els disparadors, i també l'actualització del stock quan es fa una venda.

DROP PROCEDURE IF EXISTS compraBicicleta_v7;

DELIMITER $$
CREATE PROCEDURE compraBicicleta_v7 (
   IN vclient VARCHAR(50),
   IN vid_producte SMALLINT,
   IN vquantitat SMALLINT
)
proc_label:BEGIN
   DECLARE vid_client SMALLINT;
   DECLARE vid_producte_temp SMALLINT;
   DECLARE vstock SMALLINT;
   DECLARE vid_proveidor SMALLINT;

   SELECT id_client INTO vid_client FROM CLIENT WHERE client = vclient;
   IF vid_client IS NULL THEN
      INSERT INTO CLIENT(client) VALUES (vclient);
      SELECT MAX(id_client) INTO vid_client FROM CLIENT;
      #ja no cal, ara és un trigger
      #INSERT INTO AUDIT(audit_info,data) VALUES(CONCAT('S''ha inserit el id_client: ',vid_client),NOW());
   END IF;
   SELECT CONCAT("id_client: ",vid_client) as id_client;

   SELECT id_producte, stock INTO vid_producte_temp, vstock FROM PRODUCTE WHERE id_producte = vid_producte;
   IF vid_producte_temp IS NULL THEN
      SELECT CONCAT("El producte: ",vid_producte, " no existeix a la base de dades") as producte;
      LEAVE proc_label;
   ELSE
      SELECT CONCAT("id_producte: ",vid_producte) AS producte;
   END IF;

   SELECT CONCAT("stock: ",vstock) AS stock;
   IF (vquantitat<=0 OR vquantitat > vstock) THEN
      SELECT CONCAT("quantitat no vàlida") AS quantitat;
      LEAVE proc_label;
   END IF;

   # ja podem fer la compra
   INSERT INTO COMPRA_CLIENT VALUES (vid_client,vid_producte,vquantitat,NOW());
   #ja no cal, és un trigger
   #INSERT INTO AUDIT(audit_info,data) VALUES(CONCAT('El client ',vid_client,' ha fet una compra del producte ',vid_producte,' (',vquantitat,' unitats)'),NOW());
   # actualitzem el stock
   #ja no cal, és un trigger
   #UPDATE PRODUCTE set stock=vstock-vquantitat WHERE id_producte=vid_producte;
   SELECT CONCAT("queden: ",vstock-vquantitat," unitats") as unitats_restants;
   #ja no cal, és un trigger
   #INSERT INTO AUDIT(audit_info,data) VALUES(CONCAT('Del producte ',vid_producte,' en queden ',vstock-vquantitat,' unitats'),NOW());

   IF (vstock-vquantitat)<=1 THEN
      SELECT id_proveidor INTO vid_proveidor FROM PRODUCTE WHERE id_producte=vid_producte;
      SELECT CONCAT("id_proveidor: ",vid_proveidor) as id_proveidor;
      INSERT INTO COMPRA_PROVEIDOR VALUES (vid_proveidor,vid_producte,3,NOW());
      #ja no cal, és un trigger
      #INSERT INTO AUDIT(audit_info,data) VALUES(CONCAT('S''ha realitzat una compra del producte ',vid_producte,' al proveïdor ',vid_proveidor),NOW());
      #UPDATE PRODUCTE set stock=stock+3 WHERE id_producte=vid_producte;
      INSERT INTO AUDIT(audit_info,data) VALUES(CONCAT('Ara tenim ',vstock-vquantitat+3,' unitats del producte ',vid_producte),NOW());
      SELECT CONCAT("s'ha fet una compra. Ara tenim ",vstock-vquantitat+3," unitats") as unitats;
   END IF;

END$$

DELIMITER ;

El mateix codi on hem eliminat les línies comentades, i així veiem que el codi queda molt més curt:

DROP PROCEDURE IF EXISTS compraBicicleta_v7;

DELIMITER $$
CREATE PROCEDURE compraBicicleta_v7 (
   IN vclient VARCHAR(50),
   IN vid_producte SMALLINT,
   IN vquantitat SMALLINT
)
proc_label:BEGIN
   DECLARE vid_client SMALLINT;
   DECLARE vid_producte_temp SMALLINT;
   DECLARE vstock SMALLINT;
   DECLARE vid_proveidor SMALLINT;

   SELECT id_client INTO vid_client FROM CLIENT WHERE client = vclient;
   IF vid_client IS NULL THEN
      INSERT INTO CLIENT(client) VALUES (vclient);
      SELECT MAX(id_client) INTO vid_client FROM CLIENT;
   END IF;
   SELECT CONCAT("id_client: ",vid_client) as id_client;

   SELECT id_producte, stock INTO vid_producte_temp, vstock FROM PRODUCTE WHERE id_producte = vid_producte;
   IF vid_producte_temp IS NULL THEN
      SELECT CONCAT("El producte: ",vid_producte, " no existeix a la base de dades") as producte;
      LEAVE proc_label;
   ELSE
      SELECT CONCAT("id_producte: ",vid_producte) AS producte;
   END IF;

   SELECT CONCAT("stock: ",vstock) AS stock;
   IF (vquantitat<=0 OR vquantitat > vstock) THEN
      SELECT CONCAT("quantitat no vàlida") AS quantitat;
      LEAVE proc_label;
   END IF;

   # ja podem fer la compra
   INSERT INTO COMPRA_CLIENT VALUES (vid_client,vid_producte,vquantitat,NOW());

   SELECT CONCAT("queden: ",vstock-vquantitat," unitats") as unitats_restants;

   IF (vstock-vquantitat)<=1 THEN
      SELECT id_proveidor INTO vid_proveidor FROM PRODUCTE WHERE id_producte=vid_producte;
      SELECT CONCAT("id_proveidor: ",vid_proveidor) as id_proveidor;
      INSERT INTO COMPRA_PROVEIDOR VALUES (vid_proveidor,vid_producte,3,NOW());
      INSERT INTO AUDIT(audit_info,data) VALUES(CONCAT('Ara tenim ',vstock-vquantitat+3,' unitats del producte ',vid_producte),NOW());
      SELECT CONCAT("s'ha fet una compra. Ara tenim ",vstock-vquantitat+3," unitats") as unitats;
   END IF;

END$$

DELIMITER ;

Disparadors

Es creen tres disparadors:

1. trigger afterInsertClient:

# trigger afterInsertClient

DELIMITER $$
CREATE TRIGGER afterInsertClient
    AFTER INSERT 
    ON CLIENT FOR EACH ROW
BEGIN
	INSERT INTO AUDIT(audit_info,data) VALUES(CONCAT('S''ha inserit el id_client: ',NEW.id_client),NOW());
END$$    
DELIMITER ;

2. trigger afterInsertCompraClient:

# trigger afterInsertCompraClient

DELIMITER $$
CREATE TRIGGER afterInsertCompraClient
    AFTER INSERT 
    ON COMPRA_CLIENT FOR EACH ROW
BEGIN
	DECLARE vstock SMALLINT;
	DECLARE vid_client SMALLINT;
	DECLARE vid_producte SMALLINT;
	DECLARE vquantitat SMALLINT;
	DECLARE vid_proveidor SMALLINT;

	SET vid_client = NEW.id_client;
	SET vid_producte = NEW.id_producte;
	SET vquantitat = NEW.quantitat;

	INSERT INTO AUDIT(audit_info,data) VALUES(CONCAT('El client ',vid_client,' ha fet una compra del producte ',vid_producte,' (',vquantitat,' unitats)'),NOW());
	SELECT stock INTO vstock FROM PRODUCTE WHERE id_producte = vid_producte;
	UPDATE PRODUCTE set stock=vstock-vquantitat WHERE id_producte=vid_producte;
	INSERT INTO AUDIT(audit_info,data) VALUES(CONCAT('Del producte ',vid_producte,' en queden ',vstock-vquantitat,' unitats'),NOW());

END$$    
DELIMITER ;

3. trigger afterInsertCompraProveidor:

# trigger afterInsertCompraProveidor

DELIMITER $$
CREATE TRIGGER afterInsertCompraProveidor
    AFTER INSERT 
    ON COMPRA_PROVEIDOR FOR EACH ROW
BEGIN

	INSERT INTO AUDIT(audit_info,data) VALUES(CONCAT('S''ha realitzat una compra del producte ',NEW.id_producte,' al proveïdor ',NEW.id_proveidor),NOW());
	UPDATE PRODUCTE set stock=stock+3 WHERE id_producte=NEW.id_producte;
	INSERT INTO AUDIT(audit_info,data) VALUES(CONCAT('Hem augmentat ',NEW.quantitat,' unitats del producte ',NEW.id_producte),NOW());
END$$    
DELIMITER ;

Per veure els disparadors que has creat:

mysql> SHOW TRIGGERS;

Millor:

mysql> SHOW TRIGGERS\G;

o bé

mysql> select trigger_name from information_schema.triggers where 
information_schema.triggers.trigger_schema = 'empresa2';

Comentaris

Fixar-se que quan esborrem les taules també s'esborren els disparadors. Per tant, si volem regererar totes les dades (els drops, creates i inserts de les dades estan en la teoria: Empresa_bàsica: compra_de_clients i compra a proveïdors, després d'executar els creates i insertes hem de tornar a crear els triggers. (En canvi, el procediment no desapareix, no cal tornar-lo a crear).

A dins d'un trigger no podem fer selects de cap mena. És una de les limitacions que tenen els triggers a MySQL:

ERROR 1415 (0A000): Not allowed to return a result set from a trigger

I aquest és el motiu pel qual en el procediment hi ha, al final de tot, un insert sobre la taula AUDIT que no hem pogut fer en el disparador. I és que des de dins del trigger no podem recuperar el valor del stock del producte:

   SELECT id_producte, stock INTO vid_producte_temp, vstock FROM PRODUCTE WHERE id_producte = vid_producte;
...
      INSERT INTO AUDIT(audit_info,data) VALUES(CONCAT('Ara tenim ',vstock-vquantitat+3,' unitats del producte ',vid_producte),NOW());
      SELECT CONCAT("s'ha fet una compra. Ara tenim ",vstock-vquantitat+3," unitats") as unitats;

Sortida per pantalla

CALL compraBicicleta_v7('client trigger',1,1);

Sortida del procediment:

| id_client: 1 |
| id_producte: 1 |
| stock: 3 |
| queden: 2 unitats |

select * from AUDIT;

|        1 | S'ha inserit el id_client: 1                             | 2022-05-18 15:24:50 |
|        2 | El client 1 ha fet una compra del producte 1 (1 unitats) | 2022-05-18 15:24:50 |
|        3 | Del producte 1 en queden 2 unitats                       | 2022-05-18 15:24:50 |

Tornem a executar el procediment:

CALL compraBicicleta_v7('client trigger',1,1);

Sortida del procediment:

| id_client: 1 |
| id_producte: 1 |
| stock: 2 |
| queden: 1 unitats |

select * from AUDIT;

|        1 | S'ha inserit el id_client: 1                             | 2022-05-18 15:24:50 |
|        2 | El client 1 ha fet una compra del producte 1 (1 unitats) | 2022-05-18 15:24:50 |
|        3 | Del producte 1 en queden 2 unitats                       | 2022-05-18 15:24:50 |
|        4 | El client 1 ha fet una compra del producte 1 (1 unitats) | 2022-05-18 15:25:42 |
|        5 | Del producte 1 en queden 1 unitats                       | 2022-05-18 15:25:42 |
|        6 | Ara tenim 4 unitats del producte 1                       | 2022-05-18 15:25:42 |

Tasques a realitzar

(TBD)

Entrega

(TBD)

---

creat per Joan Quintana Compte, maig 2022