觸發器其實開發中基本沒用到，到是運維中用到不少。觸發器是數據庫中的一種特殊類型的存儲過程，它會在特定的數據庫事件發生時自動執行。本文將深入探討 SQLite 觸發器的各個方面，包括其概念、語法、類型、使用場景以及最佳實踐。
什么是觸發器？
觸發器是一種數據庫對象，它與表關聯并在指定的數據庫事件（如插入、更新或刪除）發生時自動執行。觸發器可以在這些事件之前或之后執行，用于實現復雜的業務規則、維護數據完整性、記錄變更歷史等。
SQLite 觸發器的類型
SQLite 支持以下類型的觸發器：
BEFORE 觸發器：在指定事件之前執行。
AFTER 觸發器：在指定事件之后執行。
INSERT 觸發器：在插入操作時觸發。
UPDATE 觸發器：在更新操作時觸發。
DELETE 觸發器：在刪除操作時觸發。
創建測試數據
-- 1. 創建用戶表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. 創建用戶日志表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));
創建觸發器
創建觸發器的基本語法如下：
CREATE TRIGGER trigger_name [BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON table_name[FOR EACH ROW][WHEN condition]BEGIN ? ?-- 觸發器邏輯END;
示例：創建觸發器：檢查年齡限制
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);
-- 測試年齡限制觸發器（會失敗）INSERT INTO users (username, age, salary) VALUES ('小明', 16, 3000.00);
創建觸發器：記錄用戶插入日志
CREATE TRIGGER after_insert_userAFTER INSERT ON usersFOR EACH ROWBEGININSERT INTO user_logs (user_id, action, details)VALUES (NEW.id, 'INSERT', '新用戶創建: ' || NEW.username);END;
觸發器的執行時機
觸發器可以在以下時機執行：
BEFORE：在觸發事件之前執行，可以用來驗證或修改要插入或更新的數據。
AFTER：在觸發事件之后執行，通常用于記錄日志或執行后續操作。
OLD 和 NEW 引用
在觸發器中，可以使用 OLD 和 NEW 引用來訪問受影響行的值：
OLD：引用更新或刪除之前的行值。
NEW：引用插入或更新之后的行值。
創建觸發器：限制工資增長
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 = '李四';
?
觸發器中的條件執行
創建觸發器：更新最后修改時間
CREATE TRIGGER update_modification_dateAFTER UPDATE ON usersFOR EACH ROWBEGINUPDATE usersSET last_modified = CURRENT_TIMESTAMPWHERE id = NEW.id;END;
--成功更新（增長20%以內）UPDATE?users?SET?salary?=?salary?*?1.1?WHERE?username?=?'張三';
觸發器的應用場景
審計跟蹤：記錄數據變更歷史。
業務規則實施：自動執行復雜的業務邏輯。
數據驗證：在數據插入或更新前進行驗證。
自動更新關聯數據：在一個表更新時自動更新相關表。
計算衍生值：自動計算和更新統計數據。
修改和刪除觸發器
SQLite 不直接支持修改觸發器。要修改觸發器，需要先刪除再重新創建：
DROP TRIGGER IF EXISTS trigger_name;
刪除觸發器：
DROP TRIGGER IF EXISTS trigger_name;
觸發器的限制和注意事項
觸發器不能調用包含事務控制的存儲過程。
觸發器中不能使用 PRAGMA 語句。
觸發器不能修改它們正在操作的表（避免無限循環）。
虛擬表和臨時表不支持觸發器。
觸發器與性能
觸發器可能會影響數據庫性能：
復雜的觸發器邏輯可能會降低插入、更新和刪除操作的速度。
大量的觸發器可能會增加數據庫操作的整體延遲。
觸發器中的子查詢或復雜計算可能會顯著影響性能。
優化建議：
保持觸發器邏輯簡單。
避免在觸發器中執行復雜的查詢。
考慮使用索引來優化觸發器中的查詢。
結論
SQLite 觸發器是一個強大的功能，可以自動化數據庫操作，維護數據完整性，并實現復雜的業務規則。正確使用觸發器可以大大簡化應用程序邏輯，提高數據一致性，并自動化許多數據庫維護任務。
該文章在 2024/11/13 14:48:42 編輯過
400 186 1886
