【MySQL 12】觸發器

     觸發器是一種特殊類型的預存程序，它不同於先前的我們所介紹的預存程序。觸發器主要是透過事件進行觸發被自動呼叫執行的。而預存程序可以透過預存程序的名稱被呼叫。
      觸發器的主要功能是能夠實現比參考完整性更為複雜的兩張表或多張表之間的資料的完整性和一致性，從而確保表中的資料的變更符合資料庫設計者所確定的業務規則。 
      觸發器對錶進行插入、更新、刪除的時候會自動執行的特殊儲存程序。觸發器一般用在check約束更複雜的約束上面。 
      觸發器和普通的儲存程序之間的差異是：觸發器是當對某一個表格進行操作時。諸如：update、insert、delete這些操作的時候，系統會自動呼叫執行該表上對應的觸發器。 
      SQL Server 2005中觸發器可分為兩類：DML觸發器和DDL觸發器，其中DDL觸發器它們會影響多種資料定義語言語句而激發，這些語句有create、alter、drop語句。

DML觸發器分為：

1、 after觸發器（之後觸發） 
      a、 insert觸發器 
     器（之前觸發） 
3 、區別 
      after觸發器：要求只有執行某一操作insert、update、delete之後觸發器才會被觸發，且只能定義在表上。 
      instead of觸發器：表示並未執行其定義的操作（insert、update、delete）而僅是執行觸發器本身。既可以在表上定義instead of觸發器，也可以在視圖上定義。

inserted 表和 deleted 表

   在使用觸發器時，SQL Server會為每個觸發器建立兩個特殊的臨時表，即inserted表和deleted表。這兩個表儲存在記憶體中，與建立觸發器的表具有相同的結構，由系統維護和管理，不允許使用者對其進行修改。每個觸發器只能存取自己的臨時表，觸發器執行完畢以後，兩表會自動釋放。 

      （1）inserted表用於儲存insert或update語句所影響的行的副本。當執行insert或update操作時，新的資料行同時被加入到啟動觸發器的基本表和inserted表中。 

      （2）deleted表用於儲存delete或update語句所影響的行的副本。當執行delete或update操作時，指定的原始資料行會從基本表中刪除，然後被轉移到deleted表中。一般來說，在基本表和deleted表中不會存在相同的資料行。 
       說明： 
      update操作時分為兩個步驟：將基本表中修改的原始資料行轉移到deleted表中，然後將修改後的新資料行複製到基本表中。也就是說，對於update操作，deleted表中存放的是修改之前的舊值，inserted表中存放的是修改之後的新值。

觸發器創建語法四要素：

1.监视地点(table) 
2.监视事件(insert/update/delete) 
3.触发时间(after/before) 
4.触发事件(insert/update/delete)

語法：

delimiter &&create trigger trigger
Nameafter/before insert/update/delete on 表名
for each row   #这句话在mysql是固定的
beginsql语句;
end&&

商品表

mysql> create table g(
    -> id int auto_increment primary key,    -> name varchar(10),    -> num int    -> );

訂單表

mysql> create table o(
    -> idd int auto_increment primary key,    -> gid int,    -> much int    -> );

插入商品：

mysql> insert into g (name,num) value ('juzi',20);mysql> select * from g;
+----+------+------+| id | name | num  |
+----+------+------+|  3 | juzi |   20 |
+----+------+------+

訂單表

insert into o(gid,much) values(1,3);

插入商品：

update g set num=num-3 where id=1;

如果我們在沒賣了3個商品1，我們需要做兩件事

1.往訂單表插入一條記錄

delimiter &
mysql> create trigger trg1
    -> after insert on o    -> for each row    -> begin    -> update g set num = num -3 where id = 1;    -> end&

2.更新商品表商品1的剩餘數量

insert into o(gid,much) values(1,3)$

創建觸發器：

create trigger tg2 
after insert on o 
for each row 
begin 
update g set num=num-new.much where id=new.gid;(注意此处和第一个触发器的不同) 
end$

結果：

會發現商品1的數量變成7了，說明在我們插入一條訂單的時候，觸發器自動幫我們做了更新操作。

但現在會有一個問題，因為我們觸發器裡面num和id都是寫死的，所以不管我們買哪個商品，最終更新的都是商品1的數量。例如：我們往訂單表再插入一筆記錄：insert into o(gid,much) values(2,3),執行完後會發現商品1的數量變4了，而商品2的數量沒變，這樣顯然不是我們想要的結果。我們需要改改我們之前建立的觸發器。

我們如何在觸發器引用行的值，也就是說我們要得到我們新插入的訂單記錄中的gid或much的值。

對於insert而言，新插入的行用new來表示，行中的每一列的值用new.列名來表示。

所以現在我們可以這樣來改我們的觸發器

create trigger tg3
after delete on o
for each row
begin
update g set num = num + old.much where id = old.gid;(注意这边的变化)
end$

第二個觸發器創建完畢，我們先把第一個觸發器刪除

drop trigger tg1$

再來測試一下，插入一條訂單記錄：insert into o(gid,much) values(2,3)$

執行完發現商品2的數量變為7了，現在就對了。

現在還存在兩種情況：

1.当用户撤销一个订单的时候，我们这边直接删除一个订单，我们是不是需要把对应的商品数量再加回去呢？

2.当用户修改一个订单的数量时，我们触发器修改怎么写?

我们先分析一下第一种情况：

监视地点：o表

监视事件：delete

触发时间：after

触发事件：update

对于delete而言：原本有一行,后来被删除，想引用被删除的这一行，用old来表示，old.列名可以引用被删除的行的值。

那我们的触发器就该这样写：

create trigger tg3
after delete on o
for each row
begin
update g set num = num + old.much where id = old.gid;(注意这边的变化)
end$

创建完毕。

再执行

delete from o where oid = 2$

会发现商品2的数量又变为10了。

第二种情况：

监视地点：o表

监视事件：update

触发时间：after

触发事件：update

对于update而言：被修改的行，修改前的数据，用old来表示，old.列名引用被修改之前行中的值；

修改的后的数据，用new来表示，new.列名引用被修改之后行中的值。

那我们的触发器就该这样写：

create trigger tg4
after update on o
for each row
begin
update g set num = num+old.much-new.much where id = old/new.gid;
end$

先把旧的数量恢复再减去新的数量就是修改后的数量了。

我们来测试下：先把商品表和订单表的数据都清掉，易于测试。

假设我们往商品表插入三个商品，数量都是10，

买3个商品1：

insert into o(gid,much) values(1,3)$

这时候商品1的数量变为7；

我们再修改插入的订单记录:

update o set much = 5 where oid = 1$

我们变为买5个商品1，这时候再查询商品表就会发现商品1的数量只剩5了，说明我们的触发器发挥作用了。

以上就是 【MySQL 12】触发器的内容，更多相关内容请关注PHP中文网（www.php.cn）！