MySQL触发器

什么是触发器？

触发器是一种数据库对象，就是在数据表中发生插入、删除、更新操作之类的操作，然后自动触发了预先编好的若干条SQL语句的执行。

特点：

触发事件的操作和触发器里面的 SQL 语句是一个事务操作，具有原子性，要么全部执行，要么都不执行。

用法

CREATE TRIGGER trigger_name触发器名称 trigger_time触发事件 trigger_event触发事件 
ON table_name表名称 FOR EACH ROW [trigger_order] 
trigger_body

Trigger_time: 触发器执行时间： AFTER | BEFORE
Trigger_event: 触发器触发的事件： INSERT | UPDATE | DELETE
FOR EACH ROW：表示任何一条记录上的操作满足触发事件都会触发该触发器
Table_name: 表示触发事件操作表的名字
Trigger_body: 创建触发器的SQL语句

具体应用：

BEFORE INSERT ： 在插入数据前，检测插入数据是否符合业务逻辑，如不符合返回错误信息。

create trigger val_age
before insert on test1
for each row
if new age > 18 then
signal sqlsatae '20000'
set message_text = 'age过大'
end if;

插入错误数据：

insert into test1 values(22);

结果：

ERROR 1644(20000) : age太大

AFTER INSERT ： 在表 A 创建新账户后，将创建成功信息自动写入表 B 中。典型的对账处理

BEFORE UPDATE ：在更新数据前，检测更新数据是否符合业务逻辑，如不符合返回错误信息

CREATE TRIGGER validate_customer_level 
BEFORE UPDATE ON customers 
FOR EACH ROW 
    IF OLD.level='VIP' THEN SIGNAL 
        SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'VIP 级别客户不能降级为普通级别客户'; 
END IF

AFTER UPDATE ：在更新数据后，将操作行为记录在 log 中

 

CREATE TRIGGER log_sales_updates 
AFTER UPDATE 
ON sales 
FOR EACH ROW 
Insert into audit_log(sales_id, previous_amount, new_amount, updated_by, updated_on) VALUES (NEW.sales_id新数据,OLD.sales_amount老数据, NEW.sales_amount,(SELECT USER()), NOW())

BEFORE DELETE ：在删除数据前，检查是否有关联数据，如有，停止删除操作。

CREATE TRIGGER validate_related_records 
BEFORE DELETE ON customers 
FOR EACH ROW 
IF OLD.customer_id in (select customer_id from sales) THEN 
SIGNAL SQLSTATE '45000' 
SET MESSAGE_TEXT = '这位客户有相关联的销售记录，不能删除。'; 
END IF;

AFTER DELETE ：删除表 A 信息后，自动删除表 B 中与表 A 相关联的信息。

CREATE TRIGGER delete_related_info 
AFTER DELETE 
ON sales FOR 
EACH ROW 
Delete from customers where customer_id=OLD.customer_id;

查看存储器：

show TRIGGERS;

删除触发器：

delete trigger trigger_name;

触发器优点：

SQL触发器提供了检查数据完整性的替代方法。

SQL触发器可以捕获数据库业务逻辑中的错误。

SQL触发器提供了运行计划任务的另一种方法。通过使用SQL触发器，不必等待运行计划的任务，因为在对表中的数据进行更改之前或之后自动调用触发器

SQL触发器对于审核表中数据的更改非常有用。

缺点：

SQL触发器只能提供拓展验证，并且无法替换所有验证，一些简单的验证必须在应用层完成

从客户端应用程序调用和执行SQL触发器不可见，因此很难弄清数据库层中发生的情况。

SQL触发器可能会增加数据库服务器的开销。

触发器在有限的场合能够发挥其优势，比如统计数据、数据表变更日志等。但是也会有一些缺陷，比如大数据量的更新由于逐行触发，会降低效率。还有就是， MyISAM 引擎无法保障原子性。因此，要根据 应用场景是否要是有触发器。