MySQL預存程序和觸發器使用釋疑

測試表結構：

CREATE TABLE `a` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `cnt` INT(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
ROW_FORMAT=DEFAULT

和預存程序相關的幾個許可權：

  alter routine,create routine,execute   

和觸發器相關的許可權：

  trigger  

建立一個測試帳號：

grant select,update,insert,create,delete,trigger,alter routine,create routine,execute on test.* to 'mysqldba'@'%' identified by '123456';

使用相關：

1.預存程序有定義者和調用者之說，觸發器木有

我們可以為一個預存程序指明定義者和調用者，如果木有DEFINER，那預設就是建立者的環境，但是觸發器不行，一旦建立了觸發器，所有的使用者執行的操作都會觸發

例如：

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`p_test`$$

CREATE DEFINER=`mysqldba`@`%` PROCEDURE `p_test`(in a int)
BEGIN
select a;
END$$

DELIMITER ;

只讓mysqldba帳號調用,但是如果具有更大許可權的帳號，如:root，也是可以調用的

2.觸發器可以調用預存程序

3.觸發器可以訪問它自己表的新舊資料(通過old.colname,new.colname訪問)，也可以影響其他表，但是不允許修改已經被函數或者觸發器使用（讀或者寫）的表,在MySQL5.0.10之前的版本，觸發器不能修改其他表

4.MySQL5.0的版本，所有的觸發器都是基於行(FOR EACH ROW)的，因為觸發器通過insert,update,delete啟用,MySQL5.0不支援FOR EACH STATEMENT,如:

DELIMITER $$

DROP TRIGGER /*!50032 IF EXISTS */ `test`.`tai_a`$$

CREATE
/*!50017 DEFINER = 'mysqldba'@'%' */
TRIGGER `tai_a` AFTER INSERT ON `a` 
FOR EACH ROW  -- MySQL 5.0隻能這樣，不能FOR EACH STATEMENT
    BEGIN
insert into b values(NEW.id,NEW.cnt);            
END;
$$

DELIMITER ;

5.觸發器可以通過UDF調用外部應用程式

詳細可以參考：http://forge.mysql.com/projects/project.php?id=211

內部相關：

6.我們可以通過下面方式查看一個庫下面的所有觸發器:

SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_STATEMENT
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA='dbname';

7.觸發器的儲存形式

目前觸發器存放在.TRG檔案中，每一個表中的觸發器儲存在一個TRG檔案中，我們可以到表的資料目錄下，看到這個檔案，如：

[root@sunss test]# ls
a.frm  a.MYD  a.MYI  a.TRG  b.frm  b.MYD  b.MYI  tai_a.TRN  tau_a.TRN  test.frm  test.MYD  test.MYI
[root@sunss test]# pwd
/home/mysql/test
[root@sunss test]# cat a.TRG 
TYPE=TRIGGERS
triggers='CREATE DEFINER=`mysqldba`@`%` TRIGGER `tau_a` AFTER UPDATE ON `a` \n    FOR EACH ROW BEGIN\n            update b set cnt=NEW.cnt where id=NEW.id;\n    END' 'CREATE DEFINER=`mysqldba`@`%` TRIGGER `tai_a` AFTER INSERT ON `a` \n    FOR EACH ROW BEGIN\n         insert into b values(NEW.id,NEW.cnt);            \n    END'
sql_modes=0 0
definers='mysqldba@%' 'mysqldba@%'
client_cs_names='utf8' 'utf8'
connection_cl_names='utf8_general_ci' 'utf8_general_ci'
db_cl_names='utf8_general_ci' 'utf8_general_ci'
You have new mail in /var/spool/mail/root
[root@sunss test]#

8.觸發器可以更新遠端表，但遠端表必須使用FEDERATED儲存引擎

複製相關：

9.觸發器在MySQL複製環境中也是可以工作的

在MySQL5.0的版本和其他大多數資料庫系統一樣，觸發器和複製都能工作。在主庫上，通過觸發器承載的操作，不會複製到從庫上。但是，如果主庫上存在的觸發器，如果在從庫上的相關表中也建立的話，在從庫上的觸發器就能和主庫一樣被啟用、觸發。

10.在主庫上的操作是怎樣被複製到從庫上去的？

首先要確定一件事：主從庫都有同樣的觸發器，在主庫上建立的觸發器也要在從庫上重新建立一遍。這樣DML語句在複製中就能在從庫上啟用觸發器。例如：還是a表，我們在a表中建立了一個after insert 觸發器，複製過程如下:

1).一條insert語句插入a表

2).after insert 啟用觸發器，也插入到b表

3).insert語句寫入到bin log中

4).複製線程獲得並執行insert語句

5).after insert 啟用觸發器，也插入到b表

參考：MySQL手冊預存程序和視圖的許可權控制