AFTER INSERT
CREATE TABLE members (
id INT AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
email VARCHAR(255),
birthDate DATE,
PRIMARY KEY (id)
);
CREATE TABLE reminders (
id INT AUTO_INCREMENT,
memberId INT,
message VARCHAR(255) NOT NULL,
PRIMARY KEY (id , memberId)
);
DELIMITER $$
CREATE TRIGGER after_members_insert
AFTER INSERT
ON members FOR EACH ROW
BEGIN
IF [Link] IS NULL THEN
INSERT INTO reminders(memberId, message)
VALUES([Link],CONCAT('Hi ', [Link], ', please update your date of
birth.'));
END IF;
END$$
DELIMITER ;
BEFORE INSERT
CREATE TABLE WorkCenters (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
capacity INT NOT NULL
);
CREATE TABLE WorkCenterStats(
totalCapacity INT NOT NULL
);
DELIMITER $$
CREATE TRIGGER before_workcenters_insert
BEFORE INSERT
ON WorkCenters FOR EACH ROW
BEGIN
DECLARE rowcount INT;
SELECT COUNT(*)
INTO rowcount
FROM WorkCenterStats;
IF rowcount > 0 THEN
UPDATE WorkCenterStats
SET totalCapacity = totalCapacity + [Link];
ELSE
INSERT INTO WorkCenterStats(totalCapacity)
VALUES([Link]);
END IF;
END $$
DELIMITER ;
BEFORE UPDATE
CREATE TABLE sales (
id INT AUTO_INCREMENT,
product VARCHAR(100) NOT NULL,
quantity INT NOT NULL DEFAULT 0,
fiscalYear SMALLINT NOT NULL,
fiscalMonth TINYINT NOT NULL,
CHECK(fiscalMonth >= 1 AND fiscalMonth <= 12),
CHECK(fiscalYear BETWEEN 2000 and 2050),
CHECK (quantity >=0),
UNIQUE(product, fiscalYear, fiscalMonth),
PRIMARY KEY(id)
);
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);
DELIMITER $$
CREATE TRIGGER before_sales_update
BEFORE UPDATE
ON sales FOR EACH ROW
BEGIN
DECLARE errorMessage VARCHAR(255);
SET errorMessage = CONCAT('The new quantity ',
[Link],
' cannot be 3 times greater than the current quantity ',
[Link]);
IF [Link] > [Link] * 3 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = errorMessage;
END IF;
END $$
DELIMITER ;
AFTER UPDATE
CREATE TABLE Sales (
id INT AUTO_INCREMENT,
product VARCHAR(100) NOT NULL,
quantity INT NOT NULL DEFAULT 0,
fiscalYear SMALLINT NOT NULL,
fiscalMonth TINYINT NOT NULL,
CHECK(fiscalMonth >= 1 AND fiscalMonth <= 12),
CHECK(fiscalYear BETWEEN 2000 and 2050),
CHECK (quantity >=0),
UNIQUE(product, fiscalYear, fiscalMonth),
PRIMARY KEY(id)
);
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 (
id INT AUTO_INCREMENT PRIMARY KEY,
salesId INT,
beforeQuantity INT,
afterQuantity INT,
changedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
DELIMITER $$
CREATE TRIGGER after_sales_update
AFTER UPDATE
ON sales FOR EACH ROW
BEGIN
IF [Link] <> [Link] THEN
INSERT INTO SalesChanges(salesId,beforeQuantity, afterQuantity)
VALUES([Link], [Link], [Link]);
END IF;
END$$
DELIMITER ;
BEFORE DELETE
CREATE TABLE Salaries (
employeeNumber INT PRIMARY KEY,
validFrom DATE NOT NULL,
amount DEC(12 , 2 ) NOT NULL DEFAULT 0
);
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 (
id INT PRIMARY KEY AUTO_INCREMENT,
employeeNumber INT PRIMARY KEY,
validFrom DATE NOT NULL,
amount DEC(12 , 2 ) NOT NULL DEFAULT 0,
deletedAt TIMESTAMP DEFAULT NOW()
);
DELIMITER $$
CREATE TRIGGER before_salaries_delete
BEFORE DELETE
ON salaries FOR EACH ROW
BEGIN
INSERT INTO SalaryArchives(employeeNumber,validFrom,amount)
VALUES([Link],[Link],[Link]);
END$$
DELIMITER ;
AFTER DELETE
CREATE TABLE Salaries (
employeeNumber INT PRIMARY KEY,
salary DECIMAL(10,2) NOT NULL DEFAULT 0
);
INSERT INTO Salaries(employeeNumber,salary)
VALUES
(1002,5000),
(1056,7000),
(1076,8000);
CREATE TABLE SalaryBudgets(
total DECIMAL(15,2) NOT NULL
);
INSERT INTO SalaryBudgets(total)
SELECT SUM(salary)
FROM Salaries;
CREATE TRIGGER after_salaries_delete
AFTER DELETE
ON Salaries FOR EACH ROW
UPDATE SalaryBudgets
SET total = total - [Link];
