1. 什么是触发器及触发器的核心作用和生产环境的使用场景

触发器简介

数据库触发器绑定在指定的数据表上，由特定的数据库事件进行触发执行触发器中预定义程序

以MySQL为例子，仅允许绑定在永久物理表上，不允许绑定在视图或临时表中

拥有三种触发条件：插入数据，删除数据和更新数据

拥有两种触发时机：执行前和执行后

触发粒度：仅支持行级触发，Oracle和PostgreSQL等额外支持语句级触发

数据访问：通过Old关键字可访问原始数据，通过New关键字可访问待写入数据

核心作用

对数据库数据做安全审计与合规追溯

数据安全与风险防控

确保业务数据完整性和一致性

生产环境高频场景

在数据敏感的场景下，触发器可实现数据安全审计，确保数据追溯

在普通业务场景下，触发器对数据库中的数据完整性做保障

在全行业场景下，触发器可对数据库做防误删与数据备份场景

在高并发场景下的风险防控和限流拦截

触发器核心红线

禁止在A表的触发器，对A表做增删改行为，将触发MySQL1442错误，仅允许在Before触发器中通过Set关键字修改待写入字段值

禁止在触发器中写复杂业务逻辑，将拖慢数据库性能拖慢

禁止在触发器做嵌套/递归行为，将导致业务锁死

禁止在高并发场景使用行级触发器

禁止使用触发器替代应用层做核心业务逻辑

2. 触发器的触发时机，触发事件和触发规则

触发时机：触发前和触发后

触发事件：增，删和改

触发规则：MySQL仅支持行级触发

3. 触发器的Old和New关键字代表意思，在Insert，Update和Delete三类触发事件中可用性

触发器关键字：Old代表旧数据，New代表新数据

Insert语句中不可用Old，可用New

Update语句中可用Old，可用New

Delete语句中可用Old，不可用New

4. 解释MySQL中1442错误的触发原因，以及生产环境中对应的解决方案

MySQL触发器禁止在A表的触发器上，对A表做增删改行为

在生产环境中使用Before关键字+Set New.字段的形式对A表字段值做修改

5. 外键级联和触发器的核心区别，在生产环境中做数据完整性校验如果使用

外键级联：依附于外键约束，属于InnoDB引擎内置的声明式引用完整性约束，核心设计目标为了保证跨表关联数据的绝对一致性，行为固不可以自定义

触发器：依附于数据表上，核心设计目标为了实现灵活的自定义业务规则与全场景数据控制

6. MySQL触发器是否支持事务？触发器的执行逻辑和原SQL语句的事务关系？当触发器报错，是否影响原SQL语句？

MySQL触发器不支持独立事务

触发器和触发SQL语句处于同一个事务中

  • 一起成功提交

  • 一起失败回滚

触发器内执行SQL报错，触发器和触发SQL均回滚

触发器内触发逻辑不支持事务关键字

在MySQL中仅InnoDB支持事务

7. 实现触发器，对用户表的Insert行为做审计功能，将插入的数据内容写入审计日志表中

建表语句

-- 创建用户表
CREATE TABLE USER (
    ID INT,
    NAME VARCHAR(50),
    AGE INT
);

-- 创建审计日志表
CREATE TABLE AUDIT_LOG (
    INSERT_ID INT,
    INSERT_NAME VARCHAR(50),
    INSERT_AGE INT,
    INSERT_TIME DATETIME,
    INSERT_TABLE VARCHAR(50)
);

触发器语句

CREATE TRIGGER TRG_INSERT_AFTER AFTER INSERT ON USER FOR EACH ROW
BEGIN
    INSERT INTO AUDIT_LOG ( INSERT_ID, INSERT_NAME, INSERT_AGE, INSERT_TIME, INSERT_TABLE ) 
  VALUE
  ( NEW.ID, NEW.NAME, NEW.AGE, NOW(), 'USER' );
END;

测试语句

INSERT INTO USER (ID,NAME,AGE) VALUES (1,'AAA',20),(2,'BBB',22),(3,'CCC',18);
COMMIT;

8. 实现触发器，对订单表的Update行为做数据修改，当Status字段值发生修改时，修改订单时间为当前时间

建表语句

-- 创建订单表
CREATE TABLE LOCATION_ORDER (
    ID INT,
    LOCATION VARCHAR(100),
    STATUS VARCHAR(30),
    CREATE_TIME DATETIME
);

触发器语句

DELIMITER //

CREATE TRIGGER TRG_UPDATE
BEFORE UPDATE ON LOCATION_ORDER
FOR EACH ROW
BEGIN
  IF NEW.STATUS <> OLD.STATUS THEN
    SET NEW.CREATE_TIME = NOW();
  END IF;
END //

DELIMITER ;

测试语句

-- 插入数据
INSERT INTO LOCATION_ORDER (ID,LOCATION,STATUS,CREATE_TIME) VALUES (1,'AAA','1',NOW()),(2,'BBB','0',NOW()),(3,'CCC','1',NOW());
-- 更新数据
UPDATE LOCATION_ORDER SET LOCATION = 'ABCDEFG' WHERE ID = 1;

9. 实现触发器，对学生表的Delete行为做备份，将删除行内容写入学生备份表

建表语句

-- 创建学生表
CREATE TABLE STUDENT (
    ID INT,
    NAME VARCHAR(50),
    AGE INT
);
-- 创建学生备用表
CREATE TABLE STUDENT_BAK (
    ID INT,
    NAME VARCHAR(50),
    AGE INT
);

触发器语句

CREATE TRIGGER TRG_DELETE AFTER DELETE ON STUDENT FOR EACH ROW
BEGIN
    INSERT INTO STUDENT_BAK ( ID, NAME, AGE ) 
  VALUE
  ( OLD.ID, OLD.NAME, OLD.AGE );
END;

测试语句

-- 插入数据
INSERT INTO STUDENT (ID,NAME,AGE) VALUES (1,'AAA',18),(2,'BBB',20),(3,'CCC',22);
-- 删除数据
DELETE FROM STUDENT WHERE ID = 1;

y