2. 首页 > Oracle教程 > 正文

Oracle教程FG070-PL/SQL触发器

教程整理：风哥教程  |  更新时间：2026-01-05  |  教程分类：Oracle教程  |  文档学习：4

其中：

  • BE FOR
    E | AFTER | IN
    STEAD OF    ：指定触发器的执行时机
  • INSERT
    | UPDATE
    | DELETE
    ：指定触发器响应的DML操作
  • OF column_name：指定触发器只在特定列被修改时触发
  • ON table_name：指定触发器所在的表
  • FOR
    EACH ROW    ：指定为行级触发器，否则为语句级触发器
  • WHEN (condition)：指定触发器的执行条件
更多学习教程公众号风哥教程itpux_com

Part02-生产环境规划与建议

2.1 触发器设计原则

建议：在设计PL/SQL触发器时，应遵循以下原则：

  • 触发器应该只做一件事，并且做好
  • 触发器的逻辑应该简单，避免复杂的业务逻辑
  • 触发器应该有明确的命名规范，便于识别和管理
  • 触发器应该有适当的注释，说明其功能和使用方法
  • 避免在触发器中执行大量的操作，影响性能

2.2 触发器性能考虑

建议：在编写PL/SQL触发器时，应考虑以下性能因素：

  • 避免在触发器中执行大量的SQL语句
  • 避免在触发器中使用复杂的逻辑
  • 使用适当的索引来提高查询性能
  • 考虑使用批量操作来减少触发器的执行次数
  • 定期监控和优化触发器的性能

2.3 触发器安全性考虑

建议：在编写PL/SQL触发器时，应考虑以下安全性因素：

  • 避免在触发器中使用动态SQL
  • 对输入参数进行验证，确保参数的有效性
  • 使用适当的权限控制，限制对触发器的访问权限
  • 避免在触发器中处理敏感数据，保护数据的安全性
  • 定期审计触发器的使用情况，发现和解决潜在的安全问题
from oracle:www.itpux.com

Part03-生产环境项目实施方案

在生产环境中实施PL/SQL触发器时，应遵循以下步骤：

  1. 需求分析：明确触发器的功能和要求
  2. 设计：设计触发器的结构和逻辑
  3. 开发：编写触发器的代码
  4. 测试：测试触发器的功能和性能
  5. 部署：将触发器部署到生产环境
  6. 监控：监控触发器的使用情况和性能
  7. 维护：定期维护和优化触发器
更多视频教程www.fgedu.net.cn

Part04-生产案例与实战讲解

4.1 DML触发器的创建与使用

DML触发器是最常用的触发器类型，用于响应INSERT
、UPDATE
、DELETE
操作，下面是一个创建和使用DML触发器的例子：

— 创建表
CREATE TABLE
fgedu_employees (
employee_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
salary NUMBER,
hire_date DATE,
last_update_date DATE,
last_updated_by VARCHAR2(50)
);

— 创建触发器：在插入或更新时自动设置更新时间和更新人
CREATE OR REPLACE
TRIGGER
fgedu_employees_before_insert_update
BE FOR
E INSERT
OR UPDATE
ON fgedu_employees
FOR
EACH ROW
BEGIN
:NEW.last_update_date :=
SYSDATE;
:NEW.last_updated_by :=
USER;
END
;
/

— 创建触发器：在删除时记录删除的员工信息
CREATE OR REPLACE
TRIGGER
fgedu_employees_before_delete
BE FOR
E DELETE
ON fgedu_employees
FOR
EACH ROW
DECLARE
v_delete_time DATE;
BEGIN
v_delete_time :=
SYSDATE;
DBMS_OUTPUT.PUT_LINE(‘删除员工: ‘ || :OLD.first_name || ‘ ‘ || :OLD.last_name || ‘，时间: ‘ || v_delete_time);
— 这里可以添加将删除的员工信息插入到历史表的逻辑
END
;
/

执行：

SQL> — 创建表
SQL> CREATE TABLE
fgedu_employees (
2 employee_id NUMBER PRIMARY KEY,
3 first_name VARCHAR2(50),
4 last_name VARCHAR2(50),
5 salary NUMBER,
6 hire_date DATE,
7 last_update_date DATE,
8 last_updated_by VARCHAR2(50)
9 );

Table created.

SQL> — 创建触发器：在插入或更新时自动设置更新时间和更新人
SQL> CREATE OR REPLACE
TRIGGER
fgedu_employees_before_insert_update
2 BE FOR
E INSERT
OR UPDATE
ON fgedu_employees
3 FOR
EACH ROW
4 BEGIN
5 :NEW.last_update_date :=
SYSDATE;
6 :NEW.last_updated_by :=
USER;
7 END
;
8 /

Trigger created.

SQL> — 创建触发器：在删除时记录删除的员工信息
SQL> CREATE OR REPLACE
TRIGGER
fgedu_employees_before_delete
2 BE FOR
E DELETE
ON fgedu_employees
3 FOR
EACH ROW
4 DECLARE
5 v_delete_time DATE;
6 BEGIN
7 v_delete_time :=
SYSDATE;
8 DBMS_OUTPUT.PUT_LINE(‘删除员工: ‘ || :OLD.first_name || ‘ ‘ || :OLD.last_name || ‘，时间: ‘ || v_delete_time);
9 — 这里可以添加将删除的员工信息插入到历史表的逻辑
10 END
;
11 /

Trigger created.

测试触发器：

SQL> SET SERVEROUTPUT ON;
SQL> — 插入数据
SQL> INSERT
IN
TO fgedu_employees (employee_id, first_name, last_name, salary, hire_date)
2 VALUES (1, ‘John’, ‘Doe’, 8000, SYSDATE);

1 row created.

SQL> — 查看数据
SQL> SELECT
*
FROM fgedu_employees;

EMPLOYEE_ID FIRST_NAME LAS
T_NAME SALARY HIRE_DATE LAS
T_UPDATE
_DATE LAS
T_UPDATE
D_BY
———– ——————– ————————- ———- ——— ——————–
——————–
1 John Doe 8000 29-MAR-26 29-MAR-26 10:30:45 SYS

SQL> — 更新数据
SQL> UPDATE
fgedu_employees
SET salary = 9000
WHERE employee_id = 1;

1 row updated.

SQL> — 查看数据
SQL> SELECT
*
FROM fgedu_employees;

EMPLOYEE_ID FIRST_NAME LAS
T_NAME SALARY HIRE_DATE LAS
T_UPDATE
_DATE LAS
T_UPDATE
D_BY
———– ——————– ————————- ———- ——— ——————–
——————–
1 John Doe 9000 29-MAR-26 29-MAR-26 10:31:12 SYS

SQL> — 删除数据
SQL> DELETE
FROM fgedu_employees
WHERE employee_id = 1;
删除员工: John Doe，时间: 29-MAR-26

1 row deleted.

4.2 替代触发器的创建与使用

替代触发器用于视图，替代对视图的DML操作，下面是一个创建和使用替代触发器的例子：

— 创建基表
CREATE TABLE
fgedu_departments (
department_id NUMBER PRIMARY KEY,
department_name VARCHAR2(50)
);

CREATE TABLE
fgedu_employees2 (
employee_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
department_id NUMBER REFERENCES fgedu_departments(department_id)
);

— 插入测试数据
INSERT
IN
TO fgedu_departments VALUES (1, ‘IT’);
INSERT
IN
TO fgedu_departments VALUES (2, ‘HR’);
INSERT
IN
TO fgedu_employees2 VALUES (1, ‘John’, ‘Doe’, 1);
INSERT
IN
TO fgedu_employees2 VALUES (2, ‘Jane’, ‘Smith’, 2);

— 创建视图
CREATE VIEW fgedu_employee_details AS
SELECT
e.employee_id, e.first_name, e.last_name, d.department_name
FROM fgedu_employees2 e
JOIN
fgedu_departments d ON e.department_id = d.department_id;

— 创建替代触发器
CREATE OR REPLACE
TRIGGER
fgedu_employee_details_instead_of_insert
IN
STEAD OF INSERT
ON fgedu_employee_details
FOR
EACH ROW
DECLARE
v_department_id NUMBER;
BEGIN
— 查找部门ID
SELECT
department_id IN
TO v_department_id
FROM fgedu_departments
WHERE department_name = :NEW.department_name;

— 插入员工数据
INSERT
IN
TO fgedu_employees2 (employee_id, first_name, last_name, department_id)
VALUES (:NEW.employee_id, :NEW.first_name, :NEW.last_name, v_department_id);
END
;
/

执行：

SQL> — 创建基表
SQL> CREATE TABLE
fgedu_departments (
2 department_id NUMBER PRIMARY KEY,
3 department_name VARCHAR2(50)
4 );

Table created.

SQL> CREATE TABLE
fgedu_employees2 (
2 employee_id NUMBER PRIMARY KEY,
3 first_name VARCHAR2(50),
4 last_name VARCHAR2(50),
5 department_id NUMBER REFERENCES fgedu_departments(department_id)
6 );

Table created.

SQL> — 插入测试数据
SQL> INSERT
IN
TO fgedu_departments VALUES (1, ‘IT’);

1 row created.

SQL> INSERT
IN
TO fgedu_departments VALUES (2, ‘HR’);

1 row created.

SQL> INSERT
IN
TO fgedu_employees2 VALUES (1, ‘John’, ‘Doe’, 1);

1 row created.

SQL> INSERT
IN
TO fgedu_employees2 VALUES (2, ‘Jane’, ‘Smith’, 2);

1 row created.

SQL> — 创建视图
SQL> CREATE VIEW fgedu_employee_details AS
2 SELECT
e.employee_id, e.first_name, e.last_name, d.department_name
3 FROM fgedu_employees2 e
4 JOIN
fgedu_departments d ON e.department_id = d.department_id;

View created.

SQL> — 创建替代触发器
SQL> CREATE OR REPLACE
TRIGGER
fgedu_employee_details_instead_of_insert
2 IN
STEAD OF INSERT
ON fgedu_employee_details
3 FOR
EACH ROW
4 DECLARE
5 v_department_id NUMBER;
6 BEGIN
7 — 查找部门ID
8 SELECT
department_id IN
TO v_department_id
9 FROM fgedu_departments
10 WHERE department_name = :NEW.department_name;
11
12 — 插入员工数据
13 INSERT
IN
TO fgedu_employees2 (employee_id, first_name, last_name, department_id)
14 VALUES (:NEW.employee_id, :NEW.first_name, :NEW.last_name, v_department_id);
15 END
;
16 /

Trigger created.

测试替代触发器：

SQL> — 通过视图插入数据
SQL> INSERT
IN
TO fgedu_employee_details (employee_id, first_name, last_name, department_name)
2 VALUES (3, ‘Mike’, ‘Johnson’, ‘IT’);

1 row created.

SQL> — 查看视图数据
SQL> SELECT
*
FROM fgedu_employee_details;

EMPLOYEE_ID FIRST_NAME LAS
T_NAME DEPARTMENT_NAME
———– ——————– ————————- ——————–
1 John Doe IT
2 Jane Smith HR
3 Mike Johnson IT

SQL> — 查看基表数据
SQL> SELECT
*
FROM fgedu_employees2;

EMPLOYEE_ID FIRST_NAME LAS
T_NAME DEPARTMENT_ID
———– ——————– ————————- ————-
1 John Doe 1
2 Jane Smith 2
3 Mike Johnson 1

学习交流加群风哥微信: itpux-com

4.3 系统触发器的创建与使用

系统触发器用于响应系统事件，如登录、注销、启动、关闭等，下面是一个创建和使用系统触发器的例子：

— 创建日志表
CREATE TABLE
fgedu_login_logs (
log_id NUMBER PRIMARY KEY,
username VARCHAR2(50),
login_time DATE,
ip_address VARCHAR2(20)
);

— 创建序列
CREATE SEQUENCE fgedu_login_logs_seq START WITH 1 IN
CREMENT BY 1;

— 创建系统触发器：记录用户登录信息
CREATE OR REPLACE
TRIGGER
fgedu_logon_trigger
AFTER LOGON ON DATABAS
E
BEGIN
INSERT
IN
TO fgedu_login_logs (log_id, username, login_time, ip_address)
VALUES (fgedu_login_logs_seq.NEXTVAL, USER, SYSDATE, SYS_CONTEXT(‘USERENV’, ‘IP_ADDRESS’));
END
;
/

执行：

SQL> — 创建日志表
SQL> CREATE TABLE
fgedu_login_logs (
2 log_id NUMBER PRIMARY KEY,
3 username VARCHAR2(50),
4 login_time DATE,
5 ip_address VARCHAR2(20)
6 );

Table created.

SQL> — 创建序列
SQL> CREATE SEQUENCE fgedu_login_logs_seq START WITH 1 IN
CREMENT BY 1;

Sequence created.

SQL> — 创建系统触发器：记录用户登录信息
SQL> CREATE OR REPLACE
TRIGGER
fgedu_logon_trigger
2 AFTER LOGON ON DATABAS
E
3 BEGIN
4 INSERT
IN
TO fgedu_login_logs (log_id, username, login_time, ip_address)
5 VALUES (fgedu_login_logs_seq.NEXTVAL, USER, SYSDATE, SYS_CONTEXT(‘USERENV’, ‘IP_ADDRESS’));
6 END
;
7 /

Trigger created.

测试系统触发器：

SQL> — 查看登录日志
SQL> SELECT
*
FROM fgedu_login_logs;

LOG_ID USERNAME LOGIN
_TIME IP_ADDRESS
———- —————————— ——————– ——————–
1 SYS 29-MAR-26 10:45:30 127.0.0.1

4.4 触发器的异常处理

在PL/SQL触发器中，异常处理是非常重要的，下面是一个带有异常处理的触发器例子：

— 创建触发器：带有异常处理
CREATE OR REPLACE
TRIGGER
fgedu_employees_before_insert
BE FOR
E INSERT
ON fgedu_employees
FOR
EACH ROW
BEGIN
— 验证员工ID
IF
:NEW.employee_id <= 0 THEN RAISE_APPLICATION_ERROR(-20001, '员工ID必须大于0' );
END IF
;
— 验证薪水 IF
:NEW.salary
< 0 THEN RAISE_APPLICATION_ERROR(-20002, '薪水不能为负数' );
END IF
;
— 设置默认值 :NEW.last_update_date :=
SYSDATE;
:NEW.last_updated_by :=
USER;
EXCEPTION
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(‘错误：’ || SQLERRM);
RAISE;
END
;
/

执行：

SQL> — 创建触发器：带有异常处理
SQL> CREATE OR REPLACE
TRIGGER
fgedu_employees_before_insert
2 BE FOR
E INSERT
ON fgedu_employees
3 FOR
EACH ROW
4 BEGIN
5 — 验证员工ID
6 IF
:NEW.employee_id <= 0 THEN 7 RAISE_APPLICATION_ERROR(-20001, '员工ID必须大于0' );
8 END IF
;
9 10 —
验证薪水 11 IF
:NEW.salary < 0 THEN 12 RAISE_APPLICATION_ERROR(-20002, '薪水不能为负数' );
13 END IF
;
14 15
— 设置默认值 16 :NEW.last_update_date :=
SYSDATE;
17 :NEW.last_updated_by :=
USER;
18 EXCEPTION
19
WHEN OTHERS THEN 20 DBMS_OUTPUT.PUT_LINE(‘错误：’ || SQLERRM);
21 RAISE;
22 END
;
23 /
Trigger
created.

测试异常处理：

SQL> SET SERVEROUTPUT ON;
SQL> — 测试无效的员工ID
SQL> INSERT
IN
TO fgedu_employees (employee_id, first_name, last_name, salary, hire_date)
2 VALUES (0, ‘John’, ‘Doe’, 8000, SYSDATE);
错误：ORA-20001: 员工ID必须大于0
ORA-06512: 在 “SYS.FGEDU_EMPLOYEES_BE FOR
E_INSERT
“, line 7
ORA-04088: 触发器 ‘SYS.FGEDU_EMPLOYEES_BE FOR
E_INSERT
‘ 执行过程中出错

SQL> — 测试负薪水
SQL> INSERT
IN
TO fgedu_employees (employee_id, first_name, last_name, salary, hire_date)
2 VALUES (1, ‘John’, ‘Doe’, -1000, SYSDATE);
错误：ORA-20002: 薪水不能为负数
ORA-06512: 在 “SYS.FGEDU_EMPLOYEES_BE FOR
E_INSERT
“, line 12
ORA-04088: 触发器 ‘SYS.FGEDU_EMPLOYEES_BE FOR
E_INSERT
‘ 执行过程中出错

SQL> — 测试有效的数据
SQL> INSERT
IN
TO fgedu_employees (employee_id, first_name, last_name, salary, hire_date)
2 VALUES (1, ‘John’, ‘Doe’, 8000, SYSDATE);

1 row created.

学习交流加群风哥QQ113257174

Part05-风哥经验总结与分享

5.1 触发器使用最佳实践

  • 使用触发器来实现数据完整性和业务规则，如自动设置时间戳、记录操作日志等
  • 触发器的逻辑应该简单，避免复杂的业务逻辑
  • 触发器的名称应该清晰地表明其功能，遵循命名规范
  • 在触发器中添加适当的注释，说明其功能和使用方法
  • 定期监控和优化触发器的性能，确保触发器的执行效率

5.2 触发器性能优化技巧

  • 避免在触发器中执行大量的SQL语句，尽量减少数据库的访问次数
  • 使用绑定变量来提高SQL语句的执行效率
  • 考虑使用批量操作来减少触发器的执行次数
  • 使用适当的索引来提高查询性能
  • 避免在触发器中使用复杂的逻辑，保持触发器的简洁性

5.3 触发器安全性建议

  • 避免在触发器中使用动态SQL，防止SQL注入攻击
  • 对输入参数进行验证，确保参数的有效性
  • 使用适当的权限控制，限制对触发器的访问权限
  • 避免在触发器中处理敏感数据，保护数据的安全性
  • 定期审计触发器的使用情况，发现和解决潜在的安全问题
更多学习教程公众号风哥教程itpux_com

from oracle:www.itpux.com

风哥提示：建议：在设计PL/SQL触发器时，应遵循以下原则：

  • 触发器应该只做一件事，并且做好
  • 触发器的逻辑应该简单，避免复杂的业务逻辑
  • 触发器应该有明确的命名规范，便于识别和管理
  • 触发器应该有适当的注释，说明其功能和使用方法
  • 避免在触发器中执行大量的操作，影响性能

学习交流加群风哥QQ113257174

生产环境建议：请根据实际情况调整配置和参数，确保生产环境的安全性和稳定性。

风哥提示：更多学习教程公众号风哥教程itpux_com

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息