9159金沙游艺场-奥门金沙游艺场-[欢迎您]

9159金沙游艺场为您呈现最新的面貌!,目前注册送体验金,欢迎您将成为影视娱乐行业新的风向标,是亚洲的优质娱乐先锋。

MySQL中触发器的基础学习教程

日期:2020-04-04编辑作者:服务器

0.触发器的基本概念触发器是一种特殊的存储过程,它在插入,删除或修改特定表中的数据时触发执行,它比数据库本身标准的功能有更精细和更复杂的数据控制能力。

数据库触发器有以下的作用:

(1).安全性。可以基于数据库的值使用户具有操作数据库的某种权利。

# 可以基于时间限制用户的操作,例如不允许下班后和节假日修改数据库数据。

# 可以基于数据库中的数据限制用户的操作,例如不允许股票的价格的升幅一次超过10%。

(2).审计。可以跟踪用户对数据库的操作。

# 审计用户操作数据库的语句。

# 把用户对数据库的更新写入审计表。

(3).实现复杂的数据完整性规则

# 实现非标准的数据完整性检查和约束。触发器可产生比规则更为复杂的限制。与规则不同,触发器可以引用列或数据库对象。例如,触发器可回退任何企图吃进超过自己保证金的期货。

# 提供可变的缺省值。

(4).实现复杂的非标准的数据库相关完整性规则。触发器可以对数据库中相关的表进行连环更新。例如,在auths表author_code列上的删除触发器可导致相应删除在其它表中的与之匹配的行。

# 在修改或删除时级联修改或删除其它表中的与之匹配的行。

# 在修改或删除时把其它表中的与之匹配的行设成NULL值。

# 在修改或删除时把其它表中的与之匹配的行级联设成缺省值。

# 触发器能够拒绝或回退那些破坏相关完整性的变化,取消试图进行数据更新的事务。当插入一个与其主健不匹配的外部键时,这种触发器会起作用。例如,可以在books.author_code 列上生成一个插入触发器,如果新值与auths.author_code列中的某值不匹配时,插入被回退。

(5).同步实时地复制表中的数据。

(6).自动计算数据值,如果数据的值达到了一定的要求,则进行特定的处理。例如,如果公司的帐号上的资金低于5万元则立即给财务人员发送警告数据。

1. 创建触发器语法

CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_bodytrigger_time: { BEFORE | AFTER }trigger_event: { INSERT | UPDATE | DELETE }CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_body trigger_time: { BEFORE | AFTER }trigger_event: { INSERT | UPDATE | DELETE }

语法相关部分说明:1.1 授权与回收创建触发器需要有CREATE TRIGGER权限:

grant create trigger on `database_naem`.`table_name` to `user_name`@`ip_address`;grant create trigger on `database_naem`.`table_name` to `user_name`@`ip_address`;

权限收回:

revoke create trigger on `database_naem`.`table_name` from `user_name`@`ip_address`;revoke create trigger on `database_naem`.`table_name` from `user_name`@`ip_address`;

1.2 trigger_name必须给触发器命令,最多64个字符,建议用表的名字_触发器类型的缩写方法命名。如ttlsa_posts_bi(表ttlsa_posts,触发器发生在insert之前before)1.3 DEFINER子句在激活触发器时,检查访问权限,确保触发器安全使用。1.4 trigger_time定义触发器触发时间。可以设置为在行记录更改之前或之后发生。1.5 trigger_event定义触发器触发事件。触发的事件有:1.5.1INSERT:当一个新行插入到表中时触发。如INSERT、LOAD DATA和REPLACE语句。UPDATE:当一个行数据被更改时触发。如UPDATE语句。DELETE:当一个行从表中删除时触发。如DELETE和REPLACE语句。 注意:DROP TABLE和TRUNCATE TABLE语句不会触发该触发器,因为它们不是使用DELETE。同样删除一个分区表也不会触发。有一个潜在的混乱情况,如INSERT INTO ... ON DUPLICATE KEY UPDATE ... 取决于是否有重复键行。不能对一个表创建具有相同的触发事件和触发时间的多个触发器。如对于一个表不能创建两个BEFORE UPDATE触发器,但是,可以创建一个BEFORE UPDATE和一个BEFORE INSERT或一个BEFORE UPDATE和一个AFTER UPDATE触发器。1.6 FOR EACH ROW子句定义触发执行间隔。FOR EACH ROW子句定义触发器每隔一行执行一次动作,而不是对整个表执行一次。1.7 trigger_body子句包含要触发执行的SQL语句。可以是任何合法的语句,包括复合语句(需要使用BEGIN ... END结构),流控制语句(if、case、while、loop、for、repeat、leave、iterate),变量声明(declare)以及指派(set),异常处理声明,允许条件声明,但是这里的语句受的限制和函数的一样。1.7.1 OLD与NEW在触发器的SQL语句中,可以关联表中的任何列,通过使用OLD和NEW列名来标识,如OLD.col_name、NEW.col_name。OLD.col_name关联现有的行的一列在被更新或删除前的值。NEW.col_name关联一个新行的插入或更新现有的行的一列的值。对于INSERT语句,只有NEW是合法的。否则会报错:ERROR 1363 (HY000): There is no OLD row in on INSERT trigger对于DELETE语句,只有OLD是合法的。否则会报错:ERROR 1363 (HY000): There is no NEW row in on DELETE trigger对于UPDATE语句,NEW和OLD可以同时使用。2. 实例2.1 创建表使用在《mysqludf_json将关系数据以JSON编码》一文中创建的表。后续会将用户表迁移到nosql数据库上的。

mysql create table `ttlsa_users` ( - `uid` int(11) unsigned, - `username` varchar(40) NOT NULL, - `password` varchar(40) NOT NULL, - `createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, - PRIMARY KEY (`uid`) - );mysql create table `ttlsa_users` ( - `uid` int(11) unsigned, - `username` varchar(40) NOT NULL, - `password` varchar(40) NOT NULL, - `createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, - PRIMARY KEY (`uid`) - );

创建另外一张表来存放触发器动作数据。

mysql create table `ttlsa_users3` ( - `uid` int(11) unsigned, - `userinfo` varchar(200), - );mysql create table `ttlsa_users3` ( - `uid` int(11) unsigned, - `userinfo` varchar(200), - );

2.2 创建触发器

mysql delimiter //mysql create trigger ttlsa_users_ai - after insert on ttlsa_users - for each row - insert into ttlsa_users3 (uid, userinfo) values(uid, json_object(NEW.uid, NEW.username, NEW.password)); - //mysql create trigger ttlsa_users_au - after update on ttlsa_users - for each row - update ttlsa_users3 set userinfo=json_object(NEW.uid, NEW.username, NEW.password) where uid=OLD.uid; - //mysql delimiter //mysql create trigger ttlsa_users_ai - after insert on ttlsa_users - for each row - insert into ttlsa_users3 (uid, userinfo) values(uid, json_object(NEW.uid, NEW.username, NEW.password)); - // mysql create trigger ttlsa_users_au - after update on ttlsa_users - for each row - update ttlsa_users3 set userinfo=json_object(NEW.uid, NEW.username, NEW.password) where uid=OLD.uid; - //

2.3 测试

mysql insert into ttlsa_users values (890,'xuhh',md5('abc'),NULL,'test trigger')//

Query OK, 1 row affected (0.01 sec)

mysql select * from ttlsa_users//

+-----+-------------+----------------------------------+---------------------+------------------------------------+| uid | username | password | createtime | json_data |+-----+-------------+----------------------------------+---------------------+------------------------------------+| 888 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 11:27:01 | website - "" || 889 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 14:08:44 | xuhh || 890 | xuhh | 900150983cd24fb0d6963f7d28e17f72 | 2013-08-14 16:40:49 | test trigger |+-----+-------------+----------------------------------+---------------------+------------------------------------+3 rows in set (0.00 sec)

mysql select * from ttlsa_users3//

+-----------------------------------------------------------------------------+------+| userinfo | uid |+-----------------------------------------------------------------------------+------+| {"uid":890,"username":"xuhh","password":"900150983cd24fb0d6963f7d28e17f72"} | 890 |+-----------------------------------------------------------------------------+------+2 rows in set (0.00 sec)

mysql update ttlsa_users set password='test_update' where uid=890//

Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0

mysql select * from ttlsa_users//

+-----+-------------+----------------------------------+---------------------+------------------------------------+| uid | username | password | createtime | json_data |+-----+-------------+----------------------------------+---------------------+------------------------------------+| 888 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 11:27:01 | website - "" || 889 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 14:08:44 | xuhh || 890 | xuhh | test_update | 2013-08-14 16:41:33 | test trigger |+-----+-------------+----------------------------------+---------------------+------------------------------------+3 rows in set (0.00 sec)

mysql select * from ttlsa_users3//

+-----------------------------------------------------------------------------+------+| userinfo | uid |+-----------------------------------------------------------------------------+------+| {"uid":890,"username":"xuhh","password":"test_update"} | 890 |+-----------------------------------------------------------------------------+------+2 rows in set (0.00 sec)

mysql insert into ttlsa_users values (890,'xuhh',md5('abc'),NULL,'test trigger')//

Query OK, 1 row affected (0.01 sec)

mysql select * from ttlsa_users//

+-----+-------------+----------------------------------+---------------------+------------------------------------+| uid | username | password | createtime | json_data |+-----+-------------+----------------------------------+---------------------+------------------------------------+| 888 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 11:27:01 | website - "" || 889 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 14:08:44 | xuhh || 890 | xuhh | 900150983cd24fb0d6963f7d28e17f72 | 2013-08-14 16:40:49 | test trigger |+-----+-------------+----------------------------------+---------------------+------------------------------------+3 rows in set (0.00 sec)

mysql select * from ttlsa_users3//

+-----------------------------------------------------------------------------+------+| userinfo | uid |+-----------------------------------------------------------------------------+------+| {"uid":890,"username":"xuhh","password":"900150983cd24fb0d6963f7d28e17f72"} | 890 |+-----------------------------------------------------------------------------+------+2 rows in set (0.00 sec) 

mysql update ttlsa_users set password='test_update' where uid=890//

Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0

mysql select * from ttlsa_users//

+-----+-------------+----------------------------------+---------------------+------------------------------------+| uid | username | password | createtime | json_data |+-----+-------------+----------------------------------+---------------------+------------------------------------+| 888 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 11:27:01 | website - "" || 889 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 14:08:44 | xuhh || 890 | xuhh | test_update | 2013-08-14 16:41:33 | test trigger |+-----+-------------+----------------------------------+---------------------+------------------------------------+3 rows in set (0.00 sec)

mysql select * from ttlsa_users3//

+-----------------------------------------------------------------------------+------+| userinfo | uid |+-----------------------------------------------------------------------------+------+| {"uid":890,"username":"xuhh","password":"test_update"} | 890 |+-----------------------------------------------------------------------------+------+2 rows in set (0.00 sec)

3. 管理3.1 列出触发器

mysql SHOW TRIGGERS like '%ttlsa%'; 触发器名称匹配%ttlsa%

*************************** 1. row *************************** Trigger: ttlsa_users_ai Event: INSERT Table: ttlsa_users Statement: insert into ttlsa_users3 (uid,userinfo) values(NEW.uid,json_object(NEW.uid, NEW.username, NEW.password)) Timing: AFTER Created: NULL sql_mode: NO_ENGINE_SUBSTITUTION Definer: root@127.0.0.1character_set_client: utf8collation_connection: utf8_general_ci Database Collation: latin1_swedish_ci*************************** 2. row *************************** Trigger: ttlsa_users_au Event: UPDATE Table: ttlsa_users Statement: update ttlsa_users3 set userinfo=json_object(NEW.uid, NEW.username, NEW.password) where uid=OLD.uid Timing: AFTER Created: NULL sql_mode: NO_ENGINE_SUBSTITUTION Definer: root@127.0.0.1character_set_client: utf8collation_connection: utf8_general_ci Database Collation: latin1_swedish_ci2 rows in set (0.00 sec)

mysql SHOW TRIGGERS; #列出所有mysql SHOW TRIGGERS from database_name; #列出数据库的触发器mysql SHOW CREATE TRIGGER trigger_name; #查看创建触发器

*************************** 1. row *************************** Trigger: ttlsa_users_ai sql_mode: NO_ENGINE_SUBSTITUTIONSQL Original Statement: CREATE DEFINER=`root`@`127.0.0.1` trigger ttlsa_users_ai after insert on ttlsa_users for each row insert into ttlsa_users3 (uid,userinfo) values(NEW.uid,json_object(NEW.uid, NEW.username, NEW.password)) character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: latin1_swedish_ci1 row in set (0.01 sec)

3.2 INFORMATION_SCHEMA.TRIGGERS表

sql SHOW TRIGGERS like '%ttlsa%'; #触发器名称匹配%ttlsa%

*************************** 1. row *************************** Trigger: ttlsa_users_ai Event: INSERT Table: ttlsa_users Statement: insert into ttlsa_users3 (uid,userinfo) values(NEW.uid,json_object(NEW.uid, NEW.username, NEW.password)) Timing: AFTER Created: NULL sql_mode: NO_ENGINE_SUBSTITUTION Definer: root@127.0.0.1character_set_client: utf8collation_connection: utf8_general_ci Database Collation: latin1_swedish_ci*************************** 2. row *************************** Trigger: ttlsa_users_au Event: UPDATE Table: ttlsa_users Statement: update ttlsa_users3 set userinfo=json_object(NEW.uid, NEW.username, NEW.password) where uid=OLD.uid Timing: AFTER Created: NULL sql_mode: NO_ENGINE_SUBSTITUTION Definer: root@127.0.0.1character_set_client: utf8collation_connection: utf8_general_ci Database Collation: latin1_swedish_ci2 rows in set (0.00 sec)

mysql SHOW TRIGGERS; #列出所有mysql SHOW TRIGGERS from database_name; #列出数据库的触发器mysql SHOW CREATE TRIGGER trigger_name; #查看创建触发器

*************************** 1. row *************************** Trigger: ttlsa_users_ai sql_mode: NO_ENGINE_SUBSTITUTIONSQL Original Statement: CREATE DEFINER=`root`@`127.0.0.1` trigger ttlsa_users_ai after insert on ttlsa_users for each row insert into ttlsa_users3 (uid,userinfo) values(NEW.uid,json_object(NEW.uid, NEW.username, NEW.password)) character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: latin1_swedish_ci1 row in set (0.01 sec)

mysql SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA='test' AND TRIGGER_NAME='ttlsa_users_au'G

*************************** 1. row *************************** TRIGGER_CATALOG: def TRIGGER_SCHEMA: test TRIGGER_NAME: ttlsa_users_au EVENT_MANIPULATION: UPDATE EVENT_OBJECT_CATALOG: def EVENT_OBJECT_SCHEMA: test EVENT_OBJECT_TABLE: ttlsa_users ACTION_ORDER: 0 ACTION_CONDITION: NULL ACTION_STATEMENT: update ttlsa_users3 set userinfo=json_object(NEW.uid, NEW.username, NEW.password) where uid=OLD.uid ACTION_ORIENTATION: ROW ACTION_TIMING: AFTERACTION_REFERENCE_OLD_TABLE: NULLACTION_REFERENCE_NEW_TABLE: NULL ACTION_REFERENCE_OLD_ROW: OLD ACTION_REFERENCE_NEW_ROW: NEW CREATED: NULL SQL_MODE: NO_ENGINE_SUBSTITUTION DEFINER: root@127.0.0.1 CHARACTER_SET_CLIENT: utf8 COLLATION_CONNECTION: utf8_general_ci DATABASE_COLLATION: latin1_swedish_ci1 row in set (0.00 sec)

mysql SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA='test' AND TRIGGER_NAME='ttlsa_users_au'G

*************************** 1. row *************************** TRIGGER_CATALOG: def TRIGGER_SCHEMA: test TRIGGER_NAME: ttlsa_users_au EVENT_MANIPULATION: UPDATE EVENT_OBJECT_CATALOG: def EVENT_OBJECT_SCHEMA: test EVENT_OBJECT_TABLE: ttlsa_users ACTION_ORDER: 0 ACTION_CONDITION: NULL ACTION_STATEMENT: update ttlsa_users3 set userinfo=json_object(NEW.uid, NEW.username, NEW.password) where uid=OLD.uid ACTION_ORIENTATION: ROW ACTION_TIMING: AFTERACTION_REFERENCE_OLD_TABLE: NULLACTION_REFERENCE_NEW_TABLE: NULL ACTION_REFERENCE_OLD_ROW: OLD ACTION_REFERENCE_NEW_ROW: NEW CREATED: NULL SQL_MODE: NO_ENGINE_SUBSTITUTION DEFINER: root@127.0.0.1 CHARACTER_SET_CLIENT: utf8 COLLATION_CONNECTION: utf8_general_ci DATABASE_COLLATION: latin1_swedish_ci1 row in set (0.00 sec)

3.3 删除触发器

mysql drop trigger trigger_name;mysql drop trigger trigger_name;

本文由9159金沙游艺场-奥门金沙游艺场-[欢迎您]发布于服务器,转载请注明出处:MySQL中触发器的基础学习教程

关键词:

MySQL中对表连接查询的大约优化学科

在MySQL中,A LEFT JOIN B join_condition执行过程如下: · 根据表A和A依赖的所有表设置表B。 · 根据LEFT JOIN条件中使用的所有...

详细>>

化解Java程序行使MySQL时重返参数为乱码的演示教

先说MySQL的字符集难题。Windows下可由此改变my.ini内的 default-character-set=utf8 //客户端的默认字符集 在MySQL客商端工具中...

详细>>

MySQL中describe命令的使用方法小结

一、describe命令用于查看特定表的详细设计信息 例如为了查看guestbook表的设计信息,可用: describe guestbookdescribe ol_...

详细>>

MySQL触发器运用于迁移和同步数据的实例教程【

1.迁移数据 进行数据库移植,SQL Server=MySQL。SQLServer上有如下的Trigger SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ALTER TRIGGE...

详细>>