Created
February 19, 2019 18:57
-
-
Save aarsilv/65c1205aba4397354738911bfd85023c to your computer and use it in GitHub Desktop.
Simple demo of using MySQL triggers
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
USE demo; | |
/* DEMO FOR TRACKING HISTORY */ | |
DROP TABLE IF EXISTS users; | |
DROP TABLE IF EXISTS user_history; | |
DROP PROCEDURE IF EXISTS append_user_history; | |
CREATE TABLE users ( | |
user_id int NOT NULL AUTO_INCREMENT PRIMARY KEY, | |
email varchar(255) NOT NULL UNIQUE, | |
plan_id int NOT NULL DEFAULT 1, | |
status enum('active','cancel') NOT NULL DEFAULT 'active', | |
date_added timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
date_updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP | |
); | |
CREATE TABLE user_history ( | |
user_id int NOT NULL, | |
email varchar(255) NOT NULL, | |
plan_id int NOT NULL, | |
status enum('active','cancel') NOT NULL, | |
date_added timestamp NOT NULL, | |
date_updated timestamp NOT NULL, | |
CONSTRAINT pk_user_history PRIMARY KEY (user_id, date_updated) | |
); | |
DELIMITER $$ | |
CREATE TRIGGER tr_users_insert_append_user_history AFTER INSERT ON users | |
FOR EACH ROW | |
BEGIN | |
INSERT INTO user_history(user_id, email, plan_id, status, date_added, date_updated) | |
VALUES (NEW.user_id, NEW.email, NEW.plan_id, NEW.status, NEW.date_added, NEW.date_updated); | |
END $$ | |
DELIMITER ; | |
SELECT * FROM users; | |
SELECT * FROM user_history; | |
INSERT INTO users (email) | |
VALUES ('[email protected]'); | |
INSERT INTO users (email, plan_id) | |
VALUES ('[email protected]', 2); | |
SELECT * FROM users; | |
SELECT * FROM user_history; | |
UPDATE users SET status = 'cancel' WHERE user_id = 1; | |
SELECT * FROM users; | |
SELECT * FROM user_history; | |
DELIMITER $$ | |
CREATE TRIGGER tr_users_update_append_user_history AFTER UPDATE ON users | |
FOR EACH ROW | |
BEGIN | |
INSERT INTO user_history(user_id, email, plan_id, status, date_added, date_updated) | |
VALUES (NEW.user_id, NEW.email, NEW.plan_id, NEW.status, NEW.date_added, NEW.date_updated); | |
END $$ | |
DELIMITER ; | |
UPDATE users SET email = '[email protected]', status = 'cancel' WHERE user_id = 2; | |
SELECT * FROM users; | |
SELECT * FROM user_history; | |
UPDATE users SET status = 'active' WHERE user_id IN (1,2); | |
SELECT * FROM users; | |
SELECT * FROM user_history; | |
DELIMITER $$ | |
CREATE PROCEDURE append_user_history(IN user_id int, IN email varchar(255), IN plan_id int, IN status enum('active','cancel'), IN date_added timestamp, IN date_updated timestamp) | |
BEGIN | |
INSERT INTO user_history(user_id, email, plan_id, status, date_added, date_updated) | |
VALUES (user_id, email, plan_id, status, date_added, date_updated); | |
END $$ | |
DELIMITER ; | |
DROP TRIGGER tr_users_insert_append_user_history; | |
DROP TRIGGER tr_users_update_append_user_history; | |
DELIMITER $$ | |
CREATE TRIGGER tr_users_insert_append_user_history AFTER INSERT ON users | |
FOR EACH ROW | |
BEGIN | |
CALL append_user_history(NEW.user_id, NEW.email, NEW.plan_id, NEW.status, NEW.date_added, NEW.date_updated); | |
END $$ | |
DELIMITER ; | |
DELIMITER $$ | |
CREATE TRIGGER tr_users_update_append_user_history AFTER UPDATE ON users | |
FOR EACH ROW | |
BEGIN | |
CALL append_user_history(NEW.user_id, NEW.email, NEW.plan_id, NEW.status, NEW.date_added, NEW.date_updated); | |
END $$ | |
DELIMITER ; | |
INSERT INTO users (email, plan_id) | |
VALUES ('[email protected]', 3); | |
UPDATE users SET email = '[email protected]' WHERE email = '[email protected]'; | |
SELECT * FROM users; | |
SELECT * FROM user_history; | |
SHOW TRIGGERS WHERE `table` = 'users'; | |
DROP TABLE users; | |
DROP TABLE user_history; | |
DROP PROCEDURE append_user_history; | |
/* DEMO FOR FORCING INTEGRITY */ | |
DROP TABLE IF EXISTS item_libraries; | |
DROP PROCEDURE IF EXISTS check_for_library_conflicts; | |
CREATE TABLE item_libraries ( | |
item_id int NOT NULL, | |
library_id int NOT NULL, | |
CONSTRAINT pk_stock_item_stock_libraries PRIMARY KEY (item_id, library_id) | |
); | |
DELIMITER $$ | |
CREATE PROCEDURE check_for_library_conflicts(IN item_id int, IN library_id int) | |
BEGIN | |
IF library_id IN (1, 2) | |
AND ( | |
SELECT count(*) | |
FROM item_libraries l | |
WHERE l.item_id = item_id | |
AND l.library_id IN (1, 2) | |
AND l.library_id <> library_id | |
) > 0 | |
THEN | |
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Items cannot be in both libraries 1 and 2'; | |
END IF; | |
END $$ | |
DELIMITER ; | |
DELIMITER $$ | |
CREATE TRIGGER tr_item_libraries_insert_check_for_library_conflicts BEFORE INSERT ON item_libraries | |
FOR EACH ROW | |
BEGIN | |
CALL check_for_library_conflicts(NEW.item_id, NEW.library_id); | |
END $$ | |
DELIMITER ; | |
DELIMITER $$ | |
CREATE TRIGGER tr_item_libraries_update_check_for_library_conflicts BEFORE UPDATE ON item_libraries | |
FOR EACH ROW | |
BEGIN | |
CALL check_for_library_conflicts(NEW.item_id, NEW.library_id); | |
END $$ | |
DELIMITER ; | |
INSERT INTO item_libraries (item_id, library_id) | |
VALUES (101,1), (102,1), (103,1), (201,2), (202,2), (203,2), (301,3), (302,3), (303,3); | |
SELECT * FROM item_libraries; | |
INSERT INTO item_libraries (item_id, library_id) | |
VALUES (101, 3); | |
SELECT * FROM item_libraries; | |
INSERT INTO item_libraries (item_id, library_id) | |
VALUES (101, 2); | |
INSERT INTO item_libraries (item_id, library_id) | |
VALUES (202, 1); | |
INSERT INTO item_libraries (item_id, library_id) | |
VALUES (102, 3), (202, 1), (202, 3); | |
SELECT * FROM item_libraries; | |
DROP TABLE item_libraries; | |
DROP PROCEDURE check_for_library_conflicts; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment