MySQL触发器教程
触发器是在指定表上执行指定更改操作(SQL INSERT、UPDATE 或 DELETE 语句)时自动运行的一组操作。触发器对于执行业务规则、验证输入数据和保留审计跟踪等任务非常有用。下面sojson会给大家详细讲解。
触发器的用途:
执行业务规则
验证输入数据
为不同文件中新插入的行生成唯一值。
写入其他文件以进行审计跟踪
从其他文件中查询以进行交叉引用
访问系统功能
将数据复制到不同文件以实现数据一致性
在业务中使用触发器的好处:
更快的应用程序开发。由于数据库存储触发器,因此您不必将触发器操作编码到每个数据库应用程序中。
业务规则的全球执行。定义一次触发器,然后将其重新用于任何使用数据库的应用程序。
维护更方便。如果业务策略发生变化,只需要更改相应的触发程序,而不需要更改每个应用程序。
提高客户端/服务器环境中的性能。在结果返回之前,所有规则都在服务器上运行。
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
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
转载时必须以链接形式注明原始出处及本声明。
如果本文对你有帮助,那么请你赞助我,让我更有激情的写下去,帮助更多的人。