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

| 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.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;+----+-----------+----------+------------+------------+---------------------+

| 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.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;+----------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------+-------+

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

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

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

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

| current_user() |

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

| root@localhost |

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

1 row in set (0.00 sec)

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值