mysql 学习记录（十二）--触发器

一、理论：
1.触发器不能使用采用call的动态sql,不能直接返回数据到客户端
2.不能在触发器中使用开始或者结束事务的语句
3.触发器只能在支持事务的表中回滚

二、实践：

mysql> use test1;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> create table film_text(    -> film_id int(11),    -> title varchar(20),    -> description varchar(180)    -> ) engine = innodb charset = utf8 ;Query OK, 0 rows affected (0.05 sec)mysql> create table film(    -> film_id int(11),    -> title varchar(20),    -> description varchar(180)    -> ) engine = innodb charset = utf8 ;Query OK, 0 rows affected (0.02 sec)mysql> CREATE TABLE `tri_demo` (    ->   `id`  int(11) auto_increment not null primary key,    ->   `note` varchar(50) DEFAULT NULL    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;Query OK, 0 rows affected (0.03 sec)mysql> create trigger ins_film_bef    -> before insert on film for each row begin    -> insert into tri_demo(note) values ('before insert');    -> end;    -> $$Query OK, 0 rows affected (0.02 sec)mysql> create trigger ins_film_aft    -> after insert on film for each row begin    -> insert into film_text(title) values ('after insert');    -> end;    -> $$Query OK, 0 rows affected (0.02 sec)mysql> create trigger upd_film_bef    -> before update on film for each row begin    -> insert into tri_demo(note) values ('before update');    -> end;    -> $$Query OK, 0 rows affected (0.00 sec)mysql> create trigger upd_film_aft    -> after update on film for each row begin    -> insert into tri_demo(note) values ('after update');    -> end;    -> $$Query OK, 0 rows affected (0.02 sec)mysql> delimiter ;mysql> insert into film values ( 1,'film_name','film_description');Query OK, 1 row affected (0.00 sec)mysql> select * from film;+---------+-----------+------------------+| film_id | title     | description      |+---------+-----------+------------------+|       1 | film_name | film_description |+---------+-----------+------------------+mysql> select * from tri_demo;+----+---------------+| id | note          |+----+---------------+|  1 | before insert |+----+---------------+mysql> update film set title='film_update' where film_id = 1;Query OK, 1 row affected, 1 warning (0.01 sec)Rows matched: 1  Changed: 1  Warnings: 1mysql> select * from film;+---------+--------------------+--------------------------+| film_id | title              | description              |+---------+--------------------+--------------------------+|       1 | film_update        | film_description         |+---------+--------------------+--------------------------+1 rows in set (0.00 sec)mysql> select * from tri_demo;+----+---------------+| id | note          |+----+---------------+|  1 | before insert ||  2 | before update ||  3 | after update  |+----+---------------+3 rows in set (0.00 sec)mysql> show triggers \G;*************************** 1. row ***************************             Trigger: ins_film_bef               Event: INSERT               Table: film           Statement: begininsert into tri_demo(note) values ('before insert');end              Timing: BEFORE             Created: NULL            sql_mode:              Definer: root@localhostcharacter_set_client: utf8collation_connection: utf8_general_ci  Database Collation: utf8_general_ci*************************** 2. row ***************************             Trigger: ins_film_aft               Event: INSERT               Table: film           Statement: begin     insert into film_text(title) values ('after insert');     end              Timing: AFTER             Created: NULL            sql_mode:              Definer: root@localhostcharacter_set_client: utf8collation_connection: utf8_general_ci  Database Collation: utf8_general_ci*************************** 3. row ***************************             Trigger: upd_film_bef               Event: UPDATE               Table: film           Statement: begininsert into tri_demo(note) values ('before update');end              Timing: BEFORE             Created: NULL            sql_mode:              Definer: root@localhostcharacter_set_client: utf8collation_connection: utf8_general_ci  Database Collation: utf8_general_ci*************************** 4. row ***************************             Trigger: upd_film_aft               Event: UPDATE               Table: film           Statement: begininsert into tri_demo(note) values ('after update');end              Timing: AFTER             Created: NULL            sql_mode:              Definer: root@localhostcharacter_set_client: utf8collation_connection: utf8_general_ci  Database Collation: utf8_general_ci4 rows in set (0.01 sec)