PLSQL入门与精通（第57章：自治事务结合触发器保留历史的操作记录）

上次介绍了“自治事务管理”的基本概念和例子：
简单回顾一下吧。

该功能可以从主事务中调用自主事务的子程序，该子程序内的处理的事务式独立进行的（COMMIT）。
即使主事务被取消（ROLLBACK），自治性事务处理独自提交，不会因为主程序的取消而被取消。

这个功能在需要保留所有记录，以备审计的时候很方便。

我们用自治事务和数据库触发器结合来举例说明。

一个测试用的表，在对该表进行UPDATE的时候，需要将“何时、谁、哪一列、做了怎样的操作”等详细情报，要记录到审计表里。

让我们开始吧：

首先，制作测试用表（TEST01表）和审计表（AUDIT01表）。
针对TEST01表追加如下限制：（A列＞10）

为了对审计表进行INSERT处理，我们事先要创建排序对象SEQ _AUDIT(Sequence)。

//
–创建测试表和约束
//
SQL> CREATE TABLE TEST01 ( A NUMBER, B VARCHAR2(10));

表创建成功。

SQL> ALTER TABLE TEST01
2 ADD CONSTRAINT CHECK_TEST01 CHECK ( A > 10);

表已修改。

//
–创建审计表
//
SQL> CREATE TABLE AUDIT01 ( ID NUMBER, MSG VARCHAR2(200));

表创建成功。

//
–创建排序对象顺序
//
SQL> CREATE SEQUENCE SEQ_AUDIT;

创建了顺序。

我们将立即对TEST01创建触发器。

为了简化，本次将针对TEST01表的UPDATE进行限定触发。

//
–触发器的创建
//
SQL> CREATE OR REPLACE TRIGGER TRIG01
2 BEFORE UPDATE
3 ON TEST01
4 FOR EACH ROW
5 /******************/
6 --触发器宣言部
7 /******************/
8 DECLARE
9 --审计信息
10 MSG_STRING VARCHAR2(200);
11 --自治事务管理程序的声明
12 PROCEDURE PROC_AUDIT_INSERT(P_MSG IN VARCHAR2)
13 IS
14 PRAGMA AUTONOMOUS_TRANSACTION;
15 BEGIN
16 INSERT INTO AUDIT01 VALUES (SEQ_AUDIT.NEXTVAL, P_MSG);
17 COMMIT;
18 END PROC_AUDIT_INSERT;

19 /******************/
20 --触发器执行部
21 /******************/
22 BEGIN
23 --审计记录的信息
24 MSG_STRING := TO_CHAR(SYSDATE,‘YY/MM/DD HH24:MI:SS’) ||
25’，用户’ || USER || ’将‘’ || ”A列的值进行更新‘ || ‘
26 :OLD.A || ’→‘ || :NEW.A || ’，B列的值进行更新‘ ||
27 :OLD.B || ‘→’ || :NEW.B ；

28 --自治事务的INSERT的呼出和执行
29 PROC_AUDIT_INSERT(MSG_STRING);
30 END TRIG_EMP;
31 /

触发器已创建

让我们详细解释一下吧：

首先，这个触发器是行触发器（第4行的FOR EACH ROW）。
表中的DML触发器有“全表触发器”和“行触发器”，但是行触发器与全表触发器不同，对于更新的一行一行会按顺序在该行上启动该触发器，所以可以参照该行的列值。

因为需要对每一行的操作保留记录，还需要保留操作行的列的值，所以必须是行触发器。

下一个要点是，这个触发器是BEFORE触发器（第二行的BEFORE）。

这也是有理由的。如果是AFTER的触发器的话，如果DML因违反制约（一致性制约等）而发生错误的话，触发器不会被启动，所以无法在审计表上保留审计记录。

一般来说，行触发器（Before，After）的启动和行的更新的顺序如下所示：
1.首先将光标移到更新对象的第1行。
2.在该行上启动BEFORE行触发。
3.接下来更新那一行。如果存在违反制约的情况，则会发生错误并结束。
（如果发生错误，之前的触发的所有处理也会被取消。但是自治性事务处理由自己记性管理，不会被取消）
4.如果上述更新没有错误，则AFTER的行触发器会在该行上启动。
5.AFTER的行触发器正常结束后，光标将移动到下一个更新目标行。

因此，当用户更新数据发生违反约束的错误时，AFTER的行触发器将不会被启动。

因此，如果使用自治事务的处理进行保留记录进行审计的话，要用BEFORE触发器，而不是AFTER处理器。

最后，最重要的一点是，将INSERT处理定义为自治性事务处理的子程序（第12至18行）。

注意：自治事务的定义必须是子程序。

即使是本地声明部分，也必须使用子程序。

让我们测试一下吧：

首先，先在测试表上插入两行INSERT数据。
注意：上述触发器在INSERT时不会被启动。只在UPDATE的时候才会启动。另外必须满足一下条件：A列的值必须大于10。

SQL> INSERT INTO TEST01(A,B) VALUES (11,‘ABC’);
创建了一行。

SQL>INSERT INTO TEST01(A,B) VALUES (12,‘DEF’);
创建了一行。

SQL> COMMIT;
提交完成。

SQL> SELECT * FROM TEST01;

A B

---

11 ABC
12 DEF

测试表上有两行数据。

那么我们更新一行（不违反制约（A>10）的规则）。
SQL> UPDATE TEST01 SET A = 21 WHERE A = 11;
一行已更新。

SQL> SELECT * FROM TEST01;
A B

---

21 ABC ←更新的行
12 DEF

确实，更新了一行。

根据上述的UPDATE触发器应该被启动，记录在审计表中。

SQL> SELECT MSG FROM AUDIT01 ORDER BY ID;
MSG

13/05/0815:30 用户SCOTT 将A列的值进行更新11→21、B列的值更进行更新ABC→ABC

如上，操作记录作为审计记录被记录下来了。

再回滚一下吧。

SQL> ROLLBACK;
回滚完成。

SQL> SELECT * FROM TEST01;
A B

---

11 ABC ←滚回到原来的数据
12 DEF

测试表中的数据被回滚，数据恢复原状。

但是，审计表的记录中已经通过自治事务吧操作记录的历史操作提交了，没有被取消。

SQL> SELECT MSG FROM AUDIT01 ORDER BY ID;
MSG

13/05/0815:30 用户SCOTT 将A列的值进行更新11→21、B列的值更进行更新ABC→ABC

正如您所看到的，审计记录没有被取消。

这样，即使取消交易，通过自治事务管理，审计数据也不会被取消。

那么，接下来就来试试违反制约（A>10）的UPDATE吧。
因为文章违反了制约，所以会出错，这时候审计也会被记录下来吗？
结论是肯定的：

SQL> UPDATE TEST01 SET A = 0 WHERE A = 12;
UPDATE TEST01 SET A = 0 WHERE A = 12
*
行1发生错误。:
ORA-022290：违反了检查限制（SCOTT.CHECK_TEST01）

向TEST01表的UPDATE发生了错误，TEST01表的数据没有变更。
SQL> SELECT * FROM TEST01;
A B

---

11 ABC
12 DEF

但是，审计的记录是有的：
SQL> SELECT MSG FROM AUDIT01 ORDER BY ID;
MSG

13/05/0815:30 用户SCOTT 将A列的值进行更新11→21、B列的值更进行更新ABC→ABC
13/05/0816:30 用户SCOTT 将A列的值进行更新12→0、B列的值更进行更新DEF→DEF

正如您所看到的那样，实际的更新是错误的，但是审计的记录被记录下来了。

如上所述，更新SQL文即使有错误，也要进行审计记录的保留。这时候必须是BEFORE的行触发器，而且是自治事务。

如果AFTER的触发器的话，或者BEFORE触发器不是自治事务的话，即使SQL语句出错，也无法留下审计记录。

那么，这次就到此为止。

下次在这个内容里有一件想补充的事情（虽然是非常细小的事情・・・），我想谈谈这件事。

敬请期待。

报错 笔记拼音 双语对照