程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 存儲過程之六—觸發器，存儲過程之六

存儲過程之六—觸發器，存儲過程之六

編輯：MySQL綜合教程

存儲過程之六—觸發器，存儲過程之六

一、觸發器

　　觸發器是與表有關的命名數據庫對象，當表上出現特定事件時，將調用該對象。它是與表事件相關的特殊的存儲過程，它的執行不是由程序調用，也不是手工啟動，而是由事件來觸發，比如當對一個表進行操作（ insert，delete， update）時就會激活它執行。

　　觸發器經常用於加強數據的完整性約束和業務規則等。觸發器可以從 DBA_TRIGGERS ，USER_TRIGGERS 數據字典中查到。
觸發器有一個非常好的特性就是:觸發器可以禁止或回滾違反引用完整性的更改，從而取消所嘗試的數據修改。

　　1、創建

　　　　語法：

　　　　CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt

　　　　trigger_time:是觸發器的動作時間。它可以是BEFORE或AFTER，以指明觸發器是在激活它的語句之前或之後觸發。

　　　　trigger_event: 指明了激活觸發器的語句的類型。trigger_event可以是如下之中的一個：

INSERT：將新行插入表時激活觸發器，例如，通過INSERT、LOAD DATA和REPLACE語句。
UPDATE：更改某一行時激活觸發器，例如，通過UPDATE語句。
DELETE：從表中刪除某一行時激活觸發器，例如，通過DELETE和REPLACE語句。

　　對於具有相同觸發器動作時間和事件的給定表，不能有兩個觸發器。如，對於某一表，不能有兩個BEFORE UPDATE觸發器。但可以有1個BEFORE UPDATE觸發器和1個BEFORE INSERT觸發器，或1個BEFORE UPDATE觸發器和1個AFTER UPDATE觸發器。

　　　　trigger_stmt：是當觸發器激活時執行的語句。如果你打算執行多個語句，可使用BEGIN ... END復合語句結構。這樣，能夠定義執行多條語句的觸發器。

觸發器不能調用將數據返回客戶端的存儲程序，也不能使用采用CALL語句的動態SQL（允許存儲程序通過參數將數據返回觸發器）。觸發器不能使用以顯式或隱式方式開始或結束事務的語句，如START TRANSACTION、COMMIT或ROLLBACK。

　　2、查看

SHOW TRIGGERS; -- 查看所有觸發器 
SHOW CREATE TRIGGER ins_sum;-- 查看具體觸發器

　　3、刪除　

DROP TRIGGER trigger_name; -- 刪除具體觸發器

　　注釋：從MySQL 5.0.10之前的MySQL版本升級到5.0.10或更高版本時（包括所有的MySQL 5.1版本），必須在升級之前捨棄所有的觸發器，並在隨後重新創建它們，否則，在升級之後DROP TRIGGER不工作。

　　示例：

DROP TABLE IF EXISTS `account`;
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;-- 將插入amount列的值加起來。

SET @sum = 0;
INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
SELECT @sum AS 'Total amount inserted';

執行了INSERT語句後，@sum的值是14.98 + 1937.50 – 100，或1852.48。
執行結果如下：

二、old與NEW

　　關鍵字new和OLD的區別：

INSERT：只有 NEW
UPDATE： BEFORE OLD 、 AFTER NEW
DELETE：只有OLD

　　用OLD命名的列是只讀的。你可以引用它，但不能更改它。對於用NEW命名的列，如果具有SELECT權限，可引用它。可使用“SET NEW.col_name = value”更改它的值。這意味著，你可以使用觸發器來更改將要插入到新行中的值，或用於更新行的值。OLD和NEW是對觸發器的MySQL擴展。

三、實例

　　表結構如下：　

DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;

　　1、insert

DROP TRIGGER IF EXISTS trigger_insert;
-- before
CREATE TRIGGER trigger_insert BEFORE INSERT ON person
FOR EACH ROW  
SET @info = new.username, new.age = new.age + 2; 

INSERT INTO person (username, age, password) VALUES ( 'zhangsan', '21', '123456');
SELECT @info;

　　插入之前被調用，age被加1，也記錄了加入記錄的username。insert 只有before才能修改new.age的值，after中，無法修改。

　　2、update

 1 DROP TRIGGER IF EXISTS trigger_update_before;
 2 CREATE TRIGGER trigger_update_before BEFORE UPDATE ON person
 3 FOR EACH ROW 
 4 BEGIN
 5 SET @infoname1 = new.username; -- 記錄新名字
 6 SET new.age = new.age + 3; -- 修改年齡
 7 END;
 8 
 9 DROP TRIGGER IF EXISTS trigger_update_after;
10 CREATE TRIGGER trigger_update_after AFTER UPDATE ON person
11 FOR EACH ROW  
12 SET @infoname2 =  old.username;  -- 記錄修改前的名字
13 
14 SELECT * FROM person WHERE id = 1;
15 UPDATE person SET username='lisi', age = 0 WHERE id = 1;
16 SELECT * FROM person WHERE id = 1;
17 SELECT @infoname1 AS newname, @infoname2 AS oldname;

　　執行完14行結果如下：

　　執行完15、16行結果如下：

　　年齡已被觸發器更改

　　執行完17行後結果如下：

　　觸發器已獲取更改前後的用戶名稱。

　　3、delete

1 DROP TRIGGER IF EXISTS trigger_delete_after;
2 CREATE TRIGGER trigger_delete_after AFTER DELETE ON person
3 FOR EACH ROW  
4 SET @infoname3 =  old.username;  -- 獲取被刪除的用戶名稱
5  
6 DELETE FROM person WHERE id = 1;
7 SELECT * FROM person WHERE id = 1;
8 SELECT @infoname3 AS oldname;

　　執行完第7行結果如下：

　　記錄已經被刪除

　　執行完第8行結果如下：

　　顯示已被刪除的記錄的用戶名稱。

　　4、觸發器調用存儲過程

　　在觸發器中通過使用BEGIN ... END結構，能夠定義執行多條語句的觸發器。在BEGIN塊中，還能使用其他語法，如條件和循環等。我們可以將這些語句封裝到存儲過程裡面，供觸發器調用。

　　實例如下：

 1 DROP TABLE IF EXISTS `person`;
 2 CREATE TABLE `person` (
 3   `id` int(11) NOT NULL AUTO_INCREMENT,
 4   `username` varchar(255) DEFAULT NULL,
 5   `age` int(11) DEFAULT NULL,
 6   `password` varchar(255) DEFAULT NULL,
 7   PRIMARY KEY (`id`)
 8 ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;
 9 INSERT INTO person (username, age, password) VALUES ( 'zhangsan', '21', '123456');
10 
11 DROP PROCEDURE IF EXISTS pro_person_update;
12 CREATE PROCEDURE pro_person_update(
13     INOUT age INT(11)
14 )
15 BEGIN
16     IF age < 0 THEN -- 年齡小於0
17         SET age = 0;
18     ELSEIF age > 100 THEN -- 年齡大於100
19         SET age= 100;
20     END IF;
21 END;
22 
23 DROP TRIGGER IF EXISTS trgger_proceduce_person_update;
24 CREATE TRIGGER trgger_proceduce_person_update BEFORE UPDATE ON person
25 FOR EACH ROW 
26     CALL pro_person_update(new.age);
27 
28 SELECT * FROM person WHERE id = 1;
29 UPDATE person SET age= -3 WHERE id=1;
30 SELECT * FROM person WHERE id = 1;

　　由於上面的三個例子生成的觸發器會對本例有影響，所以重新創建表結構。

　　執行完第28行結果如下：

　　執行完第29，30行後，結果如下：

　　age為0，存儲過程已經被update before觸發器調用。

　　在觸發器的執行過程中，MySQL處理錯誤的方式如下：

如果BEFORE觸發器執行失敗，sql語句也會執行失敗。
僅當BEFORE觸發器（如果有的話）和sql語句執行成功，才執行AFTER觸發器。
如果在BEFORE或AFTER觸發器的執行過程中出現錯誤，將導致調用觸發器的整個語句的失敗。
對於事務性表（在 innodb上所建立的表是事務性表，是事務安全的。），如果觸發器失敗（以及由此導致的整個語句的失敗），該語句所執行的所有更改將回滾。對於非事務性表，不能執行這類回滾，因而，即使語句失敗，失敗之前所作的任何更改依然有效。

存儲過程與觸發器到底是個什玩意?

存儲過程相當於打包好的sql語法,可以包含復雜的sql操作,在程序調用時只要執行該存儲過程,一句話就可以完成復雜的數據庫操作.
觸發器是也是打包好的sql語法,只不過執行它的條件是當被設定改觸發器的表有變化的時候.比如我可以寫一個觸發器,設定它在插入一筆數據到這個表裡的時候,去同步另一個表.那麼在程序裡我只要做到往數據庫裡插一筆數據,那麼另一個表就可以同時被更新.
用存儲過程和數據庫的好處是,可以充分利用數據庫資源,減少程序代碼,程序員的工作將更簡便,寫出來的代碼也更簡潔明了.當然要真正明白程序在做什麼,還是要到存儲過程和觸發器裡面看明白.
呵呵,這些都是本人自身體會,你可以有選擇的看看.

怎在存儲過程中建立觸發器

(1).存儲過程：
create procedure for_select（你想用的觸發器名字）
delare @name char(8) output（用於輸出的）,@age int output（用於輸出）,@phone_num char(11) （輸入）
as
select @name=name(列名),@age=age（列名） from table_name（表名）
where phone_num（列名）=@phone_num

(2)觸發器
create tigger for_update
on table_name
after update
as
if(update(phone_num))
update table_name2
set phone_num=i.phone_num
from deleted d,inserted i
where table_name.phone_num=d.phone_num
end
其中:deleted與inserted是sql中默認的臨時表。用來儲存原來的值和插入的值。本觸發器的作用是當更新table_name 中的phone_num時table_name1中的phone_num也一起更新！注意觸發器只能用於update.insert.delete.select中不能用於創建表............