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)