oracle 触发器

### Oracle 触发器：语句级触发器与行级触发器详解 #### 一、概念解析 在Oracle数据库管理中，触发器是一种特殊类型的存储过程，它可以在特定的数据库事件（如数据插入、更新或删除）发生时自动执行。触发器能够帮助维护数据库完整性，确保业务逻辑的一致性，并在必要时执行复杂的操作。 本文主要讨论的是在同一表上同时定义语句级触发器和行级触发器时，这两种触发器的触发顺序问题。 #### 二、触发器类型详解 ##### 1. 行级触发器 - **定义**：当一条SQL语句（如INSERT、UPDATE或DELETE）作用于表中的某一行时触发。 - **语法格式**： ```sql CREATE OR REPLACE TRIGGER <trigger_name> BEFORE|AFTER INSERT|UPDATE|DELETE ON <table_name> FOR EACH ROW BEGIN -- 触发器主体 END; ``` - **特点**：对于每一条受影响的记录都会被独立处理，因此可以针对每一行记录进行不同的处理逻辑。 ##### 2. 语句级触发器 - **定义**：当一条SQL语句作用于表中的多行数据时触发。 - **语法格式**： ```sql CREATE OR REPLACE TRIGGER <trigger_name> BEFORE|AFTER INSERT|UPDATE|DELETE ON <table_name> BEGIN -- 触发器主体 END; ``` - **特点**：无论SQL语句影响了多少行，触发器都只会被执行一次，因此适合处理那些与行无关的操作。 #### 三、案例分析 根据题目提供的SQL代码，我们来具体分析一下： 1. **创建测试表Foo**： ```sql CREATE TABLE foo(a NUMBER); ``` 2. **创建行级触发器Tri_ins**： ```sql CREATE OR REPLACE TRIGGER tri_ins BEFORE INSERT OR UPDATE OR DELETE ON emp FOR EACH ROW BEGIN INSERT INTO foo VALUES (1); END; ``` - **说明**：此触发器会在对`emp`表进行INSERT、UPDATE或DELETE操作时，为表`foo`中的每一行添加一个值1。 3. **创建语句级触发器Tri_insert**： ```sql CREATE OR REPLACE TRIGGER tri_insert BEFORE INSERT OR UPDATE OR DELETE ON emp BEGIN INSERT INTO foo VALUES (2); END; ``` - **说明**：此触发器同样会在对`emp`表进行INSERT、UPDATE或DELETE操作时触发，但只会在SQL语句执行前向表`foo`中添加一个值2，无论该SQL语句影响了多少行数据。 4. **测试案例**： - **插入测试**： ```sql INSERT INTO emp(empno, ename, job, deptno) VALUES (9110, 'LUCY', 'CLERK', 40); SELECT * FROM foo; ``` - **预期结果**：表`foo`中应有两条记录，一条值为1，另一条值为2。这是因为行级触发器会为新插入的行添加一个值1，而语句级触发器则会在插入之前添加一个值2。 - **更新测试**： ```sql UPDATE emp SET sal = 3500 WHERE empno = 9110; SELECT * FROM foo; ``` - **预期结果**：表`foo`中将新增两条记录，一条值为1，另一条值为2。这是因为更新操作同样会触发行级和语句级触发器。 - **删除测试**： ```sql DELETE FROM emp WHERE empno = 9110; SELECT * FROM foo; ``` - **预期结果**：表`foo`中将再次新增两条记录，一条值为1，另一条值为2。删除操作同样会触发行级和语句级触发器。 #### 四、触发器执行顺序 通过上述测试案例可以看出，在同一表上定义了行级触发器和语句级触发器的情况下，**行级触发器总是先于语句级触发器触发**。这是因为行级触发器关注的是每一条记录的变化，而语句级触发器关注的是整个SQL语句的行为。因此，在执行INSERT、UPDATE或DELETE操作时，首先会触发行级触发器，然后才触发语句级触发器。 理解不同类型的触发器及其触发机制对于数据库开发者来说非常重要，这有助于更有效地管理和控制数据库中的数据变化。