一、理论：
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: [email protected]character_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: [email protected]character_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: [email protected]character_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: [email protected]character_set_client: utf8collation_connection: utf8_general_ci Database Collation: utf8_general_ci4 rows in set (0.01 sec)版权声明：本博客原创文章欢迎转载，请转载的朋友最好注明出处，谢谢大家。