PostgreSQL中如何使用觸發器

在 PostgreSQL 中，觸發器（Trigger）是一種特殊的函數，它在特定的數據庫事件（如 INSERT、UPDATE 或 DELETE）發生時自動執行。以下是如何在 PostgreSQL 中創建和使用觸發器的步驟：

1. 創建觸發器函數

首先，你需要創建一個觸發器函數，該函數將在觸發器被激活時執行。

CREATE OR REPLACE FUNCTION trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    -- 在這里編寫觸發器邏輯
    -- 例如，記錄日志、更新其他表等
    RETURN NEW; -- 對于 INSERT 和 UPDATE 觸發器，返回 NEW；對于 DELETE 觸發器，返回 OLD
END;
$$ LANGUAGE plpgsql;

2. 創建觸發器

接下來，你需要創建一個觸發器，并將其與特定的表和事件關聯起來。

CREATE TRIGGER trigger_name
AFTER INSERT OR UPDATE OR DELETE ON table_name
FOR EACH ROW
EXECUTE FUNCTION trigger_function();

• trigger_name：觸發器的名稱。
• AFTER INSERT OR UPDATE OR DELETE：指定觸發器在 INSERT、UPDATE 或 DELETE 事件之后執行。
• ON table_name：指定觸發器作用的表。
• FOR EACH ROW：指定觸發器對每一行數據都執行一次。
• EXECUTE FUNCTION trigger_function()：指定觸發器執行的函數。

示例

假設我們有一個名為 employees 的表，我們希望在每次插入或更新員工記錄時，自動更新一個日志表 employee_logs。

創建日志表

CREATE TABLE employee_logs (
    log_id SERIAL PRIMARY KEY,
    employee_id INT,
    action VARCHAR(10),
    log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

創建觸發器函數

CREATE OR REPLACE FUNCTION update_employee_log()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO employee_logs (employee_id, action) VALUES (NEW.id, 'INSERT');
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO employee_logs (employee_id, action) VALUES (NEW.id, 'UPDATE');
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO employee_logs (employee_id, action) VALUES (OLD.id, 'DELETE');
    END IF;
    RETURN NEW; -- 對于 INSERT 和 UPDATE 觸發器，返回 NEW；對于 DELETE 觸發器，返回 OLD
END;
$$ LANGUAGE plpgsql;

創建觸發器

CREATE TRIGGER employee_log_trigger
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
EXECUTE FUNCTION update_employee_log();

測試觸發器

現在，你可以測試觸發器是否按預期工作。

-- 插入一條記錄
INSERT INTO employees (name, position) VALUES ('John Doe', 'Developer');

-- 更新一條記錄
UPDATE employees SET position = 'Senior Developer' WHERE name = 'John Doe';

-- 刪除一條記錄
DELETE FROM employees WHERE name = 'John Doe';

每次執行這些操作后，employee_logs 表都會相應地更新。

通過這種方式，你可以在 PostgreSQL 中創建和使用觸發器來自動化各種數據庫操作。