-
(1) 觸發(fā)器的作用是實(shí)現(xiàn)數(shù)據(jù)完整性，它比主鍵、外鍵、NOT NULL、UNIQUE更加靈活
(2) 觸發(fā)器是特殊的存儲(chǔ)過(guò)程。它與存儲(chǔ)過(guò)程經(jīng)歷的過(guò)程類似（分析、解析、優(yōu)化），但是沒(méi)有接口，不能被顯示調(diào)用，只能自動(dòng)執(zhí)行。
(3) 觸發(fā)器是引發(fā)它的事務(wù)的一部分。只有觸發(fā)器被正確執(zhí)行，該事務(wù)才是完整的。
(4) 使用原則
1° 能用約束實(shí)現(xiàn)數(shù)據(jù)完整性的，優(yōu)先使用約束；
2° 無(wú)法通過(guò)約束實(shí)現(xiàn)的，使用存儲(chǔ)過(guò)程：存儲(chǔ)過(guò)程中在確定更新之前先檢查；
3° 當(dāng)1°，2°都不滿足時(shí)，使用觸發(fā)器。
-
MYSQL中的觸發(fā)器
(1) 語(yǔ)法
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW [trigger_order] trigger_body其中
trigger_time: { BEFORE | AFTER } trigger_event: { INSERT | UPDATE | DELETE } trigger_order: { FOLLOWS | PRECEDES } other_trigger_name(2) A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table.
(3) __觸發(fā)器必須和一個(gè)永久的table關(guān)聯(lián)，不能和一個(gè)臨時(shí)table關(guān)聯(lián)，也不能和視圖關(guān)聯(lián)。__The trigger becomes associated with the table named tbl_name, which must refer to a permanent table. You cannot associate a trigger with a TEMPORARY table or a view.
(4) Trigger names exist in the schema namespace, meaning that all triggers must have unique names within a schema. Triggers in different schemas can have the same name.
(5) CREATE TRIGGER requires the TRIGGER privilege for the table associated with the trigger. The statement might also require the SUPER privilege, depending on the DEFINER value, as described later in this section. If binary logging is enabled, CREATE TRIGGER might require the SUPER privilege, as described in Section 23.7, “Binary Logging of Stored Programs”.
(6) trigger_time is the trigger action time. It can be BEFORE or AFTER to indicate that the trigger activates before or after each row to be modified.
(7) 列的值檢查發(fā)生在觸發(fā)器之前（即使是BEFORE），所以不能用觸發(fā)器先進(jìn)行不合理值的轉(zhuǎn)換。Basic column value checks occur prior to trigger activation, so you cannot use BEFORE triggers to convert values inappropriate for the column type to valid values.
(8) trigger_event indicates the kind of operation that activates the trigger. These trigger_event values are permitted:
INSERT: The trigger activates whenever a new row is inserted into the table; for example, through INSERT, LOAD DATA, and REPLACE statements.
UPDATE: The trigger activates whenever a row is modified; for example, through UPDATE statements.
DELETE: The trigger activates whenever a row is deleted from the table; for example, through DELETE and REPLACE statements. DROP TABLE and TRUNCATE TABLE statements on the table do not activate this trigger, because they do not use DELETE. Dropping a partition does not activate DELETE triggers, either.
(9) The trigger event does not represent a literal type of SQL statement that activates the trigger so much as it represents a type of table operation. For example, an INSERT trigger activates not only for INSERT statements but also LOAD DATA statements because both statements insert rows into a table.
(10) a BEFORE INSERT trigger activates for every row, followed by either an AFTER INSERT trigger or both the BEFORE UPDATE and AFTER UPDATE triggers, depending on whether there was a duplicate key for the row.
(11) 對(duì)于同一個(gè)table，可能會(huì)有在同一個(gè)觸發(fā)事件、同一個(gè)觸發(fā)時(shí)機(jī)的多個(gè)觸發(fā)器。此時(shí)，觸發(fā)器觸發(fā)的順序是按照它們創(chuàng)建的順序來(lái)的。但是可以顯示添加 FOLLOWS 或 PRECEDES，用于指定跟隨在哪個(gè)觸發(fā)器前面或后面。 It is possible to define multiple triggers for a given table that have the same trigger event and action time. For example, you can have two BEFORE UPDATE triggers for a table. By default, triggers that have the same trigger event and action time activate in the order they were created. To affect trigger order, specify a trigger_order clause that indicates FOLLOWS or PRECEDES and the name of an existing trigger that also has the same trigger event and action time. With FOLLOWS, the new trigger activates after the existing trigger. With PRECEDES, the new trigger activates before the existing trigger.
(12) trigger body is the statement to execute when the trigger activates. To execute multiple statements, use the BEGIN ... END compound statement construct.
(13) 可以用NEW.列名和OLD.列名代表新表的列和舊表的列。 Within the trigger body, you can refer to columns in the subject table (the table associated with the trigger) by using the aliases OLD and NEW. OLD.col_name refers to a column of an existing row before it is updated or deleted. NEW.col_name refers to the column of a new row to be inserted or an existing row after it is updated.
注：Triggers cannot use NEW.col name or use OLD.col name to refer to generated columns. For information about generated columns, see Section 13.1.18.8, “CREATE TABLE and Generated Columns”.
(14) The DEFINER clause specifies the MySQL account to be used when checking access privileges at trigger activation time. If a user value is given, it should be a MySQL account specified as 'user_name'@'host_name', CURRENT_USER, or CURRENT_USER(). The default DEFINER value is the user who executes the CREATE TRIGGER statement. This is the same as specifying DEFINER = CURRENT_USER explicitly.
(15) Within a trigger body, the CURRENT_USER() function returns the account used to check privileges at trigger activation time. This is the DEFINER user, not the user whose actions caused the trigger to be activated.
（16） If you use LOCK TABLES to lock a table that has triggers, the tables used within the trigger are also locked, as described in LOCK TABLES and Triggers.
-
MYSQL中觸發(fā)器的語(yǔ)法和示例
(1) 創(chuàng)建觸發(fā)器
CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW [trigger_order] trigger_body其中
trigger_time: { BEFORE | AFTER } trigger_event: { INSERT | UPDATE | DELETE } trigger_order: { FOLLOWS | PRECEDES } other_trigger_name(2) 刪除觸發(fā)器
DROP TRIGGER [IF EXISTS] [database_name.]trigger_name其中
The schema (database) name is optional. If the schema is omitted, the trigger is dropped from the default schema. DROP TRIGGER requires the TRIGGER privilege for the table associated with the trigger.
(3) 一個(gè)簡(jiǎn)單示例
CREATE TABLE account ( acct_num INT, amount DECIMAL(10,2)); CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount;這個(gè)示例的trigger_body很簡(jiǎn)單：FOR EACH ROW SET @sum = @sum + NEW.amount。它的作用是將新插入的amount累加到一個(gè)用戶變量 @sum 中。
(4) 另一個(gè)簡(jiǎn)單示例
對(duì)于同一個(gè)表、同一個(gè)觸發(fā)事件、同一個(gè)觸發(fā)時(shí)機(jī)，可以定義多個(gè)觸發(fā)器，使用PRECEDES和FOLLOWS可以顯示指定觸發(fā)器的觸發(fā)順序。
CREATE TRIGGER ins_transaction BEFORE INSERT ON account FOR EACH ROW PRECEDES ins_sum SET @deposits = @deposits + IF(NEW.amount>0,NEW.amount,0), @withdrawals = @withdrawals + IF(NEW.amount<0,-NEW.amount,0);這個(gè)觸發(fā)器會(huì)在 ins_sum 觸發(fā)器之前被觸發(fā)。如果沒(méi)有PRECEDES的話，它會(huì)在ins_sum觸發(fā)器之后被觸發(fā)，因?yàn)槟J(rèn)是按觸發(fā)器創(chuàng)建的順序。
(5) 在 MySQL 5.7.2 之前，一個(gè)表不能在同一個(gè)觸發(fā)事件、同一個(gè)觸發(fā)時(shí)機(jī)定義多個(gè)觸發(fā)器。此時(shí)，應(yīng)該使用 BEGIN ... END
(6) OLD和NEW分別用來(lái)代表更新前的表和更新后的表，下面分為幾種情況
1° 對(duì)于INSERT觸發(fā)器，只能使用NEW.colname，不能使用OLD.colname（因?yàn)闆](méi)有舊的列)
2° 對(duì)于DELETE觸發(fā)器，只能使用OLD.colname，不能使用NEW.colname（因?yàn)闆](méi)有新的列）
3° 對(duì)于UPDATE觸發(fā)器，NEW.colname和OLD.name 都可以使用，NEW.colname代表更新后記錄的某列， OLD.colname代表更新前記錄的某列
(7) OLD.xxx應(yīng)該是只讀的，不能更改數(shù)據(jù)；
NEW.xxx可讀可寫，對(duì)于一個(gè)BEFORE觸發(fā)器，可以用SET NEW.colname = value來(lái)改變數(shù)據(jù)，也就是說(shuō)使用BEFORE觸發(fā)器可以改變要插入或更新的數(shù)據(jù)值；
!!!但是，對(duì)于AFTER觸發(fā)器，使用SET NEW.colname = value改變數(shù)據(jù)是沒(méi)有意義的，因?yàn)槟骋恍杏涗浀臄?shù)據(jù)已經(jīng)改變過(guò)了
(8) By using the BEGIN ... END construct, you can define a trigger that executes multiple statements. Within the BEGIN block, you also can use other syntax that is permitted within stored routines such as conditionals and loops. However, just as for stored routines, if you use the mysql program to define a trigger that executes multiple statements, it is necessary to redefine the mysql statement delimiter so that you can use the ; statement delimiter within the trigger definition.
示例
delimiter // CREATE TRIGGER upd_check BEFORE UPDATE ON account FOR EACH ROW BEGIN IF NEW.amount < 0 THEN SET NEW.amount = 0; ELSEIF NEW.amount > 100 THEN SET NEW.amount = 100; END IF; END; // delimiter ;(9) The trigger cannot use the CALL statement to invoke stored procedures that return data to the client or that use dynamic SQL. (Stored procedures are permitted to return data to the trigger through OUT or INOUT parameters.)
(10) 觸發(fā)器不能使用事務(wù)
(11) 錯(cuò)誤處理順序
1° 如果BEFORE觸發(fā)器報(bào)錯(cuò)，則在記錄上的操作不會(huì)執(zhí)行；
2° 無(wú)論后續(xù)的步驟是否執(zhí)行（成功），BEFORE觸發(fā)器都會(huì)被觸發(fā)；
3° 只有 BEFORE觸發(fā)器 和 對(duì)記錄的操作都成功了，AFTER觸發(fā)器才會(huì)執(zhí)行；
4° 如果是在事務(wù)中，觸發(fā)器的報(bào)錯(cuò)也會(huì)導(dǎo)致事務(wù)回滾
Triggers can contain direct references to tables by name, such as the trigger named testref shown in this example:
(12) 示例
CREATE TABLE test1(
a1 INT);
CREATE TABLE test2(
a2 INT);
CREATE TABLE test3(
a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
b4 INT DEFAULT 0);
DELIMITER |
CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW
BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END; |
DELIMITER ;
測(cè)試
INSERT INTO test3 (a3) VALUES
(NULL), (NULL), (NULL), (NULL), (NULL),
(NULL), (NULL), (NULL), (NULL), (NULL);
INSERT INTO test4 (a4) VALUES
(0), (0), (0), (0), (0), (0), (0), (0), (0), (0);
INSERT INTO test1 VALUES
(1), (3), (1), (7), (1), (8), (4), (4);
mysql> SELECT * FROM test1;
+------+
| a1 |
+------+
| 1 |
| 3 |
| 1 |
| 7 |
| 1 |
| 8 |
| 4 |
| 4 |
+------+
mysql> SELECT * FROM test2;
+------+
| a2 |
+------+
| 1 |
| 3 |
| 1 |
| 7 |
| 1 |
| 8 |
| 4 |
| 4 |
+------+
mysql> SELECT * FROM test3;
+----+
| a3 |
+----+
| 2 |
| 5 |
| 6 |
| 9 |
| 10 |
+----+
mysql> SELECT * FROM test4;
+----+------+
| a4 | b4 |
+----+------+
| 1 | 3 |
| 2 | 0 |
| 3 | 1 |
| 4 | 2 |
| 5 | 0 |
| 6 | 0 |
| 7 | 1 |
| 8 | 1 |
| 9 | 0 |
| 10 | 0 |
+----+------+
-
使用BEFORE類型的觸發(fā)器時(shí)，可以改變要插入列的值；使用AFTER類型的觸發(fā)器時(shí)，不能改變要插入列的值（事實(shí)上，根本無(wú)法創(chuàng)建這樣的觸發(fā)器）
示例
DROP TABLE IF EXISTS t25; CREATE TABLE t25 ( s1 INT NOT NULL UNIQUE, s2 VARCHAR(5), PRIMARY KEY (s1) ); DROP TRIGGER IF EXISTS t25_bi; DELIMITER // CREATE TRIGGER t25_bi BEFORE INSERT ON t25 FOR EACH ROW BEGIN IF LEFT(NEW.s2, 1) <> 'A' THEN SET NEW.s1 = 0; SET NEW.s2 = 'HEHE'; END IF; END; // DELIMITER ; INSERT INTO t25 VALUES (3, 'HAHA'); SELECT * FROM t25; 結(jié)果 s1 s2 0 HEHE -
由于MYSQL不支持 CHECK約束檢查，所以可以使用觸發(fā)器代替
示例
USE temp; DROP TABLE IF EXISTS t25; CREATE TABLE t25 ( s1 INT NOT NULL UNIQUE, s2 VARCHAR(5), PRIMARY KEY (s1) ); DROP TRIGGER IF EXISTS t25_bi; DELIMITER // CREATE TRIGGER t25_bi AFTER INSERT ON t25 FOR EACH ROW BEGIN IF LEFT(NEW.s2, 1) <> 'A' THEN DELETE FROM t25 WHERE s1 = New.s1; END IF; END; // DELIMITER ; INSERT INTO t25 VALUES (3, 'AHA'); SELECT * FROM t25; /* INSERT INTO t25 VALUES (5, 'hAHA'); SELECT * FROM t25; */ -
使用觸發(fā)器，阻止對(duì)某列的更新
示例
USE temp; DROP TABLE IF EXISTS t25; CREATE TABLE t25 ( s1 INT NOT NULL UNIQUE, s2 VARCHAR(5), PRIMARY KEY (s1) ); DROP TRIGGER IF EXISTS t25_bi; DELIMITER // CREATE TRIGGER t25_bi BEFORE UPDATE ON t25 FOR EACH ROW BEGIN DECLARE msg VARCHAR(255); IF NEW.s1 <> OLD.s1 THEN SET msg = CONCAT('MyTriggerError: Trying to modify s1: ', CAST(NEW.s1 AS char)); SIGNAL SQLSTATE '45000' SET message_text = msg; END IF; END; // DELIMITER ; INSERT INTO t25 VALUES (3, 'AHA'); UPDATE t25 SET s1 = 4 WHERE s1 = 3; SELECT * FROM t25; -
MYSQL觸發(fā)器示例
trigger_check.sql
USE temp; DROP TABLE IF EXISTS t25; CREATE TABLE t25 ( s1 INT NOT NULL UNIQUE, s2 VARCHAR(5), PRIMARY KEY (s1) ); DROP TRIGGER IF EXISTS t25_bi; DELIMITER // CREATE TRIGGER t25_bi AFTER INSERT ON t25 FOR EACH ROW BEGIN IF LEFT(NEW.s2, 1) <> 'A' THEN DELETE FROM t25 WHERE s1 = New.s1; END IF; END; // DELIMITER ; INSERT INTO t25 VALUES (3, 'AHA'); SELECT * FROM t25; /* INSERT INTO t25 VALUES (5, 'hAHA'); SELECT * FROM t25; */trigger_modification.sql
USE temp; DROP TABLE IF EXISTS t25; CREATE TABLE t25 ( s1 INT NOT NULL UNIQUE, s2 VARCHAR(5), PRIMARY KEY (s1) ); DROP TRIGGER IF EXISTS t25_bi; DELIMITER // CREATE TRIGGER t25_bi BEFORE UPDATE ON t25 FOR EACH ROW BEGIN DECLARE msg VARCHAR(255); IF NEW.s1 <> OLD.s1 THEN set msg = concat('MyTriggerError: Trying to modify s1: ', cast(new.s1 as char)); signal sqlstate '45000' set message_text = msg; END IF; END; // DELIMITER ; INSERT INTO t25 VALUES (3, 'AHA'); UPDATE t25 SET s1 = 4 WHERE s1 = 3; SELECT * FROM t25;
