約定和編程風格
每次我想要演示實際代碼時,我會對mysql客戶端的屏幕就出現的代碼進行調整,將字體改成Courier,使他們看起來與普通文本不一樣讓大家區別程序代碼和正文)。在這裡舉個例子:
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>"和"->"這些系統顯示去掉,你可以直接將代碼復制到mysql客戶端程序中如果你現在所讀的不是電子版的,可以在mysql.com網站下載相關腳本) 所以的例子都已經在Suse 9.2 Linux、Mysql 5.0.3公共版上測試通過。在您閱讀本書的時候,Mysql已經有更高的版本,同時能支持更多OS了,包括Windows,Sparc,HP-UX。因此這裡的例子將能正常的運行在您的電腦上。但如果運行仍然出現故障,可以咨詢你認識的資深Mysql用戶,這樣就能得到比較好的支持和幫助。
MySQL早期版本的用戶長期有需要觸發器的要求。 我們曾經許諾支持所有ANSI標准的特性。 您可以使用它來檢查或預防壞的數據進入數據庫。 您可以改變或者取消INSERT, UPDATE以及DELETE語句。 您可以在一個會話中監視數據改變的動作。在這裡我假定大家都讀過"MySQL新特性"叢書的第一集--"MySQL存儲過程",那麼大家都應該知道MySQL至此存儲過程和函數,那是很重要的知識,因為在觸發器中你可以使用在函數中使用的語句。特別舉個例子:
復合語句(BEGIN / END)是合法的. 流控制Flow-of-control)語句(IF, CASE, WHILE, LOOP, WHILE, REPEAT, LEAVE,ITERATE)也是合法的. 變量聲明(DECLARE)以及指派(SET)是合法的. 允許條件聲明. 異常處理聲明也是允許的. 但是在這裡要記住函數有受限條件:不能在函數中訪問表. 因此在函數中使用以下語句是非法的。 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 在觸發器中也有完全一樣的限制.觸發器相對而言比較新,因此會有bugs)缺陷.所以我在這裡給大家警告,就像我在存儲過程書中所說那樣.不要在含有重要數據的數據庫中使用這個觸發器,如果需要的話在一些以測試為目的的數據庫上使用,同時在你對表創建觸發器時確認這些數據庫是默認的。
1. 語法:命名規則 CREATE TRIGGER <觸發器名稱> <-- { BEFORE | AFTER } { INSERT | UPDATE | DELETE } ON <表名稱> FOR EACH ROW <觸發器SQL語句>觸發器必須有名字,最多64個字符,可能後面會附有分隔符.它和MySQL中其他對象的命名方式基本相象.
2. 語法:觸發時間 CREATE TRIGGER <觸發器名稱> { BEFORE | AFTER } <-- { INSERT | UPDATE | DELETE } ON <表名稱> FOR EACH ROW <觸發的SQL語句> 觸發器有執行的時間設置:可以設置為事件發生前或後。 3. 語法:事件 CREATE TRIGGER <觸發器名稱> { BEFORE | AFTER } { INSERT | UPDATE | DELETE } <-- ON <表名稱> FOR EACH ROW <觸發的SQL語句> 同樣也能設定觸發的事件:它們可以在執行insert、update或delete的過程中觸發。 4. 語法:表 CREATE TRIGGER <觸發器名稱> { BEFORE | AFTER } { INSERT | UPDATE | DELETE } ON <表名稱> <-- FOR EACH ROW <觸發的SQL語句> 觸發器是屬於某一個表的:當在這個表上執行插入、 更新或刪除操作的時候就導致觸發器的激活. 我們不能給同一張表的同一個事件安排兩個觸發器。 5. 語法: 步長)觸發間隔 CREATE TRIGGER <觸發器名稱> { BEFORE | AFTER } { INSERT | UPDATE | DELETE } ON <表名稱> FOR EACH ROW <-- <觸發的SQL語句> 觸發器的執行間隔:FOR EACH ROW子句通知觸發器 每隔一行執行一次動作,而不是對整個表執行一次。 6. 語法:語句 CREATE TRIGGER <觸發器名稱> { BEFORE | AFTER } { INSERT | UPDATE | DELETE } ON <表名稱> FOR EACH ROW <觸發的SQL語句> <-- 觸發器包含所要觸發的SQL語句:這裡的語句可以是任何合法的語句, 包括復合語句,但是這裡的語句受的限制和函數的一樣。 Privileges權限 你必須擁有相當大的權限才能創建觸發器CREATE TRIGGER)。 如果你已經是Root用戶,那麼就足夠了。這跟SQL的標准有所不同。 因此在下一個版本的MySQL中, 你完全有可能看到有一種叫做CREATE TRIGGER的新權限。 然後通過這樣的方法賦予: GRANT CREATE TRIGGER ON <表名稱> TO <用戶或用戶列表>; 也可以通過這樣收回權限: 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;// 現在如果t21表中的s1列的值是55,那麼執行了 "UPDATE t21 SET s1 = s1 + 1"之後@old的值會變成55, 而@new的值將會變成56。
Example of CREATE and INSERT CREATE和INSERT的例子 創建有觸發器的表 這裡所有的例程中我都假定大家的分隔符已經設置成//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;//在最開始我創建了一個名字為t22的表,然後在表t22上創建了一個觸發器t22_bi,當我們要向表中的行插入時,觸發器就會被激活,執行將s1列的值改為55的動作。
mysql> SELECT @x, t22.* FROM t22// +------------------------+------+ | @x | s1 | +------------------------+------+ | Trigger was activated! | 55 | +------------------------+------+ 1 row in set (0.00 sec)大家可以看到INSERT動作之後的結果,和我們預期的一樣,x標記被改動了,同時這裡插入的數據不是我們開始輸入的插入數據,而是觸發器自己的數據。
"check"完整性約束例子 什麼是"check"約束 在標准的SQL語言中,我們可以在CREATE TABLE)創建表的過程中使用"CHECK (condition)", 例如: CREATE TABLE t25 (s1 INT, s2 CHAR(5), PRIMARY KEY (s1), CHECK (LEFT(s2,1)='A')) ENGINE=INNODB;這裡CHECK的意思是"當s2列的最左邊的字符不是'A'時,insert和update語句都會非法",MySQL的視圖不支持CHECK,我個人是很希望它能支持的。但如果你很需要在表中使用這樣的功能,我建議大家使用觸發器來實現。
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;//我只需要使用BEFORE INSERT和BEFORE UPDATE語句就行了,刪除了觸發器不會對表有影響,同時AFTER的觸發器也不能修改NEW的過程變量transition variables)。為了激活觸發器,我執行了向表中的行插入s1=0的數據,之後只要執行符合LEFT(s2,1) <> 'A'條件的動作都會失敗:
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該拋棄舊的MySQL的手冊了