事件觸發就是當發生了什麼事情就怎樣怎樣，例如當媽媽回家時，電視機就要關起來，在資料庫當中就是每當資料庫執行了 INSERT、UPDATE、DELETE 的 SQL 語句時就要幹嘛幹嘛，INSERT、UPDATE、DELETE這些動作被稱為事件，不過具體的做法是怎麼做呢?

1. 首先要借助 Day 26 自訂函數的技巧，我們要創建一個自訂函數，當事件發生的時候，資料庫就去執行這個函數，這個函數是觸發器(TRIGGER)專用的函數，也稱為觸發器函數。
2. 綁定這個觸發器到資料表上，一但發生觸發事件就會自動執行觸發器。

有些資料庫的Trigger語法是直接把要執行的內容直接寫在Trigger，相比之下，PostgreSQL的語法是比較麻煩一些。(熟悉函數語法之後就是麻煩一點點而已)

創建範例資料表

CREATE TABLE employees1 ( 
    id SERIAL PRIMARY KEY, 
    first_name VARCHAR(40) NOT NULL, 
    last_name  VARCHAR(40) NOT NULL,
		dept VARCHAR(200) NOT NULL,
		salary INT,
		bonus INT,
		total_salary INT
);

創建觸發器函數

觸發器函數與一般函數不同的地方是它不能使用任何參數，RETURN的類型是TIGGER。
我們第一個觸發器是要自動計算薪水+獎金=總薪水，然後寫入資料庫欄位中。

CREATE OR REPLACE FUNCTION fnCalcTotalSalary()
RETURNS TRIGGER AS 
$$
DECLARE 
    total INT;
BEGIN
    total = NEW.salary + NEW.bonus;
    NEW.total_salary = total;
    RETURN NEW;
END;
$$ 
LANGUAGE 'plpgsql';

創建觸發器

創建了觸發器函數之後，接著我們要把觸發器綁在資料表，然後要告訴資料庫那些動作要觸發，這個例子是新資料跟更新資料的時候，我們都要去計算總薪水。

CREATE TRIGGER trCalcTotalSalary
BEFORE INSERT OR UPDATE
ON employees1
FOR EACH ROW
EXECUTE PROCEDURE fnCalcTotalSalary();

創建範例資料

接著插入一筆資料來看看結果如何

INSERT INTO employees1 (first_name, last_name, dept, salary, bonus)
VALUES ('大空','王','測試部','42000','4500');

查看資料

total_salary被正確寫入，我們完成了第一個觸發器的運用了!

id|first_name|last_name|dept|salary|bonus|total_salary|
--+----------+---------+----+------+-----+------------+
 1|大空        |王        |測試部 | 42000| 4500|       46500|

創建範例資料表2

不只不只，我們繼續來練習第二個範例，我們要紀錄員工的薪水變化，因此我們再創建一個表個來記錄薪水。

CREATE TABLE employee_salary_audits( 
    id SERIAL PRIMARY KEY, 
    employee_id INT,    
    total_salary INT,
		changed_on TIMESTAMP
);

創建觸發器函數2

如果薪水不一樣了，就寫入這張紀錄表。

CREATE OR REPLACE FUNCTION fnLogEmployeeSalary() 
RETURNS TRIGGER AS 
$$ 
BEGIN 
	IF NEW.total_salary <> OLD.total_salary THEN 
	INSERT INTO employee_salary_audits(employee_id, total_salary, changed_on) 
	VALUES(OLD.id, OLD.total_salary,now()); 
	END IF;	
	RETURN NEW; 
END; 
$$
LANGUAGE 'plpgsql';

創建觸發器2

一樣綁定觸發器，這次只用於更新。

CREATE TRIGGER trLogEmployeeSalary
BEFORE UPDATE
ON employees1
FOR EACH ROW
EXECUTE PROCEDURE fnLogEmployeeSalary();

更新範例資料2

一但資料更新了，就會被寫入紀錄。

UPDATE employees1
SET salary='44000'
WHERE id='1';

id|employee_id|total_salary|changed_on             |
--+-----------+------------+-----------------------+
 1|          1|       46500|2022-10-16 18:30:18.300|

創建範例資料表3

最後我們把使用情況擴大到整張表都欄位都要記錄，我們再用另一張表來記錄。

CREATE TABLE employee_audits ( 
    id SERIAL PRIMARY KEY, 
		action VARCHAR(200),
		employee_id INT,
    first_name VARCHAR(40), 
    last_name  VARCHAR(40),
		dept VARCHAR(200),
		salary INT,
		bonus INT,
		total_salary INT,
		changed_on TIMESTAMP
);

創建觸發器函數3

這邊用到PostgreSQL所提供的語法，TG_OP指的就是DELETE、UPDATE、INSERT這些操作，我們可以依據不同的操作寫入不同的資訊。

CREATE OR REPLACE FUNCTION fnLogEmployee()
RETURNS TRIGGER AS 
$$
    BEGIN
        IF(TG_OP='DELETE') THEN
            INSERT INTO employee_audits 
            (action, 
            employee_id, 
            first_name, 
            last_name, 
            dept, 
            salary, 
            bonus, 
            total_salary, 
            changed_on)
            VALUES 
            ('DELETE', 
            OLD.id, 
            OLD.first_name, 
            OLD.last_name, 
            OLD.dept, 
            OLD.salary, 
            OLD.bonus, 
            OLD.total_salary,
            NOW());
            RETURN OLD;
         ELSIF(TG_OP='UPDATE') THEN
            INSERT INTO employee_audits 
            (action, 
            employee_id, 
            first_name, 
            last_name, 
            dept, 
            salary, 
            bonus, 
            total_salary, 
            changed_on)
            VALUES 
            ('UPDATE', 
            NEW.id, 
            NEW.first_name, 
            NEW.last_name, 
            NEW.dept, 
            NEW.salary, 
            NEW.bonus, 
            NEW.total_salary,
            NOW());
            RETURN NEW;
         ELSIF(TG_OP='INSERT') THEN
            INSERT INTO employee_audits 
            (action, 
            employee_id, 
            first_name, 
            last_name, 
            dept, 
            salary, 
            bonus, 
            total_salary, 
            changed_on)
            VALUES 
            ('INSERT', 
            NEW.id, 
            NEW.first_name, 
            NEW.last_name, 
            NEW.dept, 
            NEW.salary, 
            NEW.bonus, 
            NEW.total_salary,
            NOW());
            RETURN NEW;
         END IF;
    END;
$$
LANGUAGE 'plpgsql';

創建觸發器3

最後就完成了操作紀錄，可以清楚看出每一筆資料的變化。

CREATE TRIGGER trLogEmployee
   AFTER INSERT OR UPDATE OR DELETE ON employees1
   FOR EACH ROW EXECUTE PROCEDURE fnLogEmployee();

id|action|employee_id|first_name|last_name|dept|salary|bonus|total_salary|changed_on             |
--+------+-----------+----------+---------+----+------+-----+------------+-----------------------+
 1|DELETE|          1|大空        |王        |測試部 | 44000| 4500|       48500|2022-10-16 19:06:09.815|
 2|INSERT|          2|小明        |陳        |研發部 | 35800|    0|       35800|2022-10-16 19:09:01.970|
 3|UPDATE|          2|小明        |陳        |研發部 | 36600|    0|       36600|2022-10-16 19:13:09.434|