mysql create triggers_MySQL create trigger语句

75 阅读 0 评论 50 点赞

我是靠谱客的博主会撒娇百合，最近开发中收集的这篇文章主要介绍mysql create triggers_MySQL create trigger语句，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

mysql> delete fromteacher_history;

Query OK,10 rows affected (0.09sec)

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.00sec)

mysql>alter table teacher_history add primary key(id);

Query OK,0 rows affected (0.29sec)

Records:0 Duplicates: 0 Warnings: 0mysql> update teacher set name='zhangsan',dept_id=2 where id=1;

Query OK,1 row affected (0.04sec)

Rows matched:1 Changed: 1 Warnings: 0mysql> select * fromteacher_history;+----+-----------+----------+------------+------------+---------------------+

+----+-----------+----------+------------+------------+---------------------+

| 1 | zhangsan3 | zhangsan | 3 | 2 | 2020-04-21 11:08:34 |

+----+-----------+----------+------------+------------+---------------------+

1 row in set (0.00sec)

mysql> update teacher set name='zhangsan',dept_id=3 where id=1;

ERROR1062 (23000): Duplicate entry '1' for key 'PRIMARY'mysql> select * fromteacher;+----+-----------+---------+

| 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.00sec)

mysql> select * fromteacher_history;+----+-----------+----------+------------+------------+---------------------+

+----+-----------+----------+------------+------------+---------------------+

| 1 | zhangsan3 | zhangsan | 3 | 2 | 2020-04-21 11:08:34 |

+----+-----------+----------+------------+------------+---------------------+

1 row in set (0.00sec)

mysql>use information_schema;

Reading table informationforcompletion of table and column names

You can turn offthis 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.01sec)

mysql>desc triggers;+----------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------+-------+

+----------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------+-------+

| 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 | |

22 rows in set (0.00sec)

mysql> select * from triggers where trigger_schema='course';+-----------------+----------------+----------------+--------------------+----------------------+---------------------+--------------------+--------------+------------------+--------------------------------------------------------------------------------------------------------+--------------------+---------------+----------------------------+----------------------------+--------------------------+--------------------------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+

+-----------------+----------------+----------------+--------------------+----------------------+---------------------+--------------------+--------------+------------------+--------------------------------------------------------------------------------------------------------+--------------------+---------------+----------------------------+----------------------------+--------------------------+--------------------------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+

insert into teacher_history

values(new.id,old.name,new.name,old.dept_id,new.dept_id,now());

1 row in set (0.00sec)

mysql> select * from triggers where trigger_schema='course'and EVENT_OBJECT_TABLE='teacher';+-----------------+----------------+----------------+--------------------+----------------------+---------------------+--------------------+--------------+------------------+--------------------------------------------------------------------------------------------------------+--------------------+---------------+----------------------------+----------------------------+--------------------------+--------------------------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+

insert into teacher_history

values(new.id,old.name,new.name,old.dept_id,new.dept_id,now());

1 row in set (0.01sec)

mysql> selectcurrent_user();+----------------+

| current_user() |

+----------------+

| root@localhost |

+----------------+

1 row in set (0.00 sec)