Lesson 24: Triggers

SELECT sql
FROM sqlite_master
WHERE type = 'trigger'
  AND name = 'trg_earn_points_on_order';

SHOW CREATE TRIGGER trg_earn_points_on_order;

SELECT pg_get_triggerdef(oid)
FROM pg_trigger
WHERE tgname = 'trg_earn_points_on_order';

CREATE TRIGGER IF NOT EXISTS trg_log_expensive_price_change
AFTER UPDATE OF price ON products
WHEN NEW.price >= 1000000 AND OLD.price <> NEW.price
BEGIN
    INSERT INTO price_change_log (product_id, old_price, new_price)
    VALUES (NEW.id, OLD.price, NEW.price);
END;

DELIMITER //
CREATE TRIGGER trg_log_expensive_price_change
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    IF NEW.price >= 1000000 AND OLD.price <> NEW.price THEN
        INSERT INTO price_change_log (product_id, old_price, new_price)
        VALUES (NEW.id, OLD.price, NEW.price);
    END IF;
END //
DELIMITER ;

CREATE OR REPLACE FUNCTION fn_log_expensive_price_change()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.price >= 1000000 AND OLD.price <> NEW.price THEN
        INSERT INTO price_change_log (product_id, old_price, new_price)
        VALUES (NEW.id, OLD.price, NEW.price);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_log_expensive_price_change
AFTER UPDATE OF price ON products
FOR EACH ROW
EXECUTE FUNCTION fn_log_expensive_price_change();

CREATE TRIGGER IF NOT EXISTS trg_review_created_at
AFTER INSERT ON reviews
WHEN NEW.created_at IS NULL
BEGIN
    UPDATE reviews
    SET created_at = datetime('now')
    WHERE id = NEW.id;
END;

DELIMITER //
CREATE TRIGGER trg_review_created_at
BEFORE INSERT ON reviews
FOR EACH ROW
BEGIN
    IF NEW.created_at IS NULL THEN
        SET NEW.created_at = NOW();
    END IF;
END //
DELIMITER ;

CREATE OR REPLACE FUNCTION fn_review_created_at()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.created_at IS NULL THEN
        NEW.created_at := NOW();
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_review_created_at
BEFORE INSERT ON reviews
FOR EACH ROW
EXECUTE FUNCTION fn_review_created_at();

SELECT
    name,
    tbl_name,
    CASE
        WHEN sql LIKE '%AFTER INSERT%'  THEN 'AFTER INSERT'
        WHEN sql LIKE '%AFTER UPDATE%'  THEN 'AFTER UPDATE'
        WHEN sql LIKE '%AFTER DELETE%'  THEN 'AFTER DELETE'
        WHEN sql LIKE '%BEFORE INSERT%' THEN 'BEFORE INSERT'
        WHEN sql LIKE '%BEFORE UPDATE%' THEN 'BEFORE UPDATE'
        WHEN sql LIKE '%BEFORE DELETE%' THEN 'BEFORE DELETE'
    END AS fires_on
FROM sqlite_master
WHERE type = 'trigger'
ORDER BY name;

CREATE TRIGGER IF NOT EXISTS trg_prevent_staff_delete
BEFORE DELETE ON staff
WHEN (SELECT COUNT(*) FROM orders WHERE staff_id = OLD.id AND status NOT IN ('delivered', 'cancelled')) > 0
BEGIN
    SELECT RAISE(ABORT, 'Cannot delete staff with active orders.');
END;

DELIMITER //
CREATE TRIGGER trg_prevent_staff_delete
BEFORE DELETE ON staff
FOR EACH ROW
BEGIN
    DECLARE active_orders INT;
    SELECT COUNT(*) INTO active_orders
    FROM orders
    WHERE staff_id = OLD.id
      AND status NOT IN ('delivered', 'cancelled');
    IF active_orders > 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Cannot delete staff with active orders.';
    END IF;
END //
DELIMITER ;

CREATE OR REPLACE FUNCTION fn_prevent_staff_delete()
RETURNS TRIGGER AS $$
BEGIN
    IF (SELECT COUNT(*) FROM orders WHERE staff_id = OLD.id AND status NOT IN ('delivered', 'cancelled')) > 0 THEN
        RAISE EXCEPTION 'Cannot delete staff with active orders.';
    END IF;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_prevent_staff_delete
BEFORE DELETE ON staff
FOR EACH ROW
EXECUTE FUNCTION fn_prevent_staff_delete();

CREATE TABLE IF NOT EXISTS grade_change_log (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_id INTEGER,
    old_grade   TEXT,
    new_grade   TEXT,
    changed_at  TEXT DEFAULT (datetime('now'))
);

CREATE TRIGGER IF NOT EXISTS trg_log_grade_change
AFTER UPDATE OF grade ON customers
WHEN OLD.grade <> NEW.grade
BEGIN
    INSERT INTO grade_change_log (customer_id, old_grade, new_grade)
    VALUES (NEW.id, OLD.grade, NEW.grade);
END;

CREATE TABLE IF NOT EXISTS grade_change_log (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    old_grade   VARCHAR(20),
    new_grade   VARCHAR(20),
    changed_at  DATETIME DEFAULT NOW()
);

DELIMITER //
CREATE TRIGGER trg_log_grade_change
AFTER UPDATE ON customers
FOR EACH ROW
BEGIN
    IF OLD.grade <> NEW.grade THEN
        INSERT INTO grade_change_log (customer_id, old_grade, new_grade)
        VALUES (NEW.id, OLD.grade, NEW.grade);
    END IF;
END //
DELIMITER ;

CREATE TABLE IF NOT EXISTS grade_change_log (
    id          SERIAL PRIMARY KEY,
    customer_id INTEGER,
    old_grade   VARCHAR(20),
    new_grade   VARCHAR(20),
    changed_at  TIMESTAMP DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION fn_log_grade_change()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.grade <> NEW.grade THEN
        INSERT INTO grade_change_log (customer_id, old_grade, new_grade)
        VALUES (NEW.id, OLD.grade, NEW.grade);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_log_grade_change
AFTER UPDATE OF grade ON customers
FOR EACH ROW
EXECUTE FUNCTION fn_log_grade_change();

DROP TRIGGER IF EXISTS trg_review_created_at;
DROP TRIGGER IF EXISTS trg_log_grade_change;
DROP TRIGGER IF EXISTS trg_prevent_staff_delete;
DROP TRIGGER IF EXISTS trg_log_expensive_price_change;
DROP TABLE IF EXISTS grade_change_log;

DROP TRIGGER IF EXISTS trg_review_created_at;
DROP TRIGGER IF EXISTS trg_log_grade_change;
DROP TRIGGER IF EXISTS trg_prevent_staff_delete;
DROP TRIGGER IF EXISTS trg_log_expensive_price_change;
DROP TABLE IF EXISTS grade_change_log;

DROP TRIGGER IF EXISTS trg_review_created_at ON reviews;
DROP TRIGGER IF EXISTS trg_log_grade_change ON customers;
DROP TRIGGER IF EXISTS trg_prevent_staff_delete ON staff;
DROP TRIGGER IF EXISTS trg_log_expensive_price_change ON products;
DROP FUNCTION IF EXISTS fn_review_created_at();
DROP FUNCTION IF EXISTS fn_log_grade_change();
DROP FUNCTION IF EXISTS fn_prevent_staff_delete();
DROP FUNCTION IF EXISTS fn_log_expensive_price_change();
DROP TABLE IF EXISTS grade_change_log;

-- Step 1: Check trigger definition
SELECT sql
FROM sqlite_master
WHERE type = 'trigger'
  AND name = 'trg_restore_stock_on_cancel';

-- Step 2: Check cancelled orders and their products
SELECT o.id AS order_id, oi.product_id, oi.quantity, p.stock_qty
FROM orders AS o
INNER JOIN order_items AS oi ON oi.order_id = o.id
INNER JOIN products    AS p  ON p.id = oi.product_id
WHERE o.status = 'cancelled'
LIMIT 5;
-- stock_qty should already reflect the restored quantity

-- Step 1: Check trigger definition
SHOW CREATE TRIGGER trg_restore_stock_on_cancel;

-- Step 2: Check cancelled orders and their products
SELECT o.id AS order_id, oi.product_id, oi.quantity, p.stock_qty
FROM orders AS o
INNER JOIN order_items AS oi ON oi.order_id = o.id
INNER JOIN products    AS p  ON p.id = oi.product_id
WHERE o.status = 'cancelled'
LIMIT 5;
-- stock_qty should already reflect the restored quantity

-- Step 1: Check trigger definition
SELECT pg_get_triggerdef(oid)
FROM pg_trigger
WHERE tgname = 'trg_restore_stock_on_cancel';

-- Step 2: Check cancelled orders and their products
SELECT o.id AS order_id, oi.product_id, oi.quantity, p.stock_qty
FROM orders AS o
INNER JOIN order_items AS oi ON oi.order_id = o.id
INNER JOIN products    AS p  ON p.id = oi.product_id
WHERE o.status = 'cancelled'
LIMIT 5;
-- stock_qty should already reflect the restored quantity

CREATE TABLE IF NOT EXISTS high_value_orders_log (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    order_id     INTEGER,
    total_amount REAL,
    logged_at    TEXT DEFAULT (datetime('now'))
);

CREATE TRIGGER IF NOT EXISTS trg_log_high_value_order
AFTER INSERT ON orders
WHEN NEW.total_amount >= 5000000
BEGIN
    INSERT INTO high_value_orders_log (order_id, total_amount)
    VALUES (NEW.id, NEW.total_amount);
END;

CREATE TABLE IF NOT EXISTS high_value_orders_log (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    order_id     INT,
    total_amount DECIMAL(12,2),
    logged_at    DATETIME DEFAULT NOW()
);

DELIMITER //
CREATE TRIGGER trg_log_high_value_order
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    IF NEW.total_amount >= 5000000 THEN
        INSERT INTO high_value_orders_log (order_id, total_amount)
        VALUES (NEW.id, NEW.total_amount);
    END IF;
END //
DELIMITER ;

CREATE TABLE IF NOT EXISTS high_value_orders_log (
    id           SERIAL PRIMARY KEY,
    order_id     INTEGER,
    total_amount NUMERIC(12,2),
    logged_at    TIMESTAMP DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION fn_log_high_value_order()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.total_amount >= 5000000 THEN
        INSERT INTO high_value_orders_log (order_id, total_amount)
        VALUES (NEW.id, NEW.total_amount);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_log_high_value_order
AFTER INSERT ON orders
FOR EACH ROW
EXECUTE FUNCTION fn_log_high_value_order();

-- Drop triggers created in exercises
DROP TRIGGER IF EXISTS trg_log_high_value_order;
DROP TRIGGER IF EXISTS trg_review_created_at;
DROP TRIGGER IF EXISTS trg_log_grade_change;
DROP TRIGGER IF EXISTS trg_prevent_staff_delete;
DROP TRIGGER IF EXISTS trg_log_expensive_price_change;

-- Drop tables created in exercises
DROP TABLE IF EXISTS high_value_orders_log;
DROP TABLE IF EXISTS grade_change_log;

-- Verify only pre-installed triggers remain
SELECT name, tbl_name FROM sqlite_master
WHERE type = 'trigger'
ORDER BY name;

DROP TRIGGER IF EXISTS trg_log_high_value_order;
DROP TRIGGER IF EXISTS trg_review_created_at;
DROP TRIGGER IF EXISTS trg_log_grade_change;
DROP TRIGGER IF EXISTS trg_prevent_staff_delete;
DROP TRIGGER IF EXISTS trg_log_expensive_price_change;
DROP TABLE IF EXISTS high_value_orders_log;
DROP TABLE IF EXISTS grade_change_log;

SELECT TRIGGER_NAME, EVENT_OBJECT_TABLE
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA = DATABASE()
ORDER BY TRIGGER_NAME;

DROP TRIGGER IF EXISTS trg_log_high_value_order ON orders;
DROP TRIGGER IF EXISTS trg_review_created_at ON reviews;
DROP TRIGGER IF EXISTS trg_log_grade_change ON customers;
DROP TRIGGER IF EXISTS trg_prevent_staff_delete ON staff;
DROP TRIGGER IF EXISTS trg_log_expensive_price_change ON products;
DROP FUNCTION IF EXISTS fn_log_high_value_order();
DROP FUNCTION IF EXISTS fn_review_created_at();
DROP FUNCTION IF EXISTS fn_log_grade_change();
DROP FUNCTION IF EXISTS fn_prevent_staff_delete();
DROP FUNCTION IF EXISTS fn_log_expensive_price_change();
DROP TABLE IF EXISTS high_value_orders_log;
DROP TABLE IF EXISTS grade_change_log;

SELECT tgname, relname
FROM pg_trigger t
JOIN pg_class c ON t.tgrelid = c.oid
WHERE NOT t.tgisinternal
ORDER BY tgname;