觸發(fā)器其實開發(fā)中基本沒用到，到是運維中用到不少。觸發(fā)器是數(shù)據(jù)庫中的一種特殊類型的存儲過程，它會在特定的數(shù)據(jù)庫事件發(fā)生時自動執(zhí)行。本文將深入探討 SQLite 觸發(fā)器的各個方面，包括其概念、語法、類型、使用場景以及最佳實踐。

什么是觸發(fā)器？

觸發(fā)器是一種數(shù)據(jù)庫對象，它與表關(guān)聯(lián)并在指定的數(shù)據(jù)庫事件（如插入、更新或刪除）發(fā)生時自動執(zhí)行。觸發(fā)器可以在這些事件之前或之后執(zhí)行，用于實現(xiàn)復(fù)雜的業(yè)務(wù)規(guī)則、維護(hù)數(shù)據(jù)完整性、記錄變更歷史等。

SQLite 觸發(fā)器的類型

SQLite 支持以下類型的觸發(fā)器：

1. BEFORE 觸發(fā)器：在指定事件之前執(zhí)行。

2. AFTER 觸發(fā)器：在指定事件之后執(zhí)行。

3. INSERT 觸發(fā)器：在插入操作時觸發(fā)。

4. UPDATE 觸發(fā)器：在更新操作時觸發(fā)。

5. DELETE 觸發(fā)器：在刪除操作時觸發(fā)。

   
   

創(chuàng)建測試數(shù)據(jù)

-- 1. 創(chuàng)建用戶表CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT,username TEXT NOT NULL,age INTEGER NOT NULL,salary DECIMAL(10,2),created_at DATETIME DEFAULT CURRENT_TIMESTAMP,last_modified DATETIME DEFAULT CURRENT_TIMESTAMP);
-- 2. 創(chuàng)建用戶日志表CREATE TABLE user_logs (id INTEGER PRIMARY KEY AUTOINCREMENT,user_id INTEGER,action TEXT NOT NULL,action_date DATETIME DEFAULT CURRENT_TIMESTAMP,details TEXT,FOREIGN KEY (user_id) REFERENCES users(id));

創(chuàng)建觸發(fā)器

創(chuàng)建觸發(fā)器的基本語法如下：

CREATE TRIGGER trigger_name [BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON table_name[FOR EACH ROW][WHEN condition]BEGIN ? ?-- 觸發(fā)器邏輯END;

示例：創(chuàng)建觸發(fā)器：檢查年齡限制

CREATE TRIGGER check_minimum_ageBEFORE INSERT ON usersFOR EACH ROWWHEN NEW.age < 18BEGINSELECT RAISE(ABORT, '用戶年齡必須至少18歲');END;
INSERT INTO users (username, age, salary) VALUES ('張三', 25, 5000.00);INSERT INTO users (username, age, salary) VALUES ('李四', 30, 6000.00);INSERT INTO users (username, age, salary) VALUES ('王五', 35, 7000.00);
-- 測試年齡限制觸發(fā)器（會失?。?/span>INSERT INTO users (username, age, salary) VALUES ('小明', 16, 3000.00);

創(chuàng)建觸發(fā)器：記錄用戶插入日志

CREATE TRIGGER after_insert_userAFTER INSERT ON usersFOR EACH ROWBEGININSERT INTO user_logs (user_id, action, details)VALUES (NEW.id, 'INSERT', '新用戶創(chuàng)建: ' || NEW.username);END;

觸發(fā)器的執(zhí)行時機(jī)

觸發(fā)器可以在以下時機(jī)執(zhí)行：

1. BEFORE：在觸發(fā)事件之前執(zhí)行，可以用來驗證或修改要插入或更新的數(shù)據(jù)。

2. AFTER：在觸發(fā)事件之后執(zhí)行，通常用于記錄日志或執(zhí)行后續(xù)操作。

OLD 和 NEW 引用

在觸發(fā)器中，可以使用 OLD 和 NEW 引用來訪問受影響行的值：

• OLD：引用更新或刪除之前的行值。

• NEW：引用插入或更新之后的行值。

  
  

創(chuàng)建觸發(fā)器：限制工資增長

CREATE TRIGGER before_update_salaryBEFORE UPDATE OF salary ON usersFOR EACH ROWWHEN NEW.salary > OLD.salary * 1.2BEGINSELECT RAISE(ABORT, '工資增長不能超過20%');END;
-- 失敗更新（增長超過20%）UPDATE users SET salary = salary * 1.3 WHERE username = '李四';

?

觸發(fā)器中的條件執(zhí)行

創(chuàng)建觸發(fā)器：更新最后修改時間

CREATE TRIGGER update_modification_dateAFTER UPDATE ON usersFOR EACH ROWBEGINUPDATE usersSET last_modified = CURRENT_TIMESTAMPWHERE id = NEW.id;END;
--成功更新（增長20%以內(nèi)）UPDATE?users?SET?salary?=?salary?*?1.1?WHERE?username?=?'張三';

觸發(fā)器的應(yīng)用場景

1. 審計跟蹤：記錄數(shù)據(jù)變更歷史。

2. 業(yè)務(wù)規(guī)則實施：自動執(zhí)行復(fù)雜的業(yè)務(wù)邏輯。

3. 數(shù)據(jù)驗證：在數(shù)據(jù)插入或更新前進(jìn)行驗證。

4. 自動更新關(guān)聯(lián)數(shù)據(jù)：在一個表更新時自動更新相關(guān)表。

5. 計算衍生值：自動計算和更新統(tǒng)計數(shù)據(jù)。

   
   

修改和刪除觸發(fā)器

SQLite 不直接支持修改觸發(fā)器。要修改觸發(fā)器，需要先刪除再重新創(chuàng)建：

DROP TRIGGER IF EXISTS trigger_name;

刪除觸發(fā)器：

DROP TRIGGER IF EXISTS trigger_name;

觸發(fā)器的限制和注意事項

1. 觸發(fā)器不能調(diào)用包含事務(wù)控制的存儲過程。

2. 觸發(fā)器中不能使用 PRAGMA 語句。

3. 觸發(fā)器不能修改它們正在操作的表（避免無限循環(huán)）。

4. 虛擬表和臨時表不支持觸發(fā)器。

   
   

觸發(fā)器與性能

觸發(fā)器可能會影響數(shù)據(jù)庫性能：

1. 復(fù)雜的觸發(fā)器邏輯可能會降低插入、更新和刪除操作的速度。

2. 大量的觸發(fā)器可能會增加數(shù)據(jù)庫操作的整體延遲。

3. 觸發(fā)器中的子查詢或復(fù)雜計算可能會顯著影響性能。

   
   

優(yōu)化建議：

• 保持觸發(fā)器邏輯簡單。

• 避免在觸發(fā)器中執(zhí)行復(fù)雜的查詢。

• 考慮使用索引來優(yōu)化觸發(fā)器中的查詢。

  
  

結(jié)論

SQLite 觸發(fā)器是一個強(qiáng)大的功能，可以自動化數(shù)據(jù)庫操作，維護(hù)數(shù)據(jù)完整性，并實現(xiàn)復(fù)雜的業(yè)務(wù)規(guī)則。正確使用觸發(fā)器可以大大簡化應(yīng)用程序邏輯，提高數(shù)據(jù)一致性，并自動化許多數(shù)據(jù)庫維護(hù)任務(wù)。