mysql之觸發(fā)器

       觸發(fā)器是一種 特殊的存儲過程，不同的是存儲過程要用CALL來調(diào)用，而觸發(fā)器不需要使用CALL也不需要手工啟動，只要當一個預定義的事件發(fā)生時，就會被MYSQL自動調(diào)用。它在插入，刪除或修改特定表中的數(shù)據(jù)時觸發(fā)執(zhí)行，它比數(shù)據(jù)庫本身的標準功能有更精細和更復雜的數(shù)據(jù)控制能力。它是一種與表操作有關的數(shù)據(jù)庫對象，當觸發(fā)器所在表上出現(xiàn)指定事件時，將調(diào)用該對象，即表的操作事件觸發(fā)表上的觸發(fā)器的執(zhí)行。

創(chuàng)建觸發(fā)器：

       創(chuàng)建觸發(fā)器語法如下：

              CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW tigger_stmt

       其中：

                trigger_name：標識觸發(fā)器名稱，由用戶自定義

                trigger_time  ：標識觸發(fā)時機，取值為BEFORE或AFTER，以指明觸發(fā)程序是在激活它的語句之前或之后觸發(fā)

                trigger_event：標識觸發(fā)事件，取值為INSERT,UPDATE或DELETE

                tbl_name：標識建立觸發(fā)器的表名，即在哪張表上建立觸發(fā)器

                trigger_stmt：觸發(fā)器程序體，可以是一句sql語句，或者用BEGIN和END包含的多條語句。觸發(fā)程序是與表有關的命名數(shù)據(jù)庫對象，當表上出現(xiàn)特定事件時，將激活該對象。觸發(fā)程序與命名為tbl_name的表相關。tbl_name必須引用永久性表。

        由此可見，我們可以建立6種觸發(fā)器，即BEFORE INSERT,BEFORE UPDATE,BDFORE DELETE,AFTER INSERT,AFTER UPDATE,AFTER DELETE。另外，我們不能在同一張表上建立2個相同類型的觸發(fā)器，因此在一個表上最多建立6個觸發(fā)器。

接下來我們詳細了解下trigger_event：

       mysql除了對INSERT,UPDATE,DELETE基本操作進行定義外，還定義了LOAD DATA和REPLACE語句，這兩種語句也能引起上述6種類型的觸發(fā)器的觸發(fā)。

               LOAD DATA語句用于將一個文件裝入到一個數(shù)據(jù)表中，相當于一系列的INSERT操作。

               REPLACE語句一般來說和INSERT語句很像，只是在表中有primary key或unique索引時，如果插入的數(shù)據(jù)和原來primary key和unique索引一致時，會先刪除原來的數(shù)據(jù)，然后增加一條新數(shù)據(jù)，也就是說，一條REPLACE語句有時等價于一條INSERT語句，有時候等價于一條DELETE語句加上一條INSERT語句。

              trigger_event指明了激活觸發(fā)程序的語句的類型，可以是下述值之一：

                       INSERT:將新行插入表時激活觸發(fā)器程序，例如通過INSERT，LOAD DATA,和REPLACE語句。

                       UPDATE:更改某一行時激活觸發(fā)程序，例如通過UPDATE語句。

                       DELETE:從表中刪除某一行時激活觸發(fā)程序，例如，通過DELETE和REPLACE語句。這里我們要注意trigger_event與以表操作方式激活觸發(fā)器程序的sql語句并不很類似，這點很重要。例如，關于INSERT的BEFORE觸發(fā)程序不僅能被INSERT程序激活，也能被LOAD DATA語句激活。

刪除觸發(fā)器：

       DROP TRIGGER [schema_name.]trigger_name

                如上是舍棄觸發(fā)器程序，方案名稱（schema_name）是可選的，如果省略了方案，將從當前方案中舍棄觸發(fā)程序。舍棄觸發(fā)器程序語句需要super權限。

上述我們提到了BEGIN...END語句，我們來說說他們的用法：

       在mysql中，BEGIN...END語句的語法為：

BEGIN

[statement_list]

END

      其中，statement_list代表一個或多個語句的列表，列表內(nèi)的每條語句都必須用；來結尾，而在mysql中分號是語句結束的標識符，遇到分號表示該段語句一結束，mysql可以開始執(zhí)行了，因此，解釋器遇到statement_list中的分號后就開始執(zhí)行，然后會報出錯誤，因為沒有找到和BEGIN拼配的END.這時就會用到DELIMITER（定界符，分隔符的意思）命令，它是一條命令，不需要語句結束標識，語法為：

        DELIMITER new_delemiter

        其中new_delemiter為一個或多個長度的符號，默認的是分號，我們可以修改為其他符號$：DELIMITER $.在這之后的語句以分號結束，解釋器不會有什么反應，只有遇到了$才認為是語句結束。注意，使用完之后，我們還得把它給改回來。

我們來看看一個具體的使用BEGIN...END的實例：

接下來我們看一個創(chuàng)建觸發(fā)器的實例：

       我們假設系統(tǒng)中有兩個表：

              班級表：class（班級號 classID,班內(nèi)學生數(shù)stuCount）

              學生表：student（學號 stuID，所屬班級號 calssID）

我們要創(chuàng)建觸發(fā)器來使班級表中的班內(nèi)學生數(shù)隨著學生的添加自動更新，代碼如下：

       DELIMITER $

       create trigger tri_stuInsert after insert

       on student for each row

       begin

       declare c int;

       set c =(select stuCount from class where classID=new.classID);

       update class set stuCount =c+1 where classID =new.classID);

       end$

       DELIMITER ;

       我們來說說上面的變量。在mysql中，使用DECLARE來定義一局部變量，該變量只能在BEGIN...END復合語句中使用，并且應該定義在復合語句的開頭，也就是其他語句之前，語法是：declare var_name[...]type[DEFAULT value].其中，var_name為變量名稱，同sql語句一樣，變量名不區(qū)分大小寫，type為mysql支持的任何數(shù)據(jù)類型，可以同時定義多個同類型的變量，用逗號隔開，變量初始值為NULL,如果需要可以使用DEFAULT子句提供默認值，值可以是一個表達式。對變量賦值采用set語句，語法為：set val_name=expr[]...

我們在上述示例中還用到了NEW關鍵字，下面我們就來說說NEW和OLD關鍵字：

       在mysql中用他們來表示觸發(fā)器的所在表中，觸發(fā)了觸發(fā)器的那一行數(shù)據(jù)。

                在INSERT觸發(fā)器中，NEW用來表示將要（BEFORE）或已經(jīng)（AFTER）插入的新數(shù)據(jù)

                在UPDATE型觸發(fā)器中，OLD用來表示將要或已經(jīng)被修改的數(shù)據(jù)，NEW用來表示將要或已經(jīng)修改為的新數(shù)據(jù)

                在DELETE型觸發(fā)器中，OLD用來表示將要或已經(jīng)被刪除的原數(shù)據(jù)

                使用方法是：new.columnName(columnName是相應的某一列名）

        另外，old是只讀的，而new則可以在觸發(fā)器中使用set賦值，這樣不會再次觸發(fā)觸發(fā)器，造成循環(huán)調(diào)用（例如每插入一個學生前都在其學號前加“2016”）。

查看觸發(fā)器：

       查看觸發(fā)器是指數(shù)據(jù)庫中已存在的觸發(fā)器的定義，狀態(tài)，語法等信息。我們可以使用SHOW TRIGGERS 和在TRIGGERS表中查看觸發(fā)器信息。

              （1）SHOW TRIGGERS

              （2）在information_schema數(shù)據(jù)庫的TRIGGERS表中查看觸發(fā)器信息：

                                select * from 'information_schema'.'TRIGGERS' where 'TEIGGER_NAME'='ins_sum'

以上就是自己目前所交接觸發(fā)器的基本操作，隨著以后深入的了解，還會進一步學習觸發(fā)器。