PLSQL入门与精通（第39章，行触发器列的值）

上次我们提到了DML操作行触发器，它是在操作对象数据行上触发的，同时“可以参照该行的列值”。
具体来说：
：OLD.列名称←DML操作前的列值
：NEW.列名←DML操作后的列值
如果触发动作是UPDATE中化《：OLD.列名》是UPDATE前的值，《：NEW.列名》是UPDATE后的值。
如果触发动作是DELETE的话，『：OLD.列名』是在DELETE发生之前的该行的该列的值，《：NEW.列名》是DELETE后该行该列的值。可是，如果DELETE的话就不存在该行了，值是啥呢？告诉大家，“：NEW.列名”的值是NULL。
同样，触发动作是INSERT的话，《：OLD.列名》是INSERT前的行的列值，也就是NULL，『：NEW.列名』是INSERT后的行的列值。
举例说明：
针对一个用户表进行操作，针对该表的操作内容进行记录。
操作表：谁、何时、进行了怎样进行DML操作。
针对用户表做操作会触发一个触发器，在记录表里边记录针对用户表的操作。

首先，SOCTT用户制作由TEST01这两列（ID列、NAME列）构成的表格。
SQL> SHOW USER
用户是“SCOTT”。
SQL> CREATE TABLE TEST01
2 (ID NUMBER,
3 NAME VARCHAR2(10));
表格创建成功。

对于这个TEST01表的操作，在AUDIT_TEST01表里边记录谁、什么时候、做了怎样的DML操作的。

SQL> CREATE TABLE AUDIT_TEST01
2 ( USERNAME VARCHAR2(10),
3 OP_TIME VARCHAR2(30),
4 OLD_ID NUMBER,
5 NEW_ID NUMBER,
6 OLD_NAME VARCHAR2(10),
7 NEW_NAME VARCHAR2(10))
8 /

表格创建成功。

该表中有谁（USERNAME）、何时（OP_TIME）操作过的、记录该行各列的OLD和NEW值的列（OLD_ID、NEW_ID、OLD_NAME、NEW_NAME）。
那么，实际在TEST01表中实施DML操作时操作的用户、时间戳、对象行的“：OLD.列值”和“：NEW列值”（INSERT），要保存到
AUDIT_TEST01表中，保存动作要针对TEST01表做一个行触发器。

以下是触发器代码：
SQL> CREATE OR REPLACE TRIGGER AUDIT_TEST01_TRIG
2 AFTER
3 INSERT OR UPDATE OR DELETE ON TEST01
4 FOR EACH ROW
5 BEGIN
6 INSERT INTO AUDIT_TEST01
7 VALUES(USER, TO_CHAR(SYSDATE,‘YY-MM-DD HH24:MI:SS’),
8 :OLD.ID, :NEW.ID, :OLD.NAME, :NEW.NAME);
9 END;
10 /

触发器已创建。

●第2行……触发时机是AFTER，这里也可以指定为BEFORE。
●第3行……指定INSERT、UPDATE、DELETE作为针对TEST01触发动作。
●第4行……行触发：FOR EACH ROW。
●第6-8行……将TEST01表的对象行的OLD和NEW的列值INSERT到AUDIT TEST01表中。
●第7行：USER函数是该操作的数据库用户的名字，把这个名字插入到记录表中。
为了便于查询时间戳，通过TO_CHAR函数对SYSDATE函数进行格式转换。
由此，如果对TEST01表进行DML操作（INSERT、UPDATE、DELETE），则AUDIT TEST01表中会留下详细的记录。
我们来试试吧。
首先，SCOTT用户对TEST01表进行两行INSERT。
SQL> SHOW USER
用户是“SCOTT”。
SQL> INSERT INTO TEST01 VALUES (10,‘ABC’);
创建了一行。
SQL> INSERT INTO TEST01 VALUES (20,‘XYZ’);
创建了一行。

接着是SYSTEM用户，将TEST01表的10号的NAME列“abc”进行UPDATE。
SQL> CONNECT SYSTEM/oracle
已连接。
SQL> UPDATE SCOTT.TEST01 SET NAME = ‘abc’ WHERE ID = 10;
一行已更新。
另外，SYSTEM用户将TEST01表20号的ID列的值设为21。
SQL> UPDATE SCOTT.TEST01 SET ID = 21 WHERE ID = 20;
一行已更新。

另外，SYSTEM用户将DELETE TEST01表的10号的数据行。
SQL> DELETE FROM SCOTT.TEST01 WHERE ID = 10;
删除了一行。

作为参考，现在查看一下TEST01表的数据。
SQL> SELECT * FROM SCOTT.TEST01;
ID NAME
‘--------------------
21 XYZ
如上所述，针对TEST01表，SOCTT用户进行了2行插入、SYSTEM用户进行了2行UPDAATE、1行DELETE，
但每次操作都会启动行触发器，记录这些操作，结果在AUDIT_TEST01表中总中记录。
SQL> SELECT * FROM SCOTT.AUDIT_TEST01 ORDER BY OP_TIME;
USERNAME OP_TIME OLD_ID NEW_ID OLD_NAME NEW_NAME

SCOTT 12-10-25 18:23:26 10 ABC
SCOTT 12-10-25 18:23:41 20 XYZ
SYSTEM 12-10-25 18:24:31 10 10 ABC abc
SYSTEM 12-10-25 18:25:03 20 21 XYZ XYZ
SYSTEM 12-10-25 18:25:24 10 abc

看了这个结果，就可以知道是谁（USERNAME）、什么时候（OP_TIME）、做了那个DML操作了。
操作的详细内容也可以通过OLD_ID、NEW_ID、OLD_NAME、NEW_NAME得值可以推测做了那个DML操作。
也就是说，第一行NEW的列有值，因为OLD的列没有值，所以可以知道是INSERT的记录，
INSERT的值是NEW_ID列和NEW_NAME列。
第3行和第4行在OLD和NEW两个方面都有价值，所以可以知道这两行是UPATE的操作

另外，最后一行NEW的两列都没有值，可以看出是DELETE的记录。
也就是说，可以知道是这队ID列是10，NAME列是“abc”行进行了DELETE的操作。

这样就会指导针对用户表做了哪些操作，都可以记录下来。
但是，这个触发器对DML操作的时候，性能会有很大影响，所以要慎重。

本次到此结束。