mysql> create table trigger_time (exec_time time);
Query OK, 0 rows affected (0.00 sec)
mysql> select * from department;
+-------+-----------+-----------------+-------------+
| d_id | d_name | function | address |
+-------+-----------+-----------------+-------------+
| 10001 | 科研部 | 新产品科研 | 3号楼5层 |
| 10002 | 生成部 | 主管生产 | 5号楼1层 |
+-------+-----------+-----------------+-------------+
2 rows in set (0.00 sec)
mysql> create trigger dept_trig1 before insert on department for each row
-> insert into trigger_time values(now());
Query OK, 0 rows affected (0.00 sec)
mysql> insert into department values(10003,'销售部','负责产品销售','1号楼销售大厅');
Query OK, 1 row affected (0.00 sec)
mysql> select * from trigger_time;
+-----------+
| exec_time |
+-----------+
| 19:52:11 |
+-----------+
1 row in set (0.00 sec)
创建有多个执行语句的触发器
mysql> delimiter &&
mysql> create trigger dept_trig2 after delete on department for each row
-> BEGIN
-> insert into trigger_time values(now());
-> insert into trigger_time values('22:01:01');
-> END
-> &&
mysql> delimiter ;
mysql> delete from department where d_id='10003';
Query OK, 1 row affected (0.01 sec)
mysql> select * from trigger_time;
+-----------+
| exec_time |
+-----------+
| 19:52:11 |
| 20:05:00 |
| 22:01:01 |
+-----------+
3 rows in set (0.00 sec)
查看触发器
show triggers 语句查看触发器信息
mysql> show triggers \G;
*************************** 1. row ***************************
Trigger: dept_trig1
Event: INSERT
Table: department
Statement: insert into trigger_time values(now())
Timing: BEFORE
Created: 2019-07-25 19:49:11.55
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Definer: skip-grants user@skip-grants host
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
*************************** 2. row ***************************
Trigger: dept_trig2
Event: DELETE
Table: department
Statement: BEGIN
insert into trigger_time values(now());
insert into trigger_time values('22:01:01');
END
Timing: AFTER
Created: 2019-07-25 20:02:41.19
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Definer: skip-grants user@skip-grants host
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
2 rows in set (0.00 sec)
在triggers表中查看触发器信息
mysql> select * from information_schema.triggers where trigger_name='dept_trig2' \G
*************************** 1. row ***************************
TRIGGER_CATALOG: def
TRIGGER_SCHEMA: example
TRIGGER_NAME: dept_trig2
EVENT_MANIPULATION: DELETE
EVENT_OBJECT_CATALOG: def
EVENT_OBJECT_SCHEMA: example
EVENT_OBJECT_TABLE: department
ACTION_ORDER: 1
ACTION_CONDITION: NULL
ACTION_STATEMENT: BEGIN
insert into trigger_time values(now());
insert into trigger_time values('22:01:01');
END
ACTION_ORIENTATION: ROW
ACTION_TIMING: AFTER
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
ACTION_REFERENCE_OLD_ROW: OLD
ACTION_REFERENCE_NEW_ROW: NEW
CREATED: 2019-07-25 20:02:41.19
SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
DEFINER: skip-grants user@skip-grants host
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: utf8_general_ci
1 row in set (0.12 sec)
触发器的使用
mysql> create table trigger_test (id int(4) primary key not null auto_increment, info varchar(20));
Query OK, 0 rows affected (0.01 sec)
mysql> create trigger before_insert before insert on department for each row insert into trigger_test values(null,'before insertt');
Query OK, 0 rows affected (0.01 sec)
mysql> create trigger after_insert after insert on department for each row insert into trigger_test values(null,'after insert');;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into department values(10003,'销售部','负责产品销售','1号楼销售大厅');
Query OK, 1 row affected (0.00 sec)
mysql> select * from trigger_test;
+----+---------------+
| id | info |
+----+---------------+
| 1 | before insert |
| 2 | after insert |
+----+---------------+
2 rows in set (0.00 sec)