24강: 트리거

CREATE TRIGGER trigger_name
    BEFORE | AFTER | INSTEAD OF
    INSERT | UPDATE | DELETE
    ON table_name
    [WHEN condition]
BEGIN
    -- SQL statements;
END;

DELIMITER //
CREATE TRIGGER trigger_name
    BEFORE | AFTER
    INSERT | UPDATE | DELETE
    ON table_name
    FOR EACH ROW
BEGIN
    -- SQL statements;
END //
DELIMITER ;

-- Step 1: Create a trigger function
CREATE OR REPLACE FUNCTION trigger_function_name()
RETURNS TRIGGER AS $$
BEGIN
    -- SQL statements;
    RETURN NEW;  -- or RETURN OLD for DELETE triggers
END;
$$ LANGUAGE plpgsql;

-- Step 2: Attach the function to a trigger
CREATE TRIGGER trigger_name
    BEFORE | AFTER
    INSERT | UPDATE | DELETE
    ON table_name
    FOR EACH ROW
    EXECUTE FUNCTION trigger_function_name();

-- 모든 트리거 목록
SELECT name, tbl_name, sql
FROM sqlite_master
WHERE type = 'trigger'
ORDER BY name;

-- 모든 트리거 목록
SELECT TRIGGER_NAME, EVENT_OBJECT_TABLE, ACTION_STATEMENT
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA = DATABASE()
ORDER BY TRIGGER_NAME;

-- 모든 트리거 목록
SELECT tgname, relname, pg_get_triggerdef(t.oid)
FROM pg_trigger t
JOIN pg_class c ON t.tgrelid = c.oid
WHERE NOT t.tgisinternal
ORDER BY tgname;

-- 특정 트리거의 전체 SQL 확인
SELECT sql
FROM sqlite_master
WHERE type = 'trigger'
  AND name = 'trg_adjust_stock_on_order';

-- 특정 트리거의 전체 SQL 확인
SHOW CREATE TRIGGER trg_adjust_stock_on_order;

-- 특정 트리거의 전체 SQL 확인
SELECT pg_get_triggerdef(oid)
FROM pg_trigger
WHERE tgname = 'trg_adjust_stock_on_order';

-- 포인트 트리거 확인
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';

-- Audit table for price changes
CREATE TABLE IF NOT EXISTS price_change_log (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    product_id  INTEGER,
    old_price   REAL,
    new_price   REAL,
    changed_at  TEXT DEFAULT (datetime('now'))
);

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

-- Audit table for price changes
CREATE TABLE IF NOT EXISTS price_change_log (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    product_id  INT,
    old_price   DECIMAL(10,2),
    new_price   DECIMAL(10,2),
    changed_at  DATETIME DEFAULT NOW()
);

-- Trigger
DELIMITER //
CREATE TRIGGER trg_log_price_change
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    IF 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 ;

-- Audit table for price changes
CREATE TABLE IF NOT EXISTS price_change_log (
    id          SERIAL PRIMARY KEY,
    product_id  INTEGER,
    old_price   NUMERIC(10,2),
    new_price   NUMERIC(10,2),
    changed_at  TIMESTAMP DEFAULT NOW()
);

-- Trigger function
CREATE OR REPLACE FUNCTION fn_log_price_change()
RETURNS TRIGGER AS $$
BEGIN
    IF 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;

-- Trigger
CREATE TRIGGER trg_log_price_change
AFTER UPDATE OF price ON products
FOR EACH ROW
EXECUTE FUNCTION fn_log_price_change();