Maison >base de données >tutoriel mysql >mysql学习记录(十二)--触发器_MySQL
一、理论:
1.触发器不能使用采用call的动态sql,不能直接返回数据到客户端
2.不能在触发器中使用开始或者结束事务的语句
3.触发器只能在支持事务的表中回滚
二、实践:
mysql> use test1; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> 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: 1 mysql> 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: begin insert into tri_demo(note) values ('before insert'); end Timing: BEFORE Created: NULL sql_mode: Definer: root@localhost character_set_client: utf8 collation_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@localhost character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: utf8_general_ci *************************** 3. row *************************** Trigger: upd_film_bef Event: UPDATE Table: film Statement: begin insert into tri_demo(note) values ('before update'); end Timing: BEFORE Created: NULL sql_mode: Definer: root@localhost character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: utf8_general_ci *************************** 4. row *************************** Trigger: upd_film_aft Event: UPDATE Table: film Statement: begin insert into tri_demo(note) values ('after update'); end Timing: AFTER Created: NULL sql_mode: Definer: root@localhost character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: utf8_general_ci 4 rows in set (0.01 sec)