我的MYSQL學習心得（十二）
?
這一篇《我的MYSQL學習心得（二）》將會講解MYSQL的觸發(fā)器
?
觸發(fā)器是一個特殊的存儲過程，不同的是存儲過程要用CALL來調(diào)用，而觸發(fā)器不需要使用CALL
也不需要手工啟動，只要當一個預(yù)定義的事件發(fā)生的時候，就會被MYSQL自動調(diào)用。
創(chuàng)建觸發(fā)器
語法如下：
CREATE
TRIGGER
trigger_name trigger_time trigger_event
ON
tbl_name
FOR
EACH ROW trigger_stmt
觸發(fā)程序是與表有關(guān)的命名數(shù)據(jù)庫對象，當表上出現(xiàn)特定事件時，將激活該對象。
觸發(fā)程序與命名為tbl_name的表相關(guān)。tbl_name必須引用永久性表。不能將觸發(fā)程序與臨時表表或視圖關(guān)聯(lián)起來。
trigger_time是觸發(fā)程序的動作時間。它可以是BEFORE或AFTER，以指明觸發(fā)程序是在激活它的語句之前或之后觸發(fā)。
trigger_event指明了激活觸發(fā)程序的語句的類型。trigger_event可以是下述值之一：
·?????????INSERT：將新行插入表時激活觸發(fā)程序，例如，通過INSERT、LOAD DATA和REPLACE語句。
·?????????UPDATE：更改某一行時激活觸發(fā)程序，例如，通過UPDATE語句。
·?????????DELETE：從表中刪除某一行時激活觸發(fā)程序，例如，通過DELETE和REPLACE語句。
請注意，trigger_event與以表操作方式激活觸發(fā)程序的SQL語句并不很類似，這點很重要。
例如，關(guān)于INSERT的BEFORE觸發(fā)程序不僅能被INSERT語句激活，也能被 LOAD DATA 語句激活。
?
可能會造成混淆的例子之一是INSERT INTO .. ON DUPLICATE UPDATE ...語法：BEFORE INSERT觸發(fā)程序?qū)τ诿恳恍袑⒓せ睿蟾鶤FTER INSERT觸發(fā)程序，或BEFORE UPDATE和AFTER UPDATE觸發(fā)程序，具體情況取決于行上是否有重復(fù)鍵。
對于具有相同觸發(fā)程序動作時間和事件的給定表，不能有兩個觸發(fā)程序。
例如，對于某一表，不能有兩個 BEFORE UPDATE 觸發(fā)程序。
但可以有1個BEFORE UPDATE觸發(fā)程序和1個BEFORE INSERT觸發(fā)程序，或1個BEFORE UPDATE觸發(fā)程序和1個AFTER UPDATE觸發(fā)程序。
?
trigger_stmt是當觸發(fā)程序激活時執(zhí)行的語句。
如果你打算執(zhí)行多個語句，可使用BEGIN ... END復(fù)合語句結(jié)構(gòu)。這樣，就能使用存儲子程序中允許的相同語句。
?
創(chuàng)建一個單執(zhí)行語句的觸發(fā)器
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;
首先創(chuàng)建一個account表，表中有兩個字段，分別為：acct_num字段（定義為int類型）
amount字段（定義成浮點類型）；其次創(chuàng)建一個名為ins_sum的觸發(fā)器，觸發(fā)的條件是向數(shù)據(jù)表account插入數(shù)據(jù)之前，
對新插入的amount字段值進行求和計算
DECLARE
@num
INT
SET
@num
=
0
INSERT
INTO
account
VALUES
(
1
,
1.00
),(
2
,
2.00
)
SELECT
@num
首先創(chuàng)建一個account表，在向表account插入數(shù)據(jù)之前，計算所有新插入的account表的amount值之和，
觸發(fā)器的名稱為ins_num，條件是在向表插入數(shù)據(jù)之前觸發(fā)。
?
?
創(chuàng)建有多個執(zhí)行語句的觸發(fā)器，語法如下：
DELIMITER
|
CREATE
TRIGGER
testref BEFORE
INSERT
ON
test1
FOR
EACH ROW
BEGIN
INSERT
INTO
test2
SET
a2
=
NEW.a1;
DELETE
FROM
test3
WHERE
a3
=
NEW.a1;
UPDATE
test4
SET
b4
=
b4
+
1
WHERE
a4
=
NEW.a1;
END
|
查看觸發(fā)器
查看觸發(fā)器是指數(shù)據(jù)庫中已存在的觸發(fā)器的定義、狀態(tài)、語法信息等。
可以使用SHOW TRIGGERS 和在TRIGGERS 表中查看觸發(fā)器信息
SHOW TRIGGERS
?
Trigger
Event
Table
Statement Timing Created sql_mode Definer character_set_client collation_connection
Database
Collation
--
----- ------ ------- ------------------------ ------ ------- -------- -------------- -------------------- -------------------- ------------------
ins_sum
INSERT
account
set
@sum
=
@sum
+
new.amount BEFORE (
NULL
) root
@localhost
utf8 utf8_general_ci utf8_general_ci
EVENT表示激活觸發(fā)器的事件，這里的觸發(fā)事件為插入操作INSERT，TABLE表示激活觸發(fā)器的對象表，這里為account 表
Timing表示觸發(fā)器的時間，為插入之前（BEFORE）；Statement 表示觸發(fā)器執(zhí)行的操作，還有一些其他信息，比如SQL模式，觸發(fā)器的定義帳戶和字符集等
?
在TRIGGERS 表中查看觸發(fā)器信息
information_schema數(shù)據(jù)庫的TRIGGERS 表中，可以通過查詢查看觸發(fā)器信息
SELECT
*
FROM
`information_schema`.`TRIGGERS`
WHERE
`TRIGGER_NAME`
=
'
ins_sum
'
TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION
--
------------- -------------- ------------ ------------------ -------------------- ------------------- ------------------ ------------ ---------------- ------------------------ ------------------ ------------- -------------------------- -------------------------- ------------------------ ------------------------ ------- -------- -------------- -------------------- -------------------- ------------------
def school ins_sum
INSERT
def school account
0
(
NULL
)
set
@sum
=
@sum
+
new.amount ROW BEFORE (
NULL
) (
NULL
) OLD NEW (
NULL
) root
@localhost
utf8 utf8_general_ci utf8_general_ci
TRIGGER_SCHEMA 表示觸發(fā)器所在的數(shù)據(jù)庫
TRIGGER_NAME表示觸發(fā)器的名稱
EVENT_OBJECT_TABLE表示在哪個表上觸發(fā)
ACTION_STATEMENT 表示觸發(fā)器觸發(fā)的時候執(zhí)行的具體操作
ACTION_ORIENTATION是ROW，表示在每條記錄上都觸發(fā)
ACTION_TIMING表示觸發(fā)的時刻是BEFORE
刪除觸發(fā)器
使用DROP TRIGGER 語句可以刪除MYSQL中已經(jīng)定義的觸發(fā)器，刪除觸發(fā)器的基本語法
DROP
TRIGGER
[
schema_name.
]
trigger_name
其中（schema_name）是可選的
如果省略了schema（方案），將從當前方案中舍棄觸發(fā)程序。
刪除ins_sum觸發(fā)器
DROP
TRIGGER
`school`.`ins_sum`
觸發(fā)器ins_sum刪除成功
總結(jié)
對于相同的表，相同的事件只能創(chuàng)建一個觸發(fā)器，比如對表account創(chuàng)建了BEFORE INSERT觸發(fā)器
那么如果對表account再次創(chuàng)建一個BEFORE INSERT觸發(fā)器，MYSQL就會報錯，此時，只可以在表account上
創(chuàng)建AFTER INSERT或者BEFORE UPDATE類型的觸發(fā)器
?
?
如有不對的地方，歡迎大家拍磚o(∩_∩)o ?
?
2014-6-23補充
CREATE
TABLE
account(acct_num
INT
,amount
DECIMAL
(
10
,
2
),NUM
INT
);
CREATE
TRIGGER
ins_sum BEFORE
INSERT
ON
account
FOR
EACH ROW
UPDATE
`employee`
SET
`Age`
=
new.acct_num
+
1
WHERE
`ID`
=
new.acct_num ;
INSERT
INTO
account(acct_num,amount)
VALUES
(
1
,
2.00
);
SELECT
*
FROM
`employee`
?
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
