DM触发器总结：提升数据库自动化的关键技术与实践-171主机测评

一、DM触发器基础

1.1 触发器定义与作用

触发器是数据库中的一种特殊对象，它是一个自动执行的存储过程，当特定事件(如INSERT, UPDATE, DELETE)发生时自动触发执行。触发器可以用于实现复杂的业务逻辑、数据完整性约束、审计日志等功能。

在DM数据库中，触发器具有以下作用：

实现复杂的数据完整性约束

自动记录数据变更历史

实现业务规则和逻辑

维护数据一致性

提供审计和监控功能

1.2 触发器类型

DM数据库支持多种类型的触发器，主要包括：

DML触发器：响应INSERT、UPDATE、DELETE等数据操作语言事件

语句级触发器：针对整个SQL语句触发一次
行级触发器：针对受影响的每一行触发一次

系统触发器：响应数据库事件，如数据库启动、关闭、用户登录等

INSTEAD OF触发器：替代原操作执行，常用于视图上的DML操作

BEFORE触发器：在操作前执行

AFTER触发器：在操作后执行

1.3 触发器语法结构

DM数据库中创建触发器的基本语法如下：

CREATE TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF}
{INSERT | UPDATE | DELETE}
ON table_name
[FOR EACH ROW [WHEN (condition)]]
BEGIN
— 触发器体
PL/SQL 代码块
END;

触发器体中可以包含：

变量声明

SQL语句

条件判断

异常处理

调用其他存储过程或函数

以下是一个示例触发器：

CREATE TRIGGER trg_employee_update
BEFORE UPDATE ON employee
FOR EACH ROW
BEGIN
IF :NEW.salary < :OLD.salary THEN
RAISE_APPLICATION_ERROR(-20001, '工资不能降低');
END IF;
END;

二、DM触发器详解

2.1 BEFORE触发器

BEFORE触发器在DML操作之前执行，常用于数据验证、默认值设置等场景。

2.1.1 BEFORE INSERT触发器

BEFORE INSERT触发器在插入数据前执行，可用于：

设置默认值

验证数据完整性

生成自动编号

示例：

CREATE TRIGGER trg_employee_before_insert
BEFORE INSERT ON employee
FOR EACH ROW
BEGIN
— 设置入职日期为当前日期
IF :NEW.hire_date IS NULL THEN
:NEW.hire_date := SYSDATE;
END IF;

— 生成员工ID
IF :NEW.employee_id IS NULL THEN
SELECT seq_employee_id.NEXTVAL INTO :NEW.employee_id FROM DUAL;
END IF;

— 验证邮箱格式
IF :NEW.email IS NOT NULL AND REGEXP_LIKE(:NEW.email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,4}$') = 0 THEN
RAISE_APPLICATION_ERROR(-20002, '邮箱格式不正确');
END IF;
END;

2.1.2 BEFORE UPDATE触发器

BEFORE UPDATE触发器在更新数据前执行，可用于：

验证修改合法性

修改即将更新的值

记录修改前后的差异

示例：

CREATE TRIGGER trg_employee_before_update
BEFORE UPDATE ON employee
FOR EACH ROW
BEGIN
— 工资调整验证
IF :NEW.salary != :OLD.salary AND :NEW.salary > :OLD.salary * 1.5 THEN
RAISE_APPLICATION_ERROR(-20003, '单次工资调整不能超过50%%');
END IF;

— 记录修改日志
INSERT INTO employee_change_log (
employee_id, change_type, old_value, new_value, change_time
) VALUES (
:NEW.employee_id, 'SALARY_UPDATE',
:OLD.salary, :NEW.salary, SYSDATE
);
END;

2.2 AFTER触发器

AFTER触发器在DML操作成功完成后执行，常用于审计、通知、数据同步等场景。

2.2.1 AFTER INSERT触发器

AFTER INSERT触发器在插入数据后执行，可用于：

记录审计日志

通知相关人员

触发其他业务流程

示例：

CREATE TRIGGER trg_employee_after_insert
AFTER INSERT ON employee
FOR EACH ROW
BEGIN
— 记录入职日志
INSERT INTO employee_audit_log (
action_type, employee_id, action_time, performed_by
) VALUES (
'INSERT', :NEW.employee_id, SYSDATE, USER
);

— 发送欢迎邮件
IF :NEW.email IS NOT NULL THEN
— 假设有一个发送邮件的存储过程
send_welcome_email(:NEW.email, :NEW.name);
END IF;

— 更新部门人数统计
UPDATE department SET employee_count = employee_count + 1
WHERE department_id = :NEW.department_id;
END;

2.2.2 AFTER UPDATE触发器

AFTER UPDATE触发器在更新数据后执行，可用于：

记录变更历史

通知相关人员变更信息

触发相关业务流程

示例：

CREATE TRIGGER trg_employee_after_update
AFTER UPDATE ON employee
FOR EACH ROW
BEGIN
— 记录变更日志
IF :NEW.salary != :OLD.salary THEN
INSERT INTO salary_change_history (
employee_id, old_salary, new_salary, change_date
) VALUES (
:NEW.employee_id, :OLD.salary, :NEW.salary, SYSDATE
);
END IF;

— 如果职位变更，触发流程审批
IF :NEW.position_id != :OLD.position_id THEN
position_change_request(
:NEW.employee_id,
:OLD.position_id,
:NEW.position_id,
:NEW.effective_date
);
END IF;
END;

2.2.3 AFTER DELETE触发器

AFTER DELETE触发器在删除数据后执行，可用于：

记录删除日志

处理相关联的数据

通知相关人员

示例：

CREATE TRIGGER trg_employee_after_delete
AFTER DELETE ON employee
FOR EACH ROW
BEGIN
— 记录离职日志
INSERT INTO employee_audit_log (
action_type, employee_id, action_time, performed_by
) VALUES (
'DELETE', :OLD.employee_id, SYSDATE, USER
);

— 更新部门人数统计
UPDATE department SET employee_count = employee_count – 1
WHERE department_id = :OLD.department_id;

— 保留联系方式到历史表
INSERT INTO employee_contact_history (
employee_id, name, phone, email, deletion_date
) VALUES (
:OLD.employee_id, :OLD.name, :OLD.phone, :OLD.email, SYSDATE
);
END;

2.3 INSTEAD OF触发器

INSTEAD OF触发器用于替代原操作的执行，常用于视图上的DML操作。

2.3.1 INSTEAD OF INSERT触发器

当直接在视图上插入数据时，INSTEAD OF INSERT触发器会替代标准的INSERT操作执行。

示例：

— 创建一个基于多个表的视图
CREATE VIEW v_employee_department AS
SELECT e.employee_id, e.name, e.position_id, d.department_name, d.location
FROM employee e JOIN department d ON e.department_id = d.department_id;

— 在视图上创建INSTEAD OF INSERT触发器
CREATE TRIGGER trg_v_emp_dept_instead_of_insert
INSTEAD OF INSERT ON v_employee_department
FOR EACH ROW
BEGIN
— 先插入部门数据（如果部门不存在）
INSERT INTO department (department_id, department_name, location)
SELECT :NEW.department_id, :NEW.department_name, :NEW.location
FROM dual WHERE NOT EXISTS (
SELECT 1 FROM department WHERE department_id = :NEW.department_id
);

— 再插入员工数据
INSERT INTO employee (employee_id, name, position_id, department_id)
VALUES (:NEW.employee_id, :NEW.name, :NEW.position_id, :NEW.department_id);
END;

2.3.2 INSTEAD OF UPDATE触发器

当直接在视图上更新数据时，INSTEAD OF UPDATE触发器会替代标准的UPDATE操作执行。

示例：

CREATE TRIGGER trg_v_emp_dept_instead_of_update
INSTEAD OF UPDATE ON v_employee_department
FOR EACH ROW
BEGIN
— 更新部门信息
IF :NEW.department_name != :OLD.department_name OR :NEW.location != :OLD.location THEN
UPDATE department
SET department_name = :NEW.department_name, location = :NEW.location
WHERE department_id = :NEW.department_id;
END IF;

— 更新员工信息
IF :NEW.name != :OLD.name OR :NEW.position_id != :OLD.position_id THEN
UPDATE employee
SET name = :NEW.name, position_id = :NEW.position_id
WHERE employee_id = :NEW.employee_id;
END IF;
END;

2.3.3 INSTEAD OF DELETE触发器

当直接在视图上删除数据时，INSTEAD OF DELETE触发器会替代标准的DELETE操作执行。

示例：

CREATE TRIGGER trg_v_emp_dept_instead_of_delete
INSTEAD OF DELETE ON v_employee_department
FOR EACH ROW
BEGIN
— 先删除员工数据
DELETE FROM employee WHERE employee_id = :OLD.employee_id;

— 再删除部门数据（如果部门没有其他员工）
DELETE FROM department
WHERE department_id = :OLD.department_id AND NOT EXISTS (
SELECT 1 FROM employee WHERE department_id = :OLD.department_id
);
END;

三、DM触发器高级应用

3.1 触发器性能优化

触发器虽然功能强大，但使用不当会影响数据库性能。以下是一些优化建议：

3.1.1 减少触发器复杂度

保持触发器逻辑简单明了

避免在触发器中执行复杂计算或长时间运行的操作

将复杂逻辑移到存储过程中，由触发器调用

3.1.2 控制触发器数量

避免对同一表创建过多触发器

合并多个小触发器为一个大的触发器

评估每个触发器的必要性

3.1.3 使用条件判断

CREATE TRIGGER trg_employee_audit
BEFORE UPDATE ON employee
FOR EACH ROW
WHEN (:NEW.salary != :OLD.salary OR :NEW.position_id != :OLD.position_id)
BEGIN
— 只有当工资或职位发生变化时才记录审计日志
INSERT INTO employee_audit_log (
employee_id, action_type, old_value, new_value, change_time
) VALUES (
:NEW.employee_id, 'UPDATE',
:OLD.salary || '/' || :OLD.position_id,
:NEW.salary || '/' || :NEW.position_id,
SYSDATE
);
END;

3.1.4 事务处理

CREATE OR REPLACE TRIGGER trg_complex_operation
AFTER INSERT ON large_table
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION; — 自治事务
BEGIN
— 使用自治事务记录日志，避免影响主事务
INSERT INTO operation_log (operation_id, operation_time, status)
VALUES (:NEW.id, SYSDATE, 'COMPLETED');

COMMIT;
END;

3.2 触发器调试方法

3.2.1 使用DBMS_OUTPUT

CREATE OR REPLACE TRIGGER trg_debug
BEFORE INSERT ON test_table
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('即将插入数据: ' || :NEW.id || ', ' || :NEW.name);

— 调试条件
IF :NEW.name IS NULL THEN
DBMS_OUTPUT.PUT_LINE('错误: name不能为空');
RAISE_APPLICATION_ERROR(-20001, 'name不能为空');
END IF;
END;

3.2.2 使用临时表记录调试信息

CREATE TABLE debug_log (
id NUMBER PRIMARY KEY,
trigger_name VARCHAR2(100),
message VARCHAR2(4000),
log_time TIMESTAMP
);

CREATE OR REPLACE TRIGGER trg_with_debug
BEFORE UPDATE ON employee
FOR EACH ROW
BEGIN
— 记录调试信息到临时表
INSERT INTO debug_log (id, trigger_name, message, log_time)
VALUES (debug_seq.NEXTVAL, 'trg_with_debug',
'更新前: ' || :OLD.salary || ' -> ' || :NEW.salary,
SYSDATE);

— 业务逻辑
IF :NEW.salary > 10000 THEN
:NEW.bonus := :NEW.salary * 0.1;
ELSE
:NEW.bonus := 0;
END IF;
END;

3.2.3 使用异常处理

CREATE OR REPLACE TRIGGER trg_with_exception_handling
BEFORE UPDATE ON employee
FOR EACH ROW
BEGIN
BEGIN
— 业务逻辑
IF :NEW.salary < 0 THEN
RAISE_APPLICATION_ERROR(-20001, '工资不能为负数');
END IF;

— 计算奖金
IF :NEW.performance_rating = 'A' THEN
:NEW.bonus := :NEW.salary * 0.2;
ELSIF :NEW.performance_rating = 'B' THEN
:NEW.bonus := :NEW.salary * 0.1;
ELSE
:NEW.bonus := 0;
END IF;

EXCEPTION
WHEN OTHERS THEN
— 记录错误信息
INSERT INTO error_log (error_time, error_code, error_message, table_name, operation)
VALUES (SYSDATE, SQLCODE, SQLERRM, 'EMPLOYEE', 'UPDATE');

— 重新抛出异常
RAISE;
END;
END;

3.3 触发器安全管理

3.3.1 权限控制

— 只有特定角色可以创建触发器
GRANT CREATE TRIGGER TO hr_manager, dba;

— 限制特定用户对触发器的访问
GRANT EXECUTE ON trg_salary_audit TO hr_system, payroll_system;
REVOKE EXECUTE ON trg_salary_audit FROM public;

3.3.2 敏感数据保护

CREATE OR REPLACE TRIGGER trg_sensitive_data
BEFORE INSERT OR UPDATE ON employee
FOR EACH ROW
BEGIN
— 加密敏感字段
IF :NEW.salary IS NOT NULL THEN
:NEW.salary_encrypted = DBMS_CRYPTO.ENCRYPT(
UTL_I18N.STRING_TO_RAW(:NEW.salary, 'AL32UTF8'),
DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5,
'encryption_key'
);
END IF;

— 记录敏感数据访问
IF INSERTING THEN
INSERT INTO data_access_audit (
table_name, operation, record_id, accessed_by, access_time
) VALUES (
'EMPLOYEE', 'INSERT', :NEW.id, USER, SYSDATE
);
ELSIF UPDATING THEN
INSERT INTO data_access_audit (
table_name, operation, record_id, accessed_by, access_time
) VALUES (
'EMPLOYEE', 'UPDATE', :NEW.id, USER, SYSDATE
);
END IF;
END;

3.3.3 审计触发器

— 创建审计触发器记录所有数据变更
CREATE OR REPLACE TRIGGER trg_audit_all
AFTER INSERT OR UPDATE OR DELETE ON employee
FOR EACH ROW
DECLARE
v_operation VARCHAR2(10);
BEGIN
IF INSERTING THEN
v_operation := 'INSERT';
ELSIF UPDATING THEN
v_operation := 'UPDATE';
ELSIF DELETING THEN
v_operation := 'DELETE';
END IF;

— 记录到审计表
INSERT INTO employee_audit (
operation_type, employee_id, changed_by, change_time,
old_data, new_data
) VALUES (
v_operation,
:NEW.employee_id,
USER,
SYSDATE,
CASE WHEN UPDING OR DELETING THEN :OLD.name END,
CASE WHEN INSERTING OR UPDING THEN :NEW.name END
);
END;

3.3.4 触发器禁用与启用

— 禁用触发器
ALTER TRIGGER trg_employee_audit DISABLE;

— 启用触发器
ALTER TRIGGER trg_employee_audit ENABLE;

— 条件性禁用触发器
BEGIN
— 在大批量数据操作前禁用触发器
EXECUTE IMMEDIATE 'ALTER TRIGGER trg_employee_audit DISABLE';

— 执行大批量数据操作
EXECUTE IMMEDIATE 'INSERT INTO employee (…) SELECT … FROM large_source_table';

— 操作完成后重新启用触发器
EXECUTE IMMEDIATE 'ALTER TRIGGER trg_employee_audit ENABLE';

— 记录批量操作事件
INSERT INTO batch_operation_log (operation_type, record_count, operation_time)
VALUES ('EMPLOYEE_INSERT', SQL%ROWCOUNT, SYSDATE);
END;

DM触发器工作流程图

#publish-mermaid-1787159026780-0{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-1787159026780-0 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-1787159026780-0 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-1787159026780-0 .error-icon{fill:#552222;}#publish-mermaid-1787159026780-0 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-1787159026780-0 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-1787159026780-0 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-1787159026780-0 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-1787159026780-0 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-1787159026780-0 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-1787159026780-0 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-1787159026780-0 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-1787159026780-0 .marker.cross{stroke:#333333;}#publish-mermaid-1787159026780-0 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-1787159026780-0 p{margin:0;}#publish-mermaid-1787159026780-0 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#publish-mermaid-1787159026780-0 .cluster-label text{fill:#333;}#publish-mermaid-1787159026780-0 .cluster-label span{color:#333;}#publish-mermaid-1787159026780-0 .cluster-label span p{background-color:transparent;}#publish-mermaid-1787159026780-0 .label text,#publish-mermaid-1787159026780-0 span{fill:#333;color:#333;}#publish-mermaid-1787159026780-0 .node rect,#publish-mermaid-1787159026780-0 .node circle,#publish-mermaid-1787159026780-0 .node ellipse,#publish-mermaid-1787159026780-0 .node polygon,#publish-mermaid-1787159026780-0 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1787159026780-0 .rough-node .label text,#publish-mermaid-1787159026780-0 .node .label text,#publish-mermaid-1787159026780-0 .image-shape .label,#publish-mermaid-1787159026780-0 .icon-shape .label{text-anchor:middle;}#publish-mermaid-1787159026780-0 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#publish-mermaid-1787159026780-0 .rough-node .label,#publish-mermaid-1787159026780-0 .node .label,#publish-mermaid-1787159026780-0 .image-shape .label,#publish-mermaid-1787159026780-0 .icon-shape .label{text-align:center;}#publish-mermaid-1787159026780-0 .node.clickable{cursor:pointer;}#publish-mermaid-1787159026780-0 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#publish-mermaid-1787159026780-0 .arrowheadPath{fill:#333333;}#publish-mermaid-1787159026780-0 .edgePath .path{stroke:#333333;stroke-width:1px;}#publish-mermaid-1787159026780-0 .flowchart-link{stroke:#333333;fill:none;}#publish-mermaid-1787159026780-0 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1787159026780-0 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#publish-mermaid-1787159026780-0 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1787159026780-0 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#publish-mermaid-1787159026780-0 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#publish-mermaid-1787159026780-0 .cluster text{fill:#333;}#publish-mermaid-1787159026780-0 .cluster span{color:#333;}#publish-mermaid-1787159026780-0 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#publish-mermaid-1787159026780-0 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#publish-mermaid-1787159026780-0 rect.text{fill:none;stroke-width:0;}#publish-mermaid-1787159026780-0 .icon-shape,#publish-mermaid-1787159026780-0 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1787159026780-0 .icon-shape p,#publish-mermaid-1787159026780-0 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#publish-mermaid-1787159026780-0 .icon-shape .label rect,#publish-mermaid-1787159026780-0 .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1787159026780-0 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#publish-mermaid-1787159026780-0 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#publish-mermaid-1787159026780-0 .node .neo-node{stroke:#9370DB;}#publish-mermaid-1787159026780-0 [data-look=\”neo\”].node rect,#publish-mermaid-1787159026780-0 [data-look=\”neo\”].cluster rect,#publish-mermaid-1787159026780-0 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1787159026780-0 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-1787159026780-0 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1787159026780-0 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1787159026780-0 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-1787159026780-0 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1787159026780-0 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-1787159026780-0 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1787159026780-0 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1787159026780-0 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

开始

触发器类型

DML触发器

系统触发器

INSTEAD OF触发器

语句级触发器

行级触发器

BEFORE触发器

AFTER触发器

BEFORE触发器

AFTER触发器

触发器执行流程图

#publish-mermaid-1787159026826-1{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-1787159026826-1 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-1787159026826-1 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-1787159026826-1 .error-icon{fill:#552222;}#publish-mermaid-1787159026826-1 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-1787159026826-1 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-1787159026826-1 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-1787159026826-1 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-1787159026826-1 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-1787159026826-1 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-1787159026826-1 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-1787159026826-1 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-1787159026826-1 .marker.cross{stroke:#333333;}#publish-mermaid-1787159026826-1 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-1787159026826-1 p{margin:0;}#publish-mermaid-1787159026826-1 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#publish-mermaid-1787159026826-1 .cluster-label text{fill:#333;}#publish-mermaid-1787159026826-1 .cluster-label span{color:#333;}#publish-mermaid-1787159026826-1 .cluster-label span p{background-color:transparent;}#publish-mermaid-1787159026826-1 .label text,#publish-mermaid-1787159026826-1 span{fill:#333;color:#333;}#publish-mermaid-1787159026826-1 .node rect,#publish-mermaid-1787159026826-1 .node circle,#publish-mermaid-1787159026826-1 .node ellipse,#publish-mermaid-1787159026826-1 .node polygon,#publish-mermaid-1787159026826-1 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1787159026826-1 .rough-node .label text,#publish-mermaid-1787159026826-1 .node .label text,#publish-mermaid-1787159026826-1 .image-shape .label,#publish-mermaid-1787159026826-1 .icon-shape .label{text-anchor:middle;}#publish-mermaid-1787159026826-1 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#publish-mermaid-1787159026826-1 .rough-node .label,#publish-mermaid-1787159026826-1 .node .label,#publish-mermaid-1787159026826-1 .image-shape .label,#publish-mermaid-1787159026826-1 .icon-shape .label{text-align:center;}#publish-mermaid-1787159026826-1 .node.clickable{cursor:pointer;}#publish-mermaid-1787159026826-1 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#publish-mermaid-1787159026826-1 .arrowheadPath{fill:#333333;}#publish-mermaid-1787159026826-1 .edgePath .path{stroke:#333333;stroke-width:1px;}#publish-mermaid-1787159026826-1 .flowchart-link{stroke:#333333;fill:none;}#publish-mermaid-1787159026826-1 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1787159026826-1 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#publish-mermaid-1787159026826-1 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1787159026826-1 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#publish-mermaid-1787159026826-1 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#publish-mermaid-1787159026826-1 .cluster text{fill:#333;}#publish-mermaid-1787159026826-1 .cluster span{color:#333;}#publish-mermaid-1787159026826-1 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#publish-mermaid-1787159026826-1 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#publish-mermaid-1787159026826-1 rect.text{fill:none;stroke-width:0;}#publish-mermaid-1787159026826-1 .icon-shape,#publish-mermaid-1787159026826-1 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1787159026826-1 .icon-shape p,#publish-mermaid-1787159026826-1 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#publish-mermaid-1787159026826-1 .icon-shape .label rect,#publish-mermaid-1787159026826-1 .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1787159026826-1 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#publish-mermaid-1787159026826-1 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#publish-mermaid-1787159026826-1 .node .neo-node{stroke:#9370DB;}#publish-mermaid-1787159026826-1 [data-look=\”neo\”].node rect,#publish-mermaid-1787159026826-1 [data-look=\”neo\”].cluster rect,#publish-mermaid-1787159026826-1 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1787159026826-1 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-1787159026826-1 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1787159026826-1 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1787159026826-1 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-1787159026826-1 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1787159026826-1 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-1787159026826-1 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1787159026826-1 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1787159026826-1 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}满足不满足INSERTUPDATEDELETE

触发事件

检查条件

执行触发器体

跳过触发器

操作类型

插入前/后处理

更新前/后处理

删除前/后处理

结束

触发器性能优化流程图

#publish-mermaid-1787159026882-2{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-1787159026882-2 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-1787159026882-2 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-1787159026882-2 .error-icon{fill:#552222;}#publish-mermaid-1787159026882-2 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-1787159026882-2 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-1787159026882-2 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-1787159026882-2 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-1787159026882-2 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-1787159026882-2 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-1787159026882-2 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-1787159026882-2 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-1787159026882-2 .marker.cross{stroke:#333333;}#publish-mermaid-1787159026882-2 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-1787159026882-2 p{margin:0;}#publish-mermaid-1787159026882-2 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#publish-mermaid-1787159026882-2 .cluster-label text{fill:#333;}#publish-mermaid-1787159026882-2 .cluster-label span{color:#333;}#publish-mermaid-1787159026882-2 .cluster-label span p{background-color:transparent;}#publish-mermaid-1787159026882-2 .label text,#publish-mermaid-1787159026882-2 span{fill:#333;color:#333;}#publish-mermaid-1787159026882-2 .node rect,#publish-mermaid-1787159026882-2 .node circle,#publish-mermaid-1787159026882-2 .node ellipse,#publish-mermaid-1787159026882-2 .node polygon,#publish-mermaid-1787159026882-2 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1787159026882-2 .rough-node .label text,#publish-mermaid-1787159026882-2 .node .label text,#publish-mermaid-1787159026882-2 .image-shape .label,#publish-mermaid-1787159026882-2 .icon-shape .label{text-anchor:middle;}#publish-mermaid-1787159026882-2 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#publish-mermaid-1787159026882-2 .rough-node .label,#publish-mermaid-1787159026882-2 .node .label,#publish-mermaid-1787159026882-2 .image-shape .label,#publish-mermaid-1787159026882-2 .icon-shape .label{text-align:center;}#publish-mermaid-1787159026882-2 .node.clickable{cursor:pointer;}#publish-mermaid-1787159026882-2 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#publish-mermaid-1787159026882-2 .arrowheadPath{fill:#333333;}#publish-mermaid-1787159026882-2 .edgePath .path{stroke:#333333;stroke-width:1px;}#publish-mermaid-1787159026882-2 .flowchart-link{stroke:#333333;fill:none;}#publish-mermaid-1787159026882-2 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1787159026882-2 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#publish-mermaid-1787159026882-2 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1787159026882-2 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#publish-mermaid-1787159026882-2 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#publish-mermaid-1787159026882-2 .cluster text{fill:#333;}#publish-mermaid-1787159026882-2 .cluster span{color:#333;}#publish-mermaid-1787159026882-2 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#publish-mermaid-1787159026882-2 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#publish-mermaid-1787159026882-2 rect.text{fill:none;stroke-width:0;}#publish-mermaid-1787159026882-2 .icon-shape,#publish-mermaid-1787159026882-2 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1787159026882-2 .icon-shape p,#publish-mermaid-1787159026882-2 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#publish-mermaid-1787159026882-2 .icon-shape .label rect,#publish-mermaid-1787159026882-2 .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1787159026882-2 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#publish-mermaid-1787159026882-2 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#publish-mermaid-1787159026882-2 .node .neo-node{stroke:#9370DB;}#publish-mermaid-1787159026882-2 [data-look=\”neo\”].node rect,#publish-mermaid-1787159026882-2 [data-look=\”neo\”].cluster rect,#publish-mermaid-1787159026882-2 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1787159026882-2 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-1787159026882-2 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1787159026882-2 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1787159026882-2 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-1787159026882-2 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1787159026882-2 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-1787159026882-2 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1787159026882-2 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1787159026882-2 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

优化触发器

减少复杂度

控制数量

使用条件判断

事务处理

保持逻辑简单

避免复杂计算

避免过多触发器

合并小触发器

使用WHEN子句

使用自治事务

触发器安全管理流程图

#publish-mermaid-1787159026932-3{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-1787159026932-3 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-1787159026932-3 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-1787159026932-3 .error-icon{fill:#552222;}#publish-mermaid-1787159026932-3 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-1787159026932-3 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-1787159026932-3 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-1787159026932-3 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-1787159026932-3 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-1787159026932-3 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-1787159026932-3 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-1787159026932-3 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-1787159026932-3 .marker.cross{stroke:#333333;}#publish-mermaid-1787159026932-3 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-1787159026932-3 p{margin:0;}#publish-mermaid-1787159026932-3 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#publish-mermaid-1787159026932-3 .cluster-label text{fill:#333;}#publish-mermaid-1787159026932-3 .cluster-label span{color:#333;}#publish-mermaid-1787159026932-3 .cluster-label span p{background-color:transparent;}#publish-mermaid-1787159026932-3 .label text,#publish-mermaid-1787159026932-3 span{fill:#333;color:#333;}#publish-mermaid-1787159026932-3 .node rect,#publish-mermaid-1787159026932-3 .node circle,#publish-mermaid-1787159026932-3 .node ellipse,#publish-mermaid-1787159026932-3 .node polygon,#publish-mermaid-1787159026932-3 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1787159026932-3 .rough-node .label text,#publish-mermaid-1787159026932-3 .node .label text,#publish-mermaid-1787159026932-3 .image-shape .label,#publish-mermaid-1787159026932-3 .icon-shape .label{text-anchor:middle;}#publish-mermaid-1787159026932-3 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#publish-mermaid-1787159026932-3 .rough-node .label,#publish-mermaid-1787159026932-3 .node .label,#publish-mermaid-1787159026932-3 .image-shape .label,#publish-mermaid-1787159026932-3 .icon-shape .label{text-align:center;}#publish-mermaid-1787159026932-3 .node.clickable{cursor:pointer;}#publish-mermaid-1787159026932-3 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#publish-mermaid-1787159026932-3 .arrowheadPath{fill:#333333;}#publish-mermaid-1787159026932-3 .edgePath .path{stroke:#333333;stroke-width:1px;}#publish-mermaid-1787159026932-3 .flowchart-link{stroke:#333333;fill:none;}#publish-mermaid-1787159026932-3 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1787159026932-3 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#publish-mermaid-1787159026932-3 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1787159026932-3 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#publish-mermaid-1787159026932-3 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#publish-mermaid-1787159026932-3 .cluster text{fill:#333;}#publish-mermaid-1787159026932-3 .cluster span{color:#333;}#publish-mermaid-1787159026932-3 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#publish-mermaid-1787159026932-3 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#publish-mermaid-1787159026932-3 rect.text{fill:none;stroke-width:0;}#publish-mermaid-1787159026932-3 .icon-shape,#publish-mermaid-1787159026932-3 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1787159026932-3 .icon-shape p,#publish-mermaid-1787159026932-3 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#publish-mermaid-1787159026932-3 .icon-shape .label rect,#publish-mermaid-1787159026932-3 .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1787159026932-3 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#publish-mermaid-1787159026932-3 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#publish-mermaid-1787159026932-3 .node .neo-node{stroke:#9370DB;}#publish-mermaid-1787159026932-3 [data-look=\”neo\”].node rect,#publish-mermaid-1787159026932-3 [data-look=\”neo\”].cluster rect,#publish-mermaid-1787159026932-3 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1787159026932-3 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-1787159026932-3 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1787159026932-3 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1787159026932-3 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-1787159026932-3 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1787159026932-3 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-1787159026932-3 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1787159026932-3 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1787159026932-3 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

触发器安全

权限控制

敏感数据保护

审计触发器

禁用与启用

角色限制

访问控制

数据加密

访问记录

变更审计

操作日志

批量操作优化

维护窗口