MySQL5觸發(fā)器教程

mysql> DROP FUNCTION f;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE p ()
-> BEGIN
-> /* This procedure does nothing */ <--
-> END;//
Query OK, 0 rows affected (0.00 sec)

MySQL早期版本的用戶長(zhǎng)期有需要觸發(fā)器的要求。
我們?cè)?jīng)許諾支持所有ANSI標(biāo)準(zhǔn)的特性。
您可以使用它來(lái)檢查或預(yù)防壞的數(shù)據(jù)進(jìn)入數(shù)據(jù)庫(kù)。
您可以改變或者取消INSERT, UPDATE以及DELETE語(yǔ)句。
您可以在一個(gè)會(huì)話中監(jiān)視數(shù)據(jù)改變的動(dòng)作。

復(fù)合語(yǔ)句(BEGIN / END)是合法的.
流控制（Flow-of-control）語(yǔ)句(IF, CASE, WHILE, LOOP, WHILE, REPEAT, LEAVE,ITERATE)也是合法的.
變量聲明(DECLARE)以及指派(SET)是合法的.
允許條件聲明.
異常處理聲明也是允許的.
但是在這里要記住函數(shù)有受限條件:不能在函數(shù)中訪問(wèn)表.

因此在函數(shù)中使用以下語(yǔ)句是非法的。
ALTER 'CACHE INDEX' CALL COMMIT CREATE DELETE
DROP 'FLUSH PRIVILEGES' GRANT INSERT KILL
LOCK OPTIMIZE REPAIR REPLACE REVOKE
ROLLBACK SAVEPOINT 'SELECT FROM table'
'SET system variable' 'SET TRANSACTION'
SHOW 'START TRANSACTION' TRUNCATE UPDATE

在觸發(fā)器中也有完全一樣的限制.

1. 語(yǔ)法：命名規(guī)則

CREATE TRIGGER <觸發(fā)器名稱> <--
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE }
ON <表名稱>
FOR EACH ROW
<觸發(fā)器SQL語(yǔ)句>

2. 語(yǔ)法：觸發(fā)時(shí)間

CREATE TRIGGER <觸發(fā)器名稱>
{ BEFORE | AFTER } <--
{ INSERT | UPDATE | DELETE }
ON <表名稱>
FOR EACH ROW
<觸發(fā)的SQL語(yǔ)句>

觸發(fā)器有執(zhí)行的時(shí)間設(shè)置：可以設(shè)置為事件發(fā)生前或后。

3. 語(yǔ)法：事件

CREATE TRIGGER <觸發(fā)器名稱>
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE } <--
ON <表名稱>
FOR EACH ROW
<觸發(fā)的SQL語(yǔ)句>

同樣也能設(shè)定觸發(fā)的事件：它們可以在執(zhí)行insert、update或delete的過(guò)程中觸發(fā)。
4. 語(yǔ)法：表

CREATE TRIGGER <觸發(fā)器名稱>
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE }
ON <表名稱> <--
FOR EACH ROW
<觸發(fā)的SQL語(yǔ)句>

觸發(fā)器是屬于某一個(gè)表的:當(dāng)在這個(gè)表上執(zhí)行插入、
更新或刪除操作的時(shí)候就導(dǎo)致觸發(fā)器的激活.
我們不能給同一張表的同一個(gè)事件安排兩個(gè)觸發(fā)器。

5. 語(yǔ)法：（ 步長(zhǎng)）觸發(fā)間隔

CREATE TRIGGER <觸發(fā)器名稱>
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE }
ON <表名稱>
FOR EACH ROW <--
<觸發(fā)的SQL語(yǔ)句>

觸發(fā)器的執(zhí)行間隔：FOR EACH ROW子句通知觸發(fā)器
每隔一行執(zhí)行一次動(dòng)作，而不是對(duì)整個(gè)表執(zhí)行一次。

6. 語(yǔ)法：語(yǔ)句

CREATE TRIGGER <觸發(fā)器名稱>
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE }
ON <表名稱>
FOR EACH ROW
<觸發(fā)的SQL語(yǔ)句> <--

觸發(fā)器包含所要觸發(fā)的SQL語(yǔ)句：這里的語(yǔ)句可以是任何合法的語(yǔ)句，
包括復(fù)合語(yǔ)句，但是這里的語(yǔ)句受的限制和函數(shù)的一樣。
Privileges權(quán)限

你必須擁有相當(dāng)大的權(quán)限才能創(chuàng)建觸發(fā)器（CREATE TRIGGER）。
如果你已經(jīng)是Root用戶，那么就足夠了。這跟SQL的標(biāo)準(zhǔn)有所不同。

因此在下一個(gè)版本的MySQL中，
你完全有可能看到有一種叫做CREATE TRIGGER的新權(quán)限。
然后通過(guò)這樣的方法賦予：
GRANT CREATE TRIGGER ON <表名稱> TO <用戶或用戶列表>;
也可以通過(guò)這樣收回權(quán)限：
REVOKE CREATE TRIGGER ON <表名稱> FROM <用戶或用戶列表>;

CREATE TRIGGER t21_au
BEFORE UPDATE ON t22
FOR EACH ROW
BEGIN
SET @old = OLD . s1;
SET @new = NEW.s1;
END;//

現(xiàn)在如果t21表中的s1列的值是55，那么執(zhí)行了
"UPDATE t21 SET s1 = s1 + 1"之后@old的值會(huì)變成55，
而@new的值將會(huì)變成56。

Example of CREATE and INSERT CREATE和INSERT的例子
創(chuàng)建有觸發(fā)器的表
這里所有的例程中我都假定大家的分隔符已經(jīng)設(shè)置成//（DELIMITER //）。
CREATE TABLE t22 (s1 INTEGER)//
CREATE TRIGGER t22_bi
BEFORE INSERT ON t22
FOR EACH ROW
BEGIN
SET @x = 'Trigger was activated!';
SET NEW.s1 = 55;
END;//

mysql> SELECT @x, t22.* FROM t22//
+------------------------+------+
| @x | s1 |
+------------------------+------+
| Trigger was activated! | 55 |
+------------------------+------+
1 row in set (0.00 sec)

"check"完整性約束例子
什么是"check"約束
在標(biāo)準(zhǔn)的SQL語(yǔ)言中，我們可以在（CREATE TABLE）創(chuàng)建表的過(guò)程中使用"CHECK (condition)"，
例如：
CREATE TABLE t25
(s1 INT, s2 CHAR(5), PRIMARY KEY (s1),
CHECK (LEFT(s2,1)='A'))
ENGINE=INNODB;

CREATE TABLE t25
(s1 INT, s2 CHAR(5),
PRIMARY KEY (s1))
ENGINE=INNODB//

CREATE TRIGGER t25_bi
BEFORE INSERT ON t25
FOR EACH ROW
IF LEFT(NEW.s2,1)<>'A' THEN SET NEW.s1=0; END IF;//

CREATE TRIGGER t25_bu
BEFORE UPDATE ON t25
FOR EACH ROW
IF LEFT(NEW.s2,1)<>'A' THEN SET NEW.s1=0; END IF;//

INSERT INTO t25 VALUES (0,'a') /* priming the pump */ //
INSERT INTO t25 VALUES (5,'b') /* gets error '23000' */ //
Don't Believe The Old MySQL Manual