MySQL create trigger语句
• create trigger语句用来创建一个触发器，触发器的作用是当表上有对应SQL语句发生时，则触发执行
• 触发器创建时需要指定对应的表名tbl_name
delimiter // create trigger simple_trigger after update on teacher for each row begin insert into teacher_history values(new.id,old.name,new.name,old.dept_id,new.dept_id,now()); end; // delimiter ; delimiter // create trigger simple_trigger2 after insert on teacher for each row begin insert into teacher_history values(new.id,null,new.name,null,new.dept_id,now()); end; // delimiter ;
mysql> use course; 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> select * from teacher; +----+-----------+---------+ | id | name | dept_id | +----+-----------+---------+ | 1 | Zhang san | 1 | | 2 | Li si | 1 | | 3 | Wang wu | 2 | | 4 | Liu liu | 3 | | 5 | Ding qi | 3 | | 6 | a | 1 | | 7 | b | 1 | +----+-----------+---------+ 7 rows in set (0.02 sec) mysql> update teacher set name='abc' where id=1; Query OK, 1 row affected (0.10 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from teacher; +----+---------+---------+ | id | name | dept_id | +----+---------+---------+ | 1 | abc | 1 | | 2 | Li si | 1 | | 3 | Wang wu | 2 | | 4 | Liu liu | 3 | | 5 | Ding qi | 3 | | 6 | a | 1 | | 7 | b | 1 | +----+---------+---------+ 7 rows in set (0.00 sec) mysql> create table teacher_history(id int,old_name varchar(64),new_name varchar(64),old_deptid int,new_deptid int,tstamp timestamp); Query OK, 0 rows affected (0.16 sec) mysql> desc teacher_history; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | old_name | varchar(64) | YES | | NULL | | | new_name | varchar(64) | YES | | NULL | | | old_deptid | int(11) | YES | | NULL | | | new_deptid | int(11) | YES | | NULL | | | tstamp | timestamp | YES | | NULL | | +------------+-------------+------+-----+---------+-------+ 6 rows in set (0.02 sec) mysql> select * from teacher; +----+---------+---------+ | id | name | dept_id | +----+---------+---------+ | 1 | abc | 1 | | 2 | Li si | 1 | | 3 | Wang wu | 2 | | 4 | Liu liu | 3 | | 5 | Ding qi | 3 | | 6 | a | 1 | | 7 | b | 1 | +----+---------+---------+ 7 rows in set (0.00 sec) mysql> select now(); +---------------------+ | now() | +---------------------+ | 2020-04-21 10:54:39 | +---------------------+ 1 row in set (0.00 sec) mysql> delimiter // mysql> create trigger simple_trigger -> after update -> on teacher for each row -> begin -> insert into teacher_history -> values(new.id,old.name,new.name,old.dept_id,new.dept_id,now()); -> end; -> // Query OK, 0 rows affected (0.02 sec) mysql> delimiter ; mysql> select * from teacher; +----+---------+---------+ | id | name | dept_id | +----+---------+---------+ | 1 | abc | 1 | | 2 | Li si | 1 | | 3 | Wang wu | 2 | | 4 | Liu liu | 3 | | 5 | Ding qi | 3 | | 6 | a | 1 | | 7 | b | 1 | +----+---------+---------+ 7 rows in set (0.01 sec) mysql> select * from teacher_history; Empty set (0.01 sec) mysql> insert into teacher values(8,'a',1); Query OK, 1 row affected (0.07 sec) mysql> select * from teacher_history; Empty set (0.00 sec) mysql> update teacher set name='zhangsan' where id=1; Query OK, 1 row affected (0.09 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from teacher; +----+----------+---------+ | id | name | dept_id | +----+----------+---------+ | 1 | zhangsan | 1 | | 2 | Li si | 1 | | 3 | Wang wu | 2 | | 4 | Liu liu | 3 | | 5 | Ding qi | 3 | | 6 | a | 1 | | 7 | b | 1 | | 8 | a | 1 | +----+----------+---------+ 8 rows in set (0.00 sec) mysql> select * from teacher_history; +------+----------+----------+------------+------------+---------------------+ | id | old_name | new_name | old_deptid | new_deptid | tstamp | +------+----------+----------+------------+------------+---------------------+ | 1 | abc | zhangsan | 1 | 1 | 2020-04-21 10:58:21 | +------+----------+----------+------------+------------+---------------------+ 1 row in set (0.00 sec) mysql> update teacher set name='zhangsan2',dept_id=3 where id=2; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from teacher_history; +------+----------+-----------+------------+------------+---------------------+ | id | old_name | new_name | old_deptid | new_deptid | tstamp | +------+----------+-----------+------------+------------+---------------------+ | 1 | abc | zhangsan | 1 | 1 | 2020-04-21 10:58:21 | | 2 | Li si | zhangsan2 | 1 | 3 | 2020-04-21 10:59:55 | +------+----------+-----------+------------+------------+---------------------+ 2 rows in set (0.00 sec) mysql> update teacher set name='zhangsan3',dept_id=3; Query OK, 8 rows affected (0.01 sec) Rows matched: 8 Changed: 8 Warnings: 0 mysql> select * from teacher; +----+-----------+---------+ | id | name | dept_id | +----+-----------+---------+ | 1 | zhangsan3 | 3 | | 2 | zhangsan3 | 3 | | 3 | zhangsan3 | 3 | | 4 | zhangsan3 | 3 | | 5 | zhangsan3 | 3 | | 6 | zhangsan3 | 3 | | 7 | zhangsan3 | 3 | | 8 | zhangsan3 | 3 | +----+-----------+---------+ 8 rows in set (0.00 sec) mysql> select * from teacher_history; +------+-----------+-----------+------------+------------+---------------------+ | id | old_name | new_name | old_deptid | new_deptid | tstamp | +------+-----------+-----------+------------+------------+---------------------+ | 1 | abc | zhangsan | 1 | 1 | 2020-04-21 10:58:21 | | 2 | Li si | zhangsan2 | 1 | 3 | 2020-04-21 10:59:55 | | 1 | zhangsan | zhangsan3 | 1 | 3 | 2020-04-21 11:02:03 | | 2 | zhangsan2 | zhangsan3 | 3 | 3 | 2020-04-21 11:02:03 | | 3 | Wang wu | zhangsan3 | 2 | 3 | 2020-04-21 11:02:03 | | 4 | Liu liu | zhangsan3 | 3 | 3 | 2020-04-21 11:02:03 | | 5 | Ding qi | zhangsan3 | 3 | 3 | 2020-04-21 11:02:03 | | 6 | a | zhangsan3 | 1 | 3 | 2020-04-21 11:02:03 | | 7 | b | zhangsan3 | 1 | 3 | 2020-04-21 11:02:03 | | 8 | a | zhangsan3 | 1 | 3 | 2020-04-21 11:02:03 | +------+-----------+-----------+------------+------------+---------------------+ 10 rows in set (0.00 sec)
mysql> delete from teacher_history; Query OK, 10 rows affected (0.09 sec) mysql> show create table teacher_history; +-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | teacher_history | CREATE TABLE `teacher_history` ( `id` int(11) DEFAULT NULL, `old_name` varchar(64) DEFAULT NULL, `new_name` varchar(64) DEFAULT NULL, `old_deptid` int(11) DEFAULT NULL, `new_deptid` int(11) DEFAULT NULL, `tstamp` timestamp NULL DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> alter table teacher_history add primary key(id); Query OK, 0 rows affected (0.29 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> update teacher set name='zhangsan',dept_id=2 where id=1; Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from teacher_history; +----+-----------+----------+------------+------------+---------------------+ | id | old_name | new_name | old_deptid | new_deptid | tstamp | +----+-----------+----------+------------+------------+---------------------+ | 1 | zhangsan3 | zhangsan | 3 | 2 | 2020-04-21 11:08:34 | +----+-----------+----------+------------+------------+---------------------+ 1 row in set (0.00 sec) mysql> update teacher set name='zhangsan',dept_id=3 where id=1; ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' mysql> select * from teacher; +----+-----------+---------+ | id | name | dept_id | +----+-----------+---------+ | 1 | zhangsan | 2 | | 2 | zhangsan3 | 3 | | 3 | zhangsan3 | 3 | | 4 | zhangsan3 | 3 | | 5 | zhangsan3 | 3 | | 6 | zhangsan3 | 3 | | 7 | zhangsan3 | 3 | | 8 | zhangsan3 | 3 | +----+-----------+---------+ 8 rows in set (0.00 sec) mysql> select * from teacher_history; +----+-----------+----------+------------+------------+---------------------+ | id | old_name | new_name | old_deptid | new_deptid | tstamp | +----+-----------+----------+------------+------------+---------------------+ | 1 | zhangsan3 | zhangsan | 3 | 2 | 2020-04-21 11:08:34 | +----+-----------+----------+------------+------------+---------------------+ 1 row in set (0.00 sec) mysql> use information_schema; 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> show tables; +---------------------------------------+ | Tables_in_information_schema | +---------------------------------------+ | CHARACTER_SETS | | COLLATIONS | | COLLATION_CHARACTER_SET_APPLICABILITY | | COLUMNS | | COLUMN_PRIVILEGES | | COLUMN_STATISTICS | | ENGINES | | EVENTS | | FILES | | INNODB_BUFFER_PAGE | | INNODB_BUFFER_PAGE_LRU | | INNODB_BUFFER_POOL_STATS | | INNODB_CACHED_INDEXES | | INNODB_CMP | | INNODB_CMPMEM | | INNODB_CMPMEM_RESET | | INNODB_CMP_PER_INDEX | | INNODB_CMP_PER_INDEX_RESET | | INNODB_CMP_RESET | | INNODB_COLUMNS | | INNODB_DATAFILES | | INNODB_FIELDS | | INNODB_FOREIGN | | INNODB_FOREIGN_COLS | | INNODB_FT_BEING_DELETED | | INNODB_FT_CONFIG | | INNODB_FT_DEFAULT_STOPWORD | | INNODB_FT_DELETED | | INNODB_FT_INDEX_CACHE | | INNODB_FT_INDEX_TABLE | | INNODB_INDEXES | | INNODB_METRICS | | INNODB_SESSION_TEMP_TABLESPACES | | INNODB_TABLES | | INNODB_TABLESPACES | | INNODB_TABLESPACES_BRIEF | | INNODB_TABLESTATS | | INNODB_TEMP_TABLE_INFO | | INNODB_TRX | | INNODB_VIRTUAL | | KEYWORDS | | KEY_COLUMN_USAGE | | OPTIMIZER_TRACE | | PARAMETERS | | PARTITIONS | | PLUGINS | | PROCESSLIST | | PROFILING | | REFERENTIAL_CONSTRAINTS | | RESOURCE_GROUPS | | ROUTINES | | SCHEMATA | | SCHEMA_PRIVILEGES | | STATISTICS | | ST_GEOMETRY_COLUMNS | | ST_SPATIAL_REFERENCE_SYSTEMS | | TABLES | | TABLESPACES | | TABLE_CONSTRAINTS | | TABLE_PRIVILEGES | | TRIGGERS | | USER_PRIVILEGES | | VIEWS | | VIEW_ROUTINE_USAGE | | VIEW_TABLE_USAGE | +---------------------------------------+ 65 rows in set (0.01 sec) mysql> desc triggers; +----------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------+-------+ | TRIGGER_CATALOG | varchar(64) | NO | | NULL | | | TRIGGER_SCHEMA | varchar(64) | NO | | NULL | | | TRIGGER_NAME | varchar(64) | NO | | NULL | | | EVENT_MANIPULATION | enum('INSERT','UPDATE','DELETE') | NO | | NULL | | | EVENT_OBJECT_CATALOG | varchar(64) | NO | | NULL | | | EVENT_OBJECT_SCHEMA | varchar(64) | NO | | NULL | | | EVENT_OBJECT_TABLE | varchar(64) | NO | | NULL | | | ACTION_ORDER | int(10) unsigned | NO | | NULL | | | ACTION_CONDITION | binary(0) | YES | | NULL | | | ACTION_STATEMENT | longtext | NO | | NULL | | | ACTION_ORIENTATION | varchar(3) | NO | | | | | ACTION_TIMING | enum('BEFORE','AFTER') | NO | | NULL | | | ACTION_REFERENCE_OLD_TABLE | binary(0) | YES | | NULL | | | ACTION_REFERENCE_NEW_TABLE | binary(0) | YES | | NULL | | | ACTION_REFERENCE_OLD_ROW | varchar(3) | NO | | | | | ACTION_REFERENCE_NEW_ROW | varchar(3) | NO | | | | | CREATED | timestamp(2) | NO | | NULL | | | SQL_MODE | set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','NOT_USED_9','NOT_USED_10','NOT_USED_11','NOT_USED_12','NOT_USED_13','NOT_USED_14','NOT_USED_15','NOT_USED_16','NOT_USED_17','NOT_USED_18','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NOT_USED_29','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') | NO | | NULL | | | DEFINER | varchar(93) | NO | | NULL | | | CHARACTER_SET_CLIENT | varchar(64) | NO | | NULL | | | COLLATION_CONNECTION | varchar(64) | NO | | NULL | | | DATABASE_COLLATION | varchar(64) | NO | | NULL | | +----------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------+-------+ 22 rows in set (0.00 sec) mysql> select * from triggers where trigger_schema='course'; +-----------------+----------------+----------------+--------------------+----------------------+---------------------+--------------------+--------------+------------------+--------------------------------------------------------------------------------------------------------+--------------------+---------------+----------------------------+----------------------------+--------------------------+--------------------------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | TRIGGER_CATALOG | TRIGGER_SCHEMA | TRIGGER_NAME | EVENT_MANIPULATION | EVENT_OBJECT_CATALOG | EVENT_OBJECT_SCHEMA | EVENT_OBJECT_TABLE | ACTION_ORDER | ACTION_CONDITION | ACTION_STATEMENT | ACTION_ORIENTATION | ACTION_TIMING | ACTION_REFERENCE_OLD_TABLE | ACTION_REFERENCE_NEW_TABLE | ACTION_REFERENCE_OLD_ROW | ACTION_REFERENCE_NEW_ROW | CREATED | SQL_MODE | DEFINER | CHARACTER_SET_CLIENT | COLLATION_CONNECTION | DATABASE_COLLATION | +-----------------+----------------+----------------+--------------------+----------------------+---------------------+--------------------+--------------+------------------+--------------------------------------------------------------------------------------------------------+--------------------+---------------+----------------------------+----------------------------+--------------------------+--------------------------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | def | course | simple_trigger | UPDATE | def | course | teacher | 1 | NULL | begin insert into teacher_history values(new.id,old.name,new.name,old.dept_id,new.dept_id,now()); end | ROW | AFTER | NULL | NULL | OLD | NEW | 2020-04-21 10:56:39.48 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | root@localhost | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci | +-----------------+----------------+----------------+--------------------+----------------------+---------------------+--------------------+--------------+------------------+--------------------------------------------------------------------------------------------------------+--------------------+---------------+----------------------------+----------------------------+--------------------------+--------------------------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+ 1 row in set (0.00 sec) mysql> select * from triggers where trigger_schema='course'and EVENT_OBJECT_TABLE='teacher'; +-----------------+----------------+----------------+--------------------+----------------------+---------------------+--------------------+--------------+------------------+--------------------------------------------------------------------------------------------------------+--------------------+---------------+----------------------------+----------------------------+--------------------------+--------------------------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | TRIGGER_CATALOG | TRIGGER_SCHEMA | TRIGGER_NAME | EVENT_MANIPULATION | EVENT_OBJECT_CATALOG | EVENT_OBJECT_SCHEMA | EVENT_OBJECT_TABLE | ACTION_ORDER | ACTION_CONDITION | ACTION_STATEMENT | ACTION_ORIENTATION | ACTION_TIMING | ACTION_REFERENCE_OLD_TABLE | ACTION_REFERENCE_NEW_TABLE | ACTION_REFERENCE_OLD_ROW | ACTION_REFERENCE_NEW_ROW | CREATED | SQL_MODE | DEFINER | CHARACTER_SET_CLIENT | COLLATION_CONNECTION | DATABASE_COLLATION | +-----------------+----------------+----------------+--------------------+----------------------+---------------------+--------------------+--------------+------------------+--------------------------------------------------------------------------------------------------------+--------------------+---------------+----------------------------+----------------------------+--------------------------+--------------------------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | def | course | simple_trigger | UPDATE | def | course | teacher | 1 | NULL | begin insert into teacher_history values(new.id,old.name,new.name,old.dept_id,new.dept_id,now()); end | ROW | AFTER | NULL | NULL | OLD | NEW | 2020-04-21 10:56:39.48 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | root@localhost | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci | +-----------------+----------------+----------------+--------------------+----------------------+---------------------+--------------------+--------------+------------------+--------------------------------------------------------------------------------------------------------+--------------------+---------------+----------------------------+----------------------------+--------------------------+--------------------------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+ 1 row in set (0.01 sec) mysql> select current_user(); +----------------+ | current_user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec)
mysql> use course; Database changed mysql> delimiter // mysql> create trigger simple_trigger2 -> after insert -> on teacher for each row -> begin -> insert into teacher_history -> values(new.id,null,new.name,null,new.dept_id,now()); -> end; -> // Query OK, 0 rows affected (0.09 sec) mysql> delimiter ; mysql> mysql> insert into teacher values(10,'a',1); Query OK, 1 row affected (0.10 sec) mysql> select * from teacher_history; +----+-----------+----------+------------+------------+---------------------+ | id | old_name | new_name | old_deptid | new_deptid | tstamp | +----+-----------+----------+------------+------------+---------------------+ | 1 | zhangsan3 | zhangsan | 3 | 2 | 2020-04-21 11:08:34 | | 10 | NULL | a | NULL | 1 | 2020-04-21 11:21:41 | +----+-----------+----------+------------+------------+---------------------+ 2 rows in set (0.00 sec)
浙公网安备 33010602011771号