MySQL的觸發器詳解

MySQL的觸發器詳解

觸發器—trigeer是資料庫下用於監視資料改變的某種情況，並觸發執行某種操作的一個功能，簡單來說就是對某一張表符合某種條件的更改前或者後執行一段預存程序。在這裡就簡單的說一說在MySQL下如何使用觸發器。在使用MySQL的觸發器可以檢查資料完整性、可以捕獲資料庫層中商務邏輯中的錯誤、對於有使用審計表中資料的更改非常有用，但是它的缺點是在使用會觸發器後只能提供擴充驗證，並且無法替換所有驗證，其次增加了後期維護成本，最後是在目前MySQL下觸發器對伺服器的開銷增加比較大，建議在業務比較頻繁的表中不要使用大量的觸發器，在MySQL中觸發器建立文法四要素：1.監視對象(大多都是對錶) 2.監視事件(insert/update/delete) 3.觸發時間(after/before) 4.觸發事件(insert/update/delete)。

首先，目前的MySQL中在使用觸發器時能對某一張表進行insert、delete、update操作的前或者是後為條件從而觸發事先定義好的操作。這裡需要注意的是在使用MySQL的觸發器時必須要有足夠大的許可權至少需要用super使用者的許可權，在預設條件下MySQL的觸發器同function一樣普通使用者是無法進行操作的，如果需要賦予普通使用者建立觸發器的許可權需要在開啟log_bin_trust_function_creators變數：

mysql>  SET  GLOBAL  log_bin_trust_function_creators =  ON ;#在配置好後在my.cnf或my.ini中添加 Query OK, 0  rows  affected (0.00 sec)

在該參數開啟後普通使用者就也可以建立觸發器和function，其次目前的MySQL中觸發器僅支援DML並不支援DDL，其建立常用DDL文法如下：

CREATE  TRIGGER  觸發器名 AFTER /BEFORE  INSERT / UPDATE / DELETE  ON  表名  FOR  EACH ROW   #這句話在MYSQL是固定的  BEGIN DML語句; END ;

此外還要注意在MySQL的觸發器下的old於new的區別，以下引用網路上一段圖文：

1、當使用insert語句的時候，如果原表中沒有資料的話，那麼對於插入資料後表來說新插入的那條資料就是new

2、當使用delete語句的時候，刪除的那一條資料相對於刪除資料後表的資料來說就是old

3、當使用update語句的時候，當修改原表資料的時候相對於修改資料後表的資料來說原表中修改的那條資料就是old，而修改資料後表被修改的那條資料就是new

所以在觸發器中使用new和old還是有區別的，在這裡用2張表說明下，有2張表tbl_name和tbl_name_log

tbl_name的DDL：

DROP  TABLE  IF EXISTS `tbl_name`; CREATE  TABLE  `tbl_name` (   `id`  int (10)  NOT  NULL  COMMENT  '姓名ID' ,   ` name `  varchar (50)  NOT  NULL  COMMENT  '姓名' ,   PRIMARY  KEY  (`id`) ) ENGINE=InnoDB  DEFAULT  CHARSET=utf8;

tbl_name_log的DDL：

DROP  TABLE  IF EXISTS `tbl_name_log`; CREATE  TABLE  `tbl_name_log` (   `id`  int (10)  NOT  NULL  AUTO_INCREMENT COMMENT  '審計ID' ,   ` action `  varchar (10)  NOT  NULL  COMMENT  'tbl_name表中執行的操作' ,   ` name `  varchar (50)  NOT  NULL  COMMENT  '操作的姓名' ,   ` time ` datetime  DEFAULT  NULL  ON  UPDATE  CURRENT_TIMESTAMP  COMMENT  '操作時間' ,   PRIMARY  KEY  (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1  DEFAULT  CHARSET=utf8;

在tbl_name中分別對insert/update/delete三種事件後做出記錄於tbl_name_log表中，就可以在tbl_name添加3個觸發器：

DROP  TRIGGER  IF EXISTS `audit_name_add`; DELIMITER ;; CREATE  TRIGGER  `audit_name_add`  AFTER  INSERT  ON  `tbl_name`  FOR  EACH ROW #在tbl_name插入後在tbl_name_log記錄日誌的觸發器 BEGIN   INSERT  INTO  `tbl_name_log` (` action `,` name `,` time `) VALUES   ( 'add' ,new.` name `,NOW()); END ;; DELIMITER ; DROP  TRIGGER  IF EXISTS `audit_name_update`; DELIMITER ;; CREATE  TRIGGER  `audit_name_update`  AFTER  UPDATE  ON  `tbl_name`  FOR  EACH ROW #在tbl_name更新後在tbl_name_log記錄日誌的觸發器 BEGIN   INSERT  INTO  `tbl_name_log` (` action `,` name `,` time `) VALUES   ( 'update' ,new.` name `,NOW()); END ;; DELIMITER ; DROP  TRIGGER  IF EXISTS `audit_name_del`; DELIMITER ;; CREATE  TRIGGER  `audit_name_del`  AFTER  DELETE  ON  `tbl_name`  FOR  EACH ROW #在tbl_name刪除後在tbl_name_log記錄日誌的觸發器 BEGIN   INSERT  INTO  `tbl_name_log` (` action `,` name `,` time `) VALUES   ( 'del' ,old.` name `,NOW()); END ;; DELIMITER ;

測試一下，在tbl_name表進行插入、更新、刪除後都會有相應的記錄於tbl_name_log表中。