Mysql数据库中的触发器(Triggers)详解,定义、原理及其应用实例
数据库的触发器(Triggers)是数据库管理系统(DBMS)中的功能,它允许用户定义在指定表上的某些操作(如 INSERT、UPDATE 或 DELETE)发生之前或之后自动执行的 SQL 语句或语句集。触发器是与表事件(表的INSERT、UPDATE、DELETE操作)相关联的命名数据库对象。在指定表上发生特定事件时,触发器会自动激活,执行相应的 SQL 语句。触发器包含复杂 SQL 语句,这
目录
一、触发器概述
MySQL 的触发器(Triggers)是数据库管理系统(DBMS)中的一种功能,它允许用户定义在指定表上的某些操作(如 INSERT、UPDATE 或 DELETE)发生之前或之后自动执行的 SQL 语句或语句集。触发器是与表事件(例如,表的 INSERT、UPDATE 或 DELETE 操作)相关联的命名数据库对象。当在指定表上发生特定事件时,触发器会自动激活,并执行相应的 SQL 语句。如下图示,表示触发器的原理:
二、触发器的特点
1、自动执行
当在表上执行指定操作时,触发器会自动激活。
2、与表关联
触发器与特定的表相关联,并且只能在该表上定义的触发事件上激活。
3、可以定义复杂逻辑
触发器可以包含复杂的 SQL 语句,这些语句可以执行数据验证、更新其他表、发送电子邮件等。
4、两种触发时间
触发器可以在事件之前(BEFORE)或之后(AFTER)激活。
5、六种触发事件
INSERT、UPDATE(OF 列名)、DELETE。具体如下:
(1)BEFORE INSERT
在插入操作之前触发。这种触发器在插入数据到表中之前执行,可以用于对即将插入的数据进行验证、修改或者阻止插入操作。
(2)AFTER INSERT
在插入操作完成后触发。这种触发器在插入数据到表中之后执行,可以用于对新插入的数据进行额外处理,如更新其他表的数据、记录日志等。
(3)BEFORE UPDATE
在更新操作之前触发。这种触发器在更新表中的数据之前执行,可以用于对即将更新的数据进行验证、修改或者阻止更新操作。
(4)AFTER UPDATE
在更新操作完成后触发。这种触发器在更新表中的数据之后执行,可以用于对更新后的数据进行额外处理,如更新其他表的数据、记录日志等。
(5)BEFORE DELETE
在删除操作之前触发。这种触发器在删除表中的数据之前执行,可以用于对即将删除的数据进行验证、修改或者阻止删除操作。
(6)AFTER DELETE
在删除操作完成后触发。这种触发器在删除表中的数据之后执行,可以用于对删除后的数据进行额外处理,如更新其他表的数据、记录日志等。
三、触发器的创建
1、创建
使用 CREATE TRIGGER 语句来创建触发器。下面是一个基本的触发器创建示例:
CREATE TRIGGER trigger_name
BEFORE INSERT ON table_name FOR EACH ROW
BEGIN
--触发器要执行的 SQL 语句
SET NEW.column_name = value; -- 这仅适用于 BEFORE INSERT 触发器
END;
2、解释
在这个示例中:
trigger_name 是触发器的名称。
BEFORE INSERT ON table_name 定义了触发器的触发时间和触发事件。
FOR EACH ROW 表示触发器对受影响的每一行都会执行。
BEGIN ... END; 之间的部分是触发器要执行的 SQL 语句。
四、触发器的使用场景
1、数据验证
在插入或更新数据之前,触发器可以检查数据的有效性,并防止无效数据进入数据库。
2、数据审计
触发器可以记录对表的更改,以便稍后进行审计或跟踪。
3、数据完整性
触发器可以确保数据的完整性,例如,通过在外键约束不可用时自动更新或删除相关表中的数据。
4、自动计算字段
触发器可以在插入或更新数据时自动计算并设置某些字段的值。
5、日志记录
触发器可以将表的更改记录到另一个表中,以便稍后进行查询或分析。
五、完整的触发器示例
1、要求
写一个触发器,在orders表插入新记录后自动更新customers表中对应客户的总订单金额。
2、建表
假设有两个表:customers 和 orders,其定义如下:
(1)customers 表结构
customers 表结构 如下:
CREATE TABLE customers (
customer_id INT PRIMARY KEY AUTO_INCREMENT,
customer_name VARCHAR(255) NOT NULL,
total_orders_amount DECIMAL(10, 2) DEFAULT 0.00
);
(2)orders 表结构
orders 表结构可能如下:
CREATE TABLE orders (
order_id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT,
order_amount DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
3、创建触发器
(1)触发器功能
要创建一个触发器,当在orders表中插入新订单时,自动更新customers表中对应客户的total_orders_amount字段。
(2)触发器语句
以下是创建该触发器的SQL语句:
DELIMITER //
CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
-- 更新 customers 表中对应客户的总订单金额
UPDATE customers
SET total_orders_amount = total_orders_amount + NEW.order_amount
WHERE customer_id = NEW.customer_id;
END;
//
DELIMITER ;
(3)触发器解释
- DELIMITER // 和 DELIMITER ; 用于更改命令提示符,以便在触发器定义中使用多个语句。
- AFTER INSERT ON orders 指定触发器在orders表插入新记录后激活。
- FOR EACH ROW 表示对插入的每一行都会执行触发器中的语句。
- NEW.order_amount 和 NEW.customer_id 是在orders表中新插入的行中的值。
- 触发器中的UPDATE语句用于更新customers表中对应客户的total_orders_amount字段。
总的执行结果是:每当在orders表中插入一个新订单时,该触发器都会自动更新customers表中对应客户的总订单金额。
六、触发器的限制和注意事项
1、触发器不能由 SQL 语句直接调用,它们只能由触发事件激活。
2、触发器中不能包含对数据表本身的 UPDATE 或 DELETE 操作(但可以对其他表执行这些操作)。
3、触发器中的 SQL 语句必须是有效的,并且不能包含任何导致错误或异常的语句。
4、触发器可能会增加数据库的复杂性,并可能导致性能问题,因此在使用触发器时需要谨慎考虑。
文章正下方可以看到我的联系方式:鼠标“点击” 下面的 “威迪斯特-就是video system 微信名片”字样,就会出现我的二维码,欢迎沟通探讨。
更多推荐
所有评论(0)