Pràctica sobre la teoria. Disparadors / triggers a MySQL

Introducció

(TBD)

Tasques a realitzar

• https://www.mysqltutorial.org/mysql-triggers/

Exercici 1

• https://www.mysqltutorial.org/create-the-first-trigger-in-mysql.aspx

CREATE TABLE employees_audit
CREATE TRIGGER before_employee_update 

Exercici 2

• https://www.mysqltutorial.org/mysql-triggers/mysql-drop-trigger/

CREATE TABLE billings (
CREATE TRIGGER before_billing_update

Fer un insert sobre la taula billings, i fer dos updates: el primer que la quantitat no sigui superior a 10 vegades el amount; i el segon que sí que superi 10 vegades el amount, per veure com s'aixeca la SIGNAL i així s'avorta el update.

Exercici 3

• https://www.mysqltutorial.org/mysql-triggers/mysql-before-insert-trigger/

CREATE TABLE WorkCenters (
CREATE TABLE WorkCenterStats(
CREATE TRIGGER before_workcenters_insert

Fer 3 ó 4:

INSERT INTO WorkCenters(name, capacity)

i veure com la capacitat s'acumula en la taula WorkCenterStats.

Exercici 4

• https://www.mysqltutorial.org/mysql-triggers/mysql-after-insert-trigger/

CREATE TABLE members (
CREATE TABLE reminders (
CREATE TRIGGER after_members_insert

INSERT INTO members(name, email, birthDate)
VALUES
    ('John Doe', 'john.doe@example.com', NULL),
    ('Jane Doe', 'jane.doe@example.com','2000-01-01');

Exercici 5

• https://www.mysqltutorial.org/mysql-triggers/mysql-before-update-trigger/

CREATE TABLE sales (

INSERT INTO sales(product, quantity, fiscalYear, fiscalMonth)
VALUES
    ('2003 Harley-Davidson Eagle Drag Bike',120, 2020,1),
    ('1969 Corvair Monza', 150,2020,1),
    ('1970 Plymouth Hemi Cuda', 200,2020,1);

CREATE TRIGGER before_sales_update

UPDATE sales SET quantity = 150 WHERE id = 1;
UPDATE sales SET quantity = 500 WHERE id = 1;

SHOW ERRORS;

Exercici 6

• https://www.mysqltutorial.org/mysql-triggers/mysql-after-update-trigger/

CREATE TABLE Sales (

INSERT INTO Sales(product, quantity, fiscalYear, fiscalMonth)
VALUES
    ('2001 Ferrari Enzo',140, 2021,1),
    ('1998 Chrysler Plymouth Prowler', 110,2021,1),
    ('1913 Ford Model T Speedster', 120,2021,1);

CREATE TABLE SalesChanges (

CREATE TRIGGER after_sales_update

UPDATE Sales SET quantity = 350 WHERE id = 1;
UPDATE Sales SET quantity = CAST(quantity * 1.1 AS UNSIGNED);

Exercici 7

• https://www.mysqltutorial.org/mysql-triggers/mysql-before-delete-trigger/

Entrega

(TBD)

---

creat per Joan Quintana Compte, maig 2022