在实际工作中，我们记录对某个表数据的操作，一般会使用触发器和日志表相配合来完成，那么DML的操作可以使用触发器，DDL是否也可以呢，答案是肯定的。

1、问题引入

我们先看一个针对 DML操作的简单操作片断，BEFORE INSERT是表明在INSERT操作时这个触发器的文本才会执行，ON TEST 表明是应用于TEST表。

create or replace trigger tri_test_insert
  before insert on test
  for each row
declare

2、实现过程

ORACLE除了针对于DML的触发器以外，还有专门针对DDL操作的触发器，比如对于CREATE、DROP、ALTER 的操作，下面的示例将是一个记录对所有对象（表、视图、过程、函数等等）的增加、删除、修改的过程。
首先创建一个日志表，和填充日志表的一个序列，表结构的说明如下：username用于记录是当前的用户名，event用于记录操作的类型，是CREATE还是DROP，object_name记录一下被操作的对象名称，ddl_sql用于记录当时执行的实际SQL，created 用于记录操作日期和时间。

SQL> create sequence seq_ddl_logger;
Sequence created
SQL> create table ddl_logger(
  2      id int,
  3      username varchar(30),
  4      event varchar(30),
  5      object_name varchar(30),
  6      ddl_sql varchar(4000),
  7      crdate date default sysdate
  8  );
Table created

下面将是触发器的文本，before ddl，表明记录所有的DDL操作，on schema表明只在当前用户下的操作才能生效，ora_login_user是登录的用户名，ora_sysevent是具体的操作，ora_dict_obj_name是对象名称，ora_name_list_t中存储的是具体的SQL，这4个属性，都是系统提供的，我们可以直接使用。

create or replace trigger tri_schema_ddl
  before ddl on schema
declare
  l_text   varchar(300);
  sql_text ora_name_list_t;
  i        number;
begin
  i      := sql_txt(sql_text);
  l_text := sql_text(1);

  insert into ddl_logger
    (id, username, event, object_name, ddl_sql)
  values
    (seq_ddl_logger.nextval,
     ora_login_user,
     ora_sysevent,
     ora_dict_obj_name,
     l_text);
end;

到此为止，所有的准备工作都已完成，下在可以做一个简单的测试，新建表、增加字段、增加索引、增加主键约束、删除表等几个操作。

SQL>
SQL> create table test(id int, name varchar(10));
Table created
SQL> alter table test add remark varchar(10);
Table altered
SQL> create index ix_test_name on test(name);
Index created
SQL> alter table test add primary key (id);
Table altered
SQL> drop table test purge;
Table dropped
SQL>

3、查看结果

此时，我们可以查看一下日志的记录情况，基本上可以从这个结果中，提取出最开始需求的内容。

SQL> select id, event, object_name, ddl_sql from ddl_logger;
I EVENT      OBJECT_NAME    DDL_SQL
- ---------- -------------- ---------------------------------------------------------
1 CREATE     TEST           create table test(id int, name varchar(10))
2 ALTER      TEST           alter table test add remark varchar(10)
3 CREATE     IX_TEST_NAME   create index ix_test_name on test(name)
4 ALTER      TEST           alter table test add primary key (id)
5 CREATE     SYS_C0015235   CREATE UNIQUE INDEX "RDM"."SYS_C0015235" on "RDM"."TEST"("ID")
6 DROP       TEST           drop table test purge
6 rows selected

SQL>

4、注意事项

不能记录其他的用户登录以后（比如 sys或system），对本账户表结构的调整，如想要这些内容，可以把 on schema 修改为 on database或 on instance，当然这需要更大的权限。
在本例中，记录的事件范围是 ON DDL，如果不想记录太多的记录，可以调整为 ON ALTER，则不会记录 CREATE 和 DROP 这些操作。
当该触发器牌无效状态时，所有的 DDL 操作都会报错，所在结束记录这些操作之前，应该先删除该触发器，再删除 logger 表，以防止对其他的会话产生影响。
如果在本例中的记录的 SQL 不完整，可以把sql_text这个数据组里的其他数据也取出来，拼接在一起。

打赏 支付宝打赏 微信打赏