Demo Triggers Database [Archived]

demo.triggers.mysql.archived.sql

DROP TRIGGER IF EXISTS db_enrollments.on_insert_transaction_creates_event;
DROP TRIGGER IF EXISTS db_enrollments.on_udate_transaction_record_event_transitions;
DROP TABLE IF EXISTS events;
DROP TABLE IF EXISTS transacts;

CREATE TABLE transacts (
    transact_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    transact_status ENUM("CREATED", "IN_MEM", "RELEASE_MEM", "PROC_STALL", "OK", "FAILED") DEFAULT "CREATED",
    transact_data TEXT NULL,
    transact_deleted TINYINT NOT NULL DEFAULT 0, -- soft delete (retain transact status information to easy rollback)
    transact_created_at DATETIME DEFAULT CURRENT_TIMESTAMP(),
    transact_deleted_at DATETIME NULL,
    INDEX idx_transacts(transact_id, transact_status)
);

CREATE TABLE events (
    event_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    event_transact_id INT NOT NULL,
    event_date DATETIME DEFAULT CURRENT_TIMESTAMP(),
    event_desc TEXT ,
    FOREIGN KEY (event_transact_id) REFERENCES transacts ( transact_id )
 );

CREATE TRIGGER on_insert_transaction_creates_event
AFTER INSERT ON transacts
FOR EACH ROW
BEGIN
  INSERT INTO events (event_transact_id, event_desc)
    VALUES (NEW.transact_id,  CONCAT('New Transaction Initial Status: ', NEW.transact_status));
END;

CREATE TRIGGER on_udate_transaction_record_event_transitions
AFTER UPDATE ON transacts
FOR EACH ROW
BEGIN
  IF OLD.transact_status <> NEW.transact_status THEN
    INSERT INTO events (event_transact_id, event_desc)
      VALUES (NEW.transact_id,  CONCAT('Transaction Transitioned from:', OLD.transact_status, ' to: ', NEW.transact_status));

    IF NEW.transact_status = 'OK' THEN
      INSERT INTO events (event_transact_id, event_desc)
        VALUES (NEW.transact_id,  'Transaction Completed');
     END IF;
  END IF;

  IF OLD.transact_deleted <> NEW.transact_deleted  THEN
    IF NEW.transact_deleted = 1 THEN
      INSERT INTO events (event_transact_id, event_desc)
        VALUES (NEW.transact_id,  'Transaction Deleted');
     ELSE
       INSERT INTO events (event_transact_id, event_desc)
        VALUES (NEW.transact_id,  'Transaction Restored');
     END IF;
  END IF;
END;
INSERT INTO transacts (transact_data) values (':transact_data');
UPDATE transacts SET transact_status = 'IN_MEM' WHERE transact_id = last_insert_id();
UPDATE transacts SET transact_status = 'RELEASE_MEM' WHERE transact_id = last_insert_id();
UPDATE transacts SET transact_status = 'PROC_STALL' WHERE transact_id = last_insert_id();
UPDATE transacts SET transact_status = 'RELEASE_MEM' WHERE transact_id = last_insert_id();
UPDATE transacts SET transact_status = 'OK' WHERE transact_id = last_insert_id();
UPDATE transacts SET transact_deleted = 1 WHERE transact_id = last_insert_id();
UPDATE transacts SET transact_deleted = 0 WHERE transact_id = last_insert_id();
SELECT * FROM events WHERE event_transact_id = last_insert_id();