MySQL触发器教程

JSON 2024-04-01 18:31:14 314

触发器是在指定表上执行指定更改操作(SQL INSERT、UPDATE 或 DELETE 语句)时自动运行的一组操作。触发器对于执行业务规则、验证输入数据和保留审计跟踪等任务非常有用。下面sojson会给大家详细讲解。

触发器的用途:

执行业务规则

验证输入数据

为不同文件中新插入的行生成唯一值。

写入其他文件以进行审计跟踪

从其他文件中查询以进行交叉引用

访问系统功能

将数据复制到不同文件以实现数据一致性

在业务中使用触发器的好处:

更快的应用程序开发。由于数据库存储触发器,因此您不必将触发器操作编码到每个数据库应用程序中。

业务规则的全球执行。定义一次触发器,然后将其重新用于任何使用数据库的应用程序。

维护更方便。如果业务策略发生变化,只需要更改相应的触发程序,而不需要更改每个应用程序。

提高客户端/服务器环境中的性能。在结果返回之前,所有规则都在服务器上运行。

SQL触发器的实现基于SQL标准。它支持大多数编程语言通用的构造。它支持局部变量的声明、控制过程流程的语句、将表达式结果分配给变量以及错误处理。

MySQL触发器

我们假设您熟悉“MySQL 存储过程”,如果不习惯,您可以阅读我们的MySQL 过程教程。您可以在触发器中使用MySQL过程的以下语句:

复合语句(BEGIN / END)

变量声明(DECLARE)和赋值(SET)

控制流语句(IF、CASE、WHILE、LOOP、WHILE、REPEAT、LEAVE、ITERATE)

条件声明

处理程序声明

如何创建MySQL触发器?

触发器是与表关联的命名数据库对象,当表发生特定事件(例如插入、更新或删除)时,触发器就会激活。 CREATE TRIGGER 语句在 MySQL 中创建一个新触发器。

语法如下:

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 }

解释:

DEFINER 子句: DEFINER 子句指定在触发器激活时检查访问权限时要使用的 MySQL 帐户。如果给出了用户值,则它应该是指定为“user_name”@“host_name”(与 GRANT 语句中使用的格式相同)、CURRENT_USER 或 CURRENT_USER() 的 MySQL 帐户。

默认 DEFINER 值是执行 CREATE TRIGGER 语句的用户。这与显式指定 DEFINER = CURRENT_USER 相同。

如果指定 DEFINER 子句,这些规则将确定有效的 DEFINER 用户值:

1.如果您没有 SUPER 权限,则唯一允许的用户值是您自己的帐户,可以按字面指定或使用 CURRENT_USER 指定。您不能将定义者设置为其他帐户。

2.如果您具有 SUPER 权限,则可以指定任何语法上有效的帐户名。如果该帐户实际上不存在,则会生成警告。

3.尽管可以使用不存在的 DEFINER 帐户创建触发器,但在帐户实际存在之前激活此类触发器并不是一个好主意。否则,与权限检查有关的行为是未定义的。

触发器名称:架构中的所有触发器必须具有唯一的名称。不同模式中的触发器可以具有相同的名称。

trigger_time: trigger_time为触发动作时间。它可以是 BEFORE 或 AFTER,指示触发器在要修改的每一行之前或之后激活。

trigger_event: trigger_event表示激活触发器的操作类型。这些trigger_event值是允许的:

1.每当新行插入表中时,触发器就会激活;例如,通过 INSERT、LOAD DATA 和 REPLACE 语句。

2.每当一行被修改时触发器就会激活;例如,通过 UPDATE 语句。

3.每当从表中删除一行时,触发器就会激活;例如,通过 DELETE 和 REPLACE 语句。表上的 DROP TABLE 和 TRUNCATE TABLE 语句不会激活此触发器,因为它们不使用 DELETE。删除分区也不会激活 DELETE 触发器。

tbl_name :触发器与名为 tbl_name 的表关联,该表必须引用永久表。您不能将触发器与 TEMPORARY 表或视图关联。

trigger_body: trigger_body是触发器激活时要执行的语句。要执行多个语句,请使用 BEGIN ... END 复合语句结构。这还使您能够使用存储例程中允许的相同语句。

例子:

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
    -> FOR EACH ROW SET @sum = @sum + NEW.amount;
Query OK, 0 rows affected (0.06 sec)

在上面的例子中,有一个新的关键字“ NEW ”,它是 MySQL 对触发器的扩展。有两个 MySQL 扩展可以触发“ OLD ”和“ NEW ”。 OLD 和 NEW 不区分大小写。

在触发器主体内,OLD 和 NEW 关键字使您能够访问受触发器影响的行中的列

在 INSERT 触发器中,只能使用 NEW.col_name。

在 UPDATE 触发器中,您可以使用 OLD.col_name 引用行更新前的列,使用 NEW.col_name 引用行更新后的列。

在 DELETE 触发器中,只能使用 OLD.col_name;没有新行。

以 OLD 命名的列是只读的。您可以引用它(如果您有 SELECT 权限),但不能修改它。如果您具有 SELECT 权限,则可以引用以 NEW 命名的列。在 BEFORE 触发器中,如果您具有 UPDATE 权限,您还可以使用 SET NEW.col_name = value 更改其值。这意味着您可以使用触发器来修改要插入新行或用于更新行的值。 (这样的 SET 语句在 AFTER 触发器中没有效果,因为行更改已经发生。)

各种示例的示例数据库、表、表结构、表记录

数据库名称:hr

主机名:localhost

数据库用户:root

密码:' '

表的结构:emp_details


表的记录(部分字段):emp_details

mysql> 从 emp_details 中选择 EMPLOYEE_ID、FIRST_NAME、LAST_NAME、JOB_ID、SALARY、COMMISSION_PCT;

+-------------+------------+-----------+---------+----------+----------------+
| EMPLOYEE_ID | FIRST_NAME | LAST_NAME | JOB_ID  | SALARY   | COMMISSION_PCT |
+-------------+------------+-----------+---------+----------+----------------+
|         100 | Steven     | King      | AD_PRES | 24000.00 |           0.10 |
|         101 | Neena      | Kochhar   | AD_VP   | 17000.00 |           0.50 |
|         102 | Lex        | De Haan   | AD_VP   | 17000.00 |           0.50 |
|         103 | Alexander  | Hunold    | IT_PROG |  9000.00 |           0.25 |
|         104 | Bruce      | Ernst     | IT_PROG |  6000.00 |           0.25 |
|         105 | David      | Austin    | IT_PROG |  4800.00 |           0.25 |
+-------------+------------+-----------+---------+----------+----------------+

6 行一组(0.00 秒)

创建 MySQL 触发器的工具

您可以使用MySQL命令行工具编写程序,也可以使用MySQL Workbench,这是一个优秀的前端工具(这里我们使用5.3 CE版本)。

MySQL 命令行工具:

从开始菜单中选择 MySQL 命令 Client:


选择 MySQL 命令提示符,将出现以下屏幕:


登录成功后,可以进入MySQL命令提示符:


现在您可以在特定表上编写自己的触发器,请参阅以下示例:


完成代码后,单击“应用”按钮。

注意:在“添加触发器”按钮中看到新文本“删除触发器”。单击此按钮可以删除触发器。

最后您可以再次检查脚本,因为没有错误,所以单击“应用”按钮:


这是完成前的最后一个窗口。单击“完成”按钮。


如果您查看架构,您将在 emp_details 表下看到 emp_details_AINS 触发器,如下所示:


MySQL 触发器:插入后示例

在以下示例中,我们有两个表:emp_details 和 log_emp_details。为了每次将一些信息插入到 log_emp_details 表(该表具有三个字段:员工 ID、薪水和 edttime)中,当 INSERT 发生到 emp_details 表中时,我们使用了以下触发器:

DELIMITER 
$
USE `hr`
$
CREATE 
DEFINER=`root`@`127.0.0.1` 
TRIGGER `hr`.`emp_details_AINS` 
AFTER INSERT ON `hr`.`emp_details`
FOR EACH ROW
-- Edit trigger body code below this line. Do not edit lines above this one
BEGIN 
INSERT INTO log_emp_details 
VALUES(NEW.employee_id, NEW.salary, NOW());
END$

MySQL 触发器:删除后示例

在我们的“更新后”示例中,我们有两个表student_mast 和stu_log。 Student_mast有三列STUDENT_ID、NAME、ST_CLASS,而stu_log表有两列user_id和description。在对student_mast表进行删除操作后,我们希望在stu_log表中存储一些信息。这是触发器:

+------------+------------------+----------+
| STUDENT_ID | NAME             | ST_CLASS |
+------------+------------------+----------+
|          2 | Neena  Kochhar   |        9 |
|          3 | Lex  De Haan     |        9 |
|          4 | Alexander Hunold |       11 |
+------------+------------------+----------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM STU_LOG;
+----------------+-----------------------------------------------------------------------------+
| user_id        | description                                                                 |
+----------------+-----------------------------------------------------------------------------+
| root@localhost | Update Student RecordSteven King Previous Class :7 Present Class 8          |
| root@localhost | Update Student RecordNeena  Kochhar Previous Class :8 Present Class 9       |
| root@localhost | Update Student RecordLex  De Haan Previous Class :8 Present Class 9         |
| root@localhost | Update Student RecordAlexander Hunold Previous Class :10 Present Class 11   |
| root@localhost | Update Student Record Steven King Clas :8-> Deleted on 2013-07-16 15:35:30  |
+----------------+-----------------------------------------------------------------------------+

MySQL如何处理触发器执行过程中的错误?

如果 BEFORE 触发器失败,则不会执行相应行的操作。

BEFORE 触发器在尝试插入或修改行时被激活,无论该尝试随后是否成功。

仅当任何 BEFORE 触发器且行操作执行成功时,才会执行 AFTER 触发器。

BEFORE 或 AFTER 触发器期间的错误会导致导致触发器调用的整个语句失败。

对于事务表,语句失败应导致该语句执行的所有更改回滚。

删除 MySQL 触发器

要删除或销毁触发器,请使用 DROP TRIGGER 语句。如果触发器不在默认(当前)架构中,则必须指定架构名称:

删除触发器 [如果存在] [ schema_name .] trigger_nam如果删除一个表,该表的所有触发器也会被删除。

版权所属:SO JSON在线解析

原文地址:https://www.sojson.com/blog/517.html

转载时必须以链接形式注明原始出处及本声明。

本文主题:

如果本文对你有帮助,那么请你赞助我,让我更有激情的写下去,帮助更多的人。

关于作者
一个低调而闷骚的男人。
相关文章
Elasticsearch教程(二),IK分词安装
Mysql插入、存在不插入,存在更新操作
Elasticsearch教程(三),IK分词安装 (极速版)
Elasticsearch教程,Elasticsearch 设置近义词搜索,IK分词实现同义词搜索
Mysql5.6下载安装,Mysql5.7下载安装 ,Windows64位,绿色安装(解压缩安装)图文安装教程
Elasticsearch教程,Elasticsearch Java API创建Mapping,指定分词
Oracle与Mysql删除重复的数据,Oracle和Mysql数据去重复
MySQL 分区、分表
Shiro教程(四)Shiro + Redis配置
IE、Firefox对同一域名访问并限制,及解决优化方案
最新文章
Java赋值运算符 21
XML内部实体和外部实体 180
Java面向对象编程概念 143
PHP回显语句 91
Linux—文件树 116
C语言while循环和do while循环 131
Python元组剖析 200
MySQL触发器教程 314
sql使用布尔运算符和关系运算符 258
C语言的变量和常量 296
最热文章
最新MyEclipse8.5注册码,有效期到2020年 (已经更新) 682085
苹果电脑Mac怎么恢复出厂系统?苹果系统怎么重装系统? 674719
免费天气API,全国天气 JSON API接口,可以获取五天的天气预报 602172
免费天气API,天气JSON API,不限次数获取十五天的天气预报 577354
Jackson 时间格式化,时间注解 @JsonFormat 用法、时差问题说明 552926
我为什么要选择RabbitMQ ,RabbitMQ简介,各种MQ选型对比 509367
Elasticsearch教程(四) elasticsearch head 插件安装和使用 480022
Jackson 美化输出JSON,优雅的输出JSON数据,格式化输出JSON数据... ... 264583
Java 信任所有SSL证书,HTTPS请求抛错,忽略证书请求完美解决 244254
Elasticsearch教程(一),全程直播(小白级别) 225588
支付扫码

所有赞助/开支都讲公开明细,用于网站维护:赞助名单查看

查看我的收藏

正在加载... ...