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: Before delete trigger

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

CREATE TABLE Salaries (

INSERT INTO salaries(employeeNumber,validFrom,amount)
VALUES
    (1002,'2000-01-01',50000),
    (1056,'2000-01-01',60000),
    (1076,'2000-01-01',70000);

CREATE TABLE SalaryArchives (

CREATE TRIGGER before_salaries_delete

DELETE FROM salaries WHERE employeeNumber = 1002;

SELECT * FROM SalaryArchives; 

Exercici 8: After delete trigger

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

CREATE TABLE Salaries (

INSERT INTO Salaries(employeeNumber,salary)
VALUES
    (1002,5000),
    (1056,7000),
    (1076,8000);

CREATE TABLE SalaryBudgets(

mirar bé aquesta sentència, que no estàs acostumat a fer-ho d'aquesta manera:
INSERT INTO SalaryBudgets(total)
SELECT SUM(salary) 
FROM Salaries;

CREATE TRIGGER after_salaries_delete

DELETE FROM Salaries WHERE employeeNumber = 1002;

SELECT * FROM SalaryBudgets;  

Entrega

(TBD)

---

creat per Joan Quintana Compte, maig 2022