Debian PostgreSQL觸發器使用場景介紹

在Debian系統上使用PostgreSQL時，觸發器（Trigger）是一種非常有用的數據庫對象，它可以在特定的數據庫事件（如INSERT、UPDATE或DELETE操作）發生時自動執行預定義的代碼。以下是一些常見的PostgreSQL觸發器使用場景：

1. 數據驗證

場景描述：在插入或更新數據之前，確保數據的完整性和一致性。 示例：

CREATE OR REPLACE FUNCTION validate_data()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.age < 0 THEN
        RAISE EXCEPTION 'Age cannot be negative';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER before_insert_user
BEFORE INSERT ON users
FOR EACH ROW EXECUTE FUNCTION validate_data();

2. 自動更新相關字段

場景描述：當一個表中的數據發生變化時，自動更新另一個表中的相關字段。 示例：

CREATE OR REPLACE FUNCTION update_related_table()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE orders
    SET total_amount = total_amount + NEW.amount
    WHERE customer_id = NEW.customer_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER after_insert_order
AFTER INSERT ON orders
FOR EACH ROW EXECUTE FUNCTION update_related_table();

3. 日志記錄

場景描述：記錄對表的修改歷史，包括誰在什么時候做了什么修改。 示例：

CREATE OR REPLACE FUNCTION log_changes()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (table_name, action, user_name, timestamp)
    VALUES (TG_TABLE_NAME, TG_OP, session_user, NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_log_changes
AFTER INSERT OR UPDATE OR DELETE ON your_table
FOR EACH ROW EXECUTE FUNCTION log_changes();

4. 復雜業務邏輯

場景描述：在數據操作前后執行復雜的業務邏輯。 示例：

CREATE OR REPLACE FUNCTION complex_business_logic()
RETURNS TRIGGER AS $$
DECLARE
    v_count INT;
BEGIN
    SELECT COUNT(*) INTO v_count FROM related_table WHERE id = NEW.related_id;
    IF v_count > 10 THEN
        RAISE EXCEPTION 'Too many related records';
    END IF;
    -- 其他復雜邏輯
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER before_insert_complex
BEFORE INSERT ON your_table
FOR EACH ROW EXECUTE FUNCTION complex_business_logic();

5. 安全控制

場景描述：限制某些用戶只能執行特定的操作。 示例：

CREATE OR REPLACE FUNCTION check_user_permissions()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.user_id <> current_user_id THEN
        RAISE EXCEPTION 'Unauthorized access';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER before_update_sensitive_data
BEFORE UPDATE ON sensitive_data
FOR EACH ROW EXECUTE FUNCTION check_user_permissions();

6. 數據同步

場景描述：在多個表之間同步數據。 示例：

CREATE OR REPLACE FUNCTION sync_data()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE table_b
    SET column_b = NEW.column_a
    WHERE table_b.id = NEW.id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER after_insert_table_a
AFTER INSERT ON table_a
FOR EACH ROW EXECUTE FUNCTION sync_data();

注意事項

• 觸發器會增加數據庫的復雜性，應謹慎使用。
• 觸發器的性能可能會影響數據庫的整體性能，特別是在高并發環境下。
• 確保觸發器的邏輯簡單明了，避免嵌套過深和復雜的循環。

通過合理使用觸發器，可以大大提高數據庫的自動化程度和數據管理的效率。