二、實踐：
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)
