觸發(fā)器其實(shí)開發(fā)中基本沒用到，到是運(yùn)維中用到不少。觸發(fā)器是數(shù)據(jù)庫中的一種特殊類型的存儲過程，它會在特定的數(shù)據(jù)庫事件發(fā)生時自動執(zhí)行。本文將深入探討 SQLite 觸發(fā)器的各個方面，包括其概念、語法、類型、使用場景以及最佳實(shí)踐。
什么是觸發(fā)器？
觸發(fā)器是一種數(shù)據(jù)庫對象，它與表關(guān)聯(lián)并在指定的數(shù)據(jù)庫事件（如插入、更新或刪除）發(fā)生時自動執(zhí)行。觸發(fā)器可以在這些事件之前或之后執(zhí)行，用于實(shí)現(xiàn)復(fù)雜的業(yè)務(wù)規(guī)則、維護(hù)數(shù)據(jù)完整性、記錄變更歷史等。
SQLite 觸發(fā)器的類型
SQLite 支持以下類型的觸發(fā)器：
BEFORE 觸發(fā)器：在指定事件之前執(zhí)行。
AFTER 觸發(fā)器：在指定事件之后執(zhí)行。
INSERT 觸發(fā)器：在插入操作時觸發(fā)。
UPDATE 觸發(fā)器：在更新操作時觸發(fā)。
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í)行：
BEFORE：在觸發(fā)事件之前執(zhí)行，可以用來驗(yàn)證或修改要插入或更新的數(shù)據(jù)。
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)用場景
審計跟蹤：記錄數(shù)據(jù)變更歷史。
業(yè)務(wù)規(guī)則實(shí)施：自動執(zhí)行復(fù)雜的業(yè)務(wù)邏輯。
數(shù)據(jù)驗(yàn)證：在數(shù)據(jù)插入或更新前進(jìn)行驗(yàn)證。
自動更新關(guān)聯(lián)數(shù)據(jù)：在一個表更新時自動更新相關(guān)表。
計算衍生值：自動計算和更新統(tǒng)計數(shù)據(jù)。
修改和刪除觸發(fā)器
SQLite 不直接支持修改觸發(fā)器。要修改觸發(fā)器，需要先刪除再重新創(chuàng)建：
DROP TRIGGER IF EXISTS trigger_name;
刪除觸發(fā)器：
DROP TRIGGER IF EXISTS trigger_name;
觸發(fā)器的限制和注意事項(xiàng)
觸發(fā)器不能調(diào)用包含事務(wù)控制的存儲過程。
觸發(fā)器中不能使用 PRAGMA 語句。
觸發(fā)器不能修改它們正在操作的表（避免無限循環(huán)）。
虛擬表和臨時表不支持觸發(fā)器。
觸發(fā)器與性能
觸發(fā)器可能會影響數(shù)據(jù)庫性能：
復(fù)雜的觸發(fā)器邏輯可能會降低插入、更新和刪除操作的速度。
大量的觸發(fā)器可能會增加數(shù)據(jù)庫操作的整體延遲。
觸發(fā)器中的子查詢或復(fù)雜計算可能會顯著影響性能。
優(yōu)化建議：
結(jié)論
SQLite 觸發(fā)器是一個強(qiáng)大的功能，可以自動化數(shù)據(jù)庫操作，維護(hù)數(shù)據(jù)完整性，并實(shí)現(xiàn)復(fù)雜的業(yè)務(wù)規(guī)則。正確使用觸發(fā)器可以大大簡化應(yīng)用程序邏輯，提高數(shù)據(jù)一致性，并自動化許多數(shù)據(jù)庫維護(hù)任務(wù)。
該文章在 2024/11/13 14:48:42 編輯過
400 186 1886
