mysql 触发器trigeer_MySQL触发器的应用

103 阅读 0 评论 68 点赞

我是靠谱客的博主土豪铃铛，最近开发中收集的这篇文章主要介绍mysql 触发器trigeer_MySQL触发器的应用，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

MySQL触发器是在5.0版本引入的。针对需要对数据库做级联更改是很适宜使用的，如需要实时监控某张表中的某个字段的更改而需要做出相应的处理。触发器虽然功能强大，能轻松可靠的实现许多复杂的功能，《使用MySQL UDFs来调用gearman分布式任务分发系统》、《sphinx应用(三)—sphinx分布式, XML数据源, Gearman, UpdateAttributes》以及后续会将用户表导入到NOSQL数据库中，都使用到触发器的。不过，要慎用触发器，滥用触发器会造成数据库以及应用程序维护困难。

1. 创建触发器语法

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 }

CREATE

[DEFINER={user|CURRENT_USER}]

TRIGGERtrigger_name

trigger_timetrigger_event

ONtbl_nameFOREACHROW

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

grantcreatetriggeron`database_naem`.`table_name`to`user_name`@`ip_address`;

权限收回：

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

revokecreatetriggeron`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.1

INSERT：当一个新行插入到表中时触发。如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>createtable`ttlsa_users`(

->`uid`int(11)unsigned,

->`username`varchar(40)NOTNULL,

->`password`varchar(40)NOTNULL,

->`createtime`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,

->PRIMARYKEY(`uid`)

->);

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

mysql> create table `ttlsa_users3` (

-> `uid` int(11) unsigned,

-> `userinfo` varchar(200),

-> );

mysql>createtable`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>createtriggerttlsa_users_ai

->afterinsertonttlsa_users

->foreachrow

->insertintottlsa_users3(uid,userinfo)values(uid,json_object(NEW.uid,NEW.username,NEW.password));

->//

mysql>createtriggerttlsa_users_au

->afterupdateonttlsa_users

->foreachrow

->updatettlsa_users3setuserinfo=json_object(NEW.uid,NEW.username,NEW.password)whereuid=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//

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

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

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

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

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

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

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

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>insertintottlsa_usersvalues(890,'xuhh',md5('abc'),NULL,'test trigger')//

QueryOK,1rowaffected(0.01sec)

mysql>select *fromttlsa_users//

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

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

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

3rowsinset(0.00sec)

mysql>select *fromttlsa_users3//

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

|userinfo|uid|

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

|{"uid":890,"username":"xuhh","password":"900150983cd24fb0d6963f7d28e17f72"}|890|

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

2rowsinset(0.00sec)

mysql>updatettlsa_userssetpassword='test_update'whereuid=890//

QueryOK,1rowaffected(0.00sec)

Rowsmatched:1Changed:1Warnings:0

mysql>select *fromttlsa_users//

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

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

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

3rowsinset(0.00sec)

mysql>select *fromttlsa_users3//

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

|userinfo|uid|

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

|{"uid":890,"username":"xuhh","password":"test_update"}|890|

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

2rowsinset(0.00sec)

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

character_set_client: utf8

collation_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.1

character_set_client: utf8

collation_connection: utf8_general_ci

Database Collation: latin1_swedish_ci

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

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

1 row in set (0.01 sec)

mysql>SHOWTRIGGERSlike'%ttlsa%';触发器名称匹配%ttlsa%

***************************1.row ***************************

Trigger:ttlsa_users_ai

Event:INSERT

Table:ttlsa_users

Statement:insertintottlsa_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.1

character_set_client:utf8

collation_connection:utf8_general_ci

DatabaseCollation:latin1_swedish_ci

***************************2.row ***************************

Trigger:ttlsa_users_au

Event:UPDATE

Table:ttlsa_users

Statement:updatettlsa_users3setuserinfo=json_object(NEW.uid,NEW.username,NEW.password)whereuid=OLD.uid

Timing:AFTER

Created:NULL

sql_mode:NO_ENGINE_SUBSTITUTION

Definer:root@127.0.0.1

character_set_client:utf8

collation_connection:utf8_general_ci

DatabaseCollation:latin1_swedish_ci

2rowsinset(0.00sec)

mysql>SHOWTRIGGERS;列出所有

mysql>SHOWTRIGGERSfromdatabase_name;列出数据库的触发器

mysql>SHOWCREATETRIGGERtrigger_name;查看创建触发器

***************************1.row ***************************

Trigger:ttlsa_users_ai

sql_mode:NO_ENGINE_SUBSTITUTION

SQLOriginalStatement:CREATEDEFINER=`root`@`127.0.0.1`triggerttlsa_users_aiafterinsertonttlsa_usersforeachrowinsertintottlsa_users3(uid,userinfo)values(NEW.uid,json_object(NEW.uid,NEW.username,NEW.password))

character_set_client:utf8

collation_connection:utf8_general_ci

DatabaseCollation:latin1_swedish_ci

1rowinset(0.01sec)

3.2 INFORMATION_SCHEMA.TRIGGERS表

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

ACTION_REFERENCE_OLD_TABLE: NULL

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

1 row in set (0.00 sec)

mysql>SELECT *FROMINFORMATION_SCHEMA.TRIGGERSWHERETRIGGER_SCHEMA='test'ANDTRIGGER_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:updatettlsa_users3setuserinfo=json_object(NEW.uid,NEW.username,NEW.password)whereuid=OLD.uid

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

1rowinset(0.00sec)

3.3 删除触发器

mysql> drop trigger trigger_name;

mysql>droptriggertrigger_name;

如需转载请注明出处：MySQL触发器的应用 http://www.ttlsa.com/html/2335.html

最后

以上就是土豪铃铛为你收集整理的mysql 触发器trigeer_MySQL触发器的应用的全部内容，希望文章能够帮你解决mysql 触发器trigeer_MySQL触发器的应用所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。

本文分类：mysql 触发器trigeer
浏览次数：103 次浏览
发布日期：2023-05-10 23:31:02
本文链接：https://www.kaopuke.com/article/k-p-k_14_ujo_6_f5_13__7__14_4.html

mysql 触发器trigeer_MySQL触发器的应用

概述

最后

评论列表共有 0 条评论

发表评论取消回复

mysql 触发器trigeer_MySQL触发器的应用

概述

最后

相关文章

评论列表共有 0 条评论

发表评论 取消回复

发表评论取消回复