Oracle中使用系统触发器记录表结构的修改操作
在实际工作中,我们记录对某个表数据的操作,一般会使用触发器和日志表相配合来完成,那么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这个数据组里的其他数据也取出来,拼接在一起。
扫描二维码推送至手机访问。
版权声明:本文由卖水果的net发布,如需转载请注明出处。
本文链接:http://www.msgde.net/oracle/oracle_sys_trigger_on_ddl.html