mysql trigger 觸發器

標籤：alter   let   begin   ext   欄位表   sql_mode   準備   trigger   error:   

建立觸發器：CREATE [DEFINER = {user|CURRENT_USER}]TRIGGER trigger_nametrigger_time trigger_eventON tbl_name FOR EACH ROWtrigger_body文法中：trigger_name：觸發器的名稱，不能與已經存在的觸發器重複；trigger_time：{ BEFORE | AFTER }，表示在事件之前或之後觸發；trigger_event:：{ INSERT |UPDATE | DELETE }，觸發該觸發器的具體事件；tbl_name：該觸發器作用在tbl_name上；執行個體：建立簡單確發器<1> 準備學生表和學生數目統計表mysql> CREATE TABLE student_info (    -> stu_no INT(11) NOT NULL AUTO_INCREMENT,    -> stu_name VARCHAR(255) DEFAULT NULL,    -> PRIMARY KEY (stu_no)    -> );mysql> CREATE TABLE student_count(    -> student_count INT(11) DEFAULT 0    -> );插入一條資料:mysql> INSERT INTO student_count VALUES(0);<2> 建立簡單觸發器，在向學生表INSERT資料時，學生數增加，DELETE學生時，學生數減少http://blog.csdn.net/goskalrie/article/details/53020631mysql> CREATE TRIGGER trigger_student_count_insert     -> AFTER INSERT    -> ON student_count FOR EACH ROW    -> UPDATE student_count SET student_count=student_count+1;mysql> CREATE TRIGGER trigger_student_count_insert    -> AFTER INSERT    -> ON student_info FOR EACH ROW    -> UPDATE student_count SET student_count=student_count+1;mysql> CREATE TRIGGER trigger_student_count_delete    -> AFTER DELETE    -> ON student_info FOR EACH ROW    -> UPDATE student_count SET student_count=student_count-1;<3> INSERT、DELETE資料，查看觸發器是否正常工作mysql> INSERT INTO student_info VALUES(NULL,‘xiaoc‘),(NULL,‘xiaoz‘),(NULL,‘xionan‘);mysql> select * from student_info;+--------+----------+| stu_no | stu_name |+--------+----------+|      1 | xiaoc    ||      2 | xiaoz    ||      3 | xionan   |+--------+----------+3 rows in set (0.00 sec)mysql> desc student_info;+----------+--------------+------+-----+---------+----------------+| Field    | Type         | Null | Key | Default | Extra          |+----------+--------------+------+-----+---------+----------------+| stu_no   | int(11)      | NO   | PRI | NULL    | auto_increment || stu_name | varchar(255) | YES  |     | NULL    |                |+----------+--------------+------+-----+---------+----------------+2 rows in set (0.00 sec)刪除確發器，修改確發器:觸發器會隨著表的刪除被刪除！查看觸發器:show triggers;正解版：mysql> CREATE TABLE student_info (    ->     ->   stu_no INT(11) NOT NULL AUTO_INCREMENT,    ->     ->   stu_name VARCHAR(255) DEFAULT NULL,    ->     ->   PRIMARY KEY (stu_no)    ->     -> );Query OK, 0 rows affected (0.22 sec)mysql> CREATE TABLE student_count (    ->     ->   student_count INT(11) DEFAULT 0    ->     -> );Query OK, 0 rows affected (0.24 sec)mysql> INSERT INTO student_count VALUES(0);Query OK, 1 row affected (0.14 sec)mysql> CREATE TRIGGER trigger_student_count_insert    ->     -> AFTER INSERT    ->     -> ON student_info FOR EACH ROW    ->     -> UPDATE student_count SET student_count=student_count+1;Query OK, 0 rows affected (0.09 sec)mysql> CREATE TRIGGER trigger_student_count_delete    ->     -> AFTER DELETE    ->     -> ON student_info FOR EACH ROW    ->     -> UPDATE student_count SET student_count=student_count-1;Query OK, 0 rows affected (0.14 sec)mysql> INSERT INTO student_info VALUES(NULL,‘xiaoc‘),(NULL,‘xiaoz‘),(NULL,‘xionan‘);Query OK, 3 rows affected (0.05 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> select * from student_info;+--------+----------+| stu_no | stu_name |+--------+----------+|      1 | xiaoc    ||      2 | xiaoz    ||      3 | xionan   |+--------+----------+3 rows in set (0.00 sec)mysql> select * from student_count;+---------------+| student_count |+---------------+|             3 |+---------------+1 row in set (0.00 sec)mysql> delete from student_info where stu_name in (‘xionan‘,‘xiaoc‘);Query OK, 2 rows affected (0.06 sec)mysql> select * from student_count;+---------------+| student_count |+---------------+|             1 |+---------------+1 row in set (0.00 sec)mysql> insert into student_info values(null,‘xiaol‘);Query OK, 1 row affected (0.06 sec)mysql> select * from student_info;+--------+----------+| stu_no | stu_name |+--------+----------+|      2 | xiaoz    ||      4 | xiaol    |+--------+----------+2 rows in set (0.00 sec)mysql> select * from student_count;+---------------+| student_count |+---------------+|             2 |+---------------+1 row in set (0.00 sec)可以看到無論是INSERT還是DELETE學生，學生數目都會跟變化的。建立包含多條執行語句的觸發器:在trigger_body中可以執行多條SQL語句，此時的trigger_body需要使用BEGIN和END做為開始和結束的標誌：CREATE    [DEFINER = { user | CURRENT_USER }]    TRIGGER trigger_name    trigger_time trigger_eventON tbl_name FOR EACH ROWBEGINtrigger_statementEND;樣本2，建立包含多條執行語句的觸發器：(刪除觸發器)mysql> show triggers\G;*************************** 1. row ***************************             Trigger: trigger_student_count_insert               Event: INSERT               Table: student_info           Statement: UPDATE student_count SET student_count=student_count+1              Timing: AFTER             Created: NULL            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION             Definer: [email protected]character_set_client: utf8collation_connection: utf8_general_ci  Database Collation: latin1_swedish_ci*************************** 2. row ***************************             Trigger: trigger_student_count_delete               Event: DELETE               Table: student_info           Statement: UPDATE student_count SET student_count=student_count-1              Timing: AFTER             Created: NULL            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION             Definer: [email protected]character_set_client: utf8collation_connection: utf8_general_ci  Database Collation: latin1_swedish_ci2 rows in set (0.00 sec)ERROR: No query specifiedmysql> DROP TRIGGER trigger_student_count_insert;Query OK, 0 rows affected (0.10 sec)mysql> DROP TRIGGER trigger_student_count_delete;Query OK, 0 rows affected (0.00 sec)mysql> show triggers;Empty set (0.00 sec)依然沿用上面的例子中的表，對student_count表做如下變更：增加student_class欄位表示具體年級的學生數，其中0表示全年級，1代表1年級……；同樣學生表中也增加該欄位。清空兩個表中的所有資料。1:對student_count表增加student_class欄位：mysql> show create table student_count;+---------------+-------------------------------------------------------------------------------------------------------------+| Table         | Create Table                                                                                                |+---------------+-------------------------------------------------------------------------------------------------------------+| student_count | CREATE TABLE `student_count` (  `student_count` int(11) DEFAULT ‘0‘) ENGINE=InnoDB DEFAULT CHARSET=latin1 |+---------------+-------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> alter table student_count add student_class int(11) default ‘0‘;Query OK, 0 rows affected (0.33 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show create table student_count;+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------+| Table         | Create Table                                                                                                                                       |+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------+| student_count | CREATE TABLE `student_count` (  `student_count` int(11) DEFAULT ‘0‘,  `student_class` int(11) DEFAULT ‘0‘) ENGINE=InnoDB DEFAULT CHARSET=latin1 |+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)<1> 刪除上例中的兩個觸發器，初始化student_count表中資料，插入三條資料(0,0),(1,0),(2,0)表示全年級、一年級、二年級的初始人數都是0；mysql> update student_count set student_count=0;Query OK, 1 row affected (0.07 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from student_count;+---------------+---------------+| student_count | student_class |+---------------+---------------+|             0 |             0 |+---------------+---------------+1 row in set (0.00 sec)mysql> insert student_count values(1,0),(2,0);Query OK, 2 rows affected (0.07 sec)Records: 2  Duplicates: 0  Warnings: 0mysql> select * from student_count;+---------------+---------------+| student_count | student_class |+---------------+---------------+|             0 |             0 ||             1 |             0 ||             2 |             0 |+---------------+---------------+3 rows in set (0.00 sec)<2> 建立觸發器，在INSERT時首先增加學生總人數，然後判斷新增的學生是幾年級的，再增加對應年級的學生總數：mysql> select * from student_info;+--------+----------+| stu_no | stu_name |+--------+----------+|      2 | xiaoz    ||      4 | xiaol    |+--------+----------+2 rows in set (0.00 sec)mysql> delete from student_info where stu_name=‘xiaoz‘;Query OK, 1 row affected (0.03 sec)mysql> delete from student_info where stu_name=‘xiaol‘;Query OK, 1 row affected (0.06 sec)mysql> select * from student_info;Empty set (0.00 sec)上面是先清理一下資料：欄位的增加有誤，順序有誤:mysql> alter table student_count drop student_count;Query OK, 0 rows affected (0.36 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc student_count;+---------------+---------+------+-----+---------+-------+| Field         | Type    | Null | Key | Default | Extra |+---------------+---------+------+-----+---------+-------+| student_class | int(11) | YES  |     | 0       |       |+---------------+---------+------+-----+---------+-------+1 row in set (0.00 sec)mysql> alter table student_count add student_count int(11) default ‘0‘;Query OK, 0 rows affected (0.41 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show create table student_count;+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------+| Table         | Create Table                                                                                                                                       |+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------+| student_count | CREATE TABLE `student_count` (  `student_class` int(11) DEFAULT ‘0‘,  `student_count` int(11) DEFAULT ‘0‘) ENGINE=InnoDB DEFAULT CHARSET=latin1 |+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> delete from student_count where student_class=0;Query OK, 3 rows affected (0.06 sec)重新插入資料：mysql> insert student_count values(0,0),(1,0),(2,0);Query OK, 3 rows affected (0.08 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> select * from student_count;+---------------+---------------+| student_class | student_count |+---------------+---------------+|             0 |             0 ||             1 |             0 ||             2 |             0 |+---------------+---------------+3 rows in set (0.00 sec)http://blog.csdn.net/goskalrie/article/details/53020631對student_count表做如下變更：增加student_class欄位表示具體年級的學生數，其中0表示全年級，1代表1年級……；同樣學生表中也增加該欄位。清空兩個表中的所有資料。mysql> alter table student_info add column student_class int//Query OK, 0 rows affected (1.31 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc student_info//+---------------+--------------+------+-----+---------+----------------+| Field         | Type         | Null | Key | Default | Extra          |+---------------+--------------+------+-----+---------+----------------+| stu_no        | int(11)      | NO   | PRI | NULL    | auto_increment || stu_name      | varchar(255) | YES  |     | NULL    |                || student_class | int(11)      | YES  |     | NULL    |                |+---------------+--------------+------+-----+---------+----------------+3 rows in set (0.00 sec)建立觸發器，在INSERT時首先增加學生總人數，然後判斷增加學生總人數，然後判斷新增的學生幾年級的，再增加對應年級的學生總數。mysql> delimiter $$mysql> create trigger trigger_student_count_insert    -> after insert    -> on student_info for each row    -> begin    -> update student_count set student_count=student_count+1 where student_class=0;    -> update student_count set student_count=student_count+1 where student_class=new.student_class;    -> end    -> $$Query OK, 0 rows affected (0.09 sec)mysql> delimiter;建立觸發器，在DELETE時首先減少學生總人數，然後判斷刪除的學生是幾年級的，再減少對應年級的學生總數；mysql> DELIMITER $$mysql> mysql> CREATE TRIGGER trigger_student_count_delete    ->     -> AFTER DELETE    ->     -> ON student_info FOR EACH ROW    ->     -> BEGIN    ->     -> UPDATE student_count SET student_count=student_count-1 WHERE student_class=0;    ->     -> UPDATE student_count SET student_count=student_count-1 WHERE student_class= OLD.student_class;    ->     -> END    ->     -> $$Query OK, 0 rows affected (0.15 sec)mysql> mysql> DELIMITER ;mysql> desc student_info//+---------------+--------------+------+-----+---------+----------------+| Field         | Type         | Null | Key | Default | Extra          |+---------------+--------------+------+-----+---------+----------------+| stu_no        | int(11)      | NO   | PRI | NULL    | auto_increment || stu_name      | varchar(255) | YES  |     | NULL    |                || student_class | int(11)      | YES  |     | NULL    |                |+---------------+--------------+------+-----+---------+----------------+3 rows in set (0.00 sec)向學生表中分別插入多條不同年級的學生資訊，查看觸發器是否起做用：mysql>  INSERT INTO student_info VALUES(NULL,‘AAA‘,1),(NULL,‘BBB‘,1),(NULL,‘CCC‘,2),(NULL,‘DDD‘,2),(NULL,‘ABB‘,1),(NULL,‘ACC‘,1);Query OK, 6 rows affected (0.12 sec)Records: 6  Duplicates: 0  Warnings: 0mysql> select * from student_info;+--------+----------+---------------+| stu_no | stu_name | student_class |+--------+----------+---------------+|      1 | AAA      |             1 ||      2 | BBB      |             1 ||      3 | CCC      |             2 ||      4 | DDD      |             2 ||      5 | ABB      |             1 ||      6 | ACC      |             1 |+--------+----------+---------------+6 rows in set (0.00 sec)mysql> select * from student_count;+---------------+---------------+| student_class | student_count |+---------------+---------------+|             1 |             4 ||             2 |             2 ||             0 |             6 |+---------------+---------------+3 rows in set (0.00 sec)可以看到，總共插入6條資料，學生總數是6，1年級4個，2年級2個，trigger正確執行。從學生表中分別岀除多條不同年級的學生資訊，查看觸發器是否起作用：mysql> delete from student_info where stu_name like ‘A%‘;Query OK, 3 rows affected (0.15 sec)mysql> SELECT * FROM student_info;+--------+----------+---------------+| stu_no | stu_name | student_class |+--------+----------+---------------+|      2 | BBB      |             1 ||      3 | CCC      |             2 ||      4 | DDD      |             2 |+--------+----------+---------------+3 rows in set (0.00 sec)mysql> select * from student_count;+---------------+---------------+| student_class | student_count |+---------------+---------------+|             1 |             1 ||             2 |             2 ||             0 |             3 |+---------------+---------------+3 rows in set (0.00 sec)從學生表中將姓名以A開頭的學生資訊刪除，學生資訊刪除的同時，數量表也跟隨變化。在上面的樣本中，使用了三個新的關鍵字：DELIMITER、NEW、OLD，這三個關鍵字在官網上“觸發器文法”一節中都有介紹

 

mysql trigger 觸發器