Simple demo of using MySQL triggers

mysql_trigger_demo.sql

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 ('ness@storyblocks.com');

INSERT INTO users (email, plan_id)
VALUES ('paula@storyblocks.com', 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 = 'jeff@storyblocks.com', 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 ('poo@storyblocks.com', 3);

UPDATE users SET email = 'pokey@storyblocks.com' WHERE email = 'poo@storyblocks.com';

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;