DDL触发器

来源：互联网发布：js设置css属性编辑：程序博客网时间：2024/05/22 17:32

1. Create the follow table to record the trace:

CREATE TABLE SYS.DDL$TRACE(  LOGIN_USER     VARCHAR2(50 BYTE),  DDL_TIME       DATE,  IP_ADDRESS     VARCHAR2(50 BYTE),  AUDSID         VARCHAR2(50 BYTE),  SCHEMA_USER    VARCHAR2(20 BYTE),  SCHEMA_OBJECT  VARCHAR2(50 BYTE),  DDL_SQL        VARCHAR2(4000 BYTE));

2. Create the trigger:

CREATE OR REPLACE TRIGGER SYS.TR_TRACE_DDLAFTER DDLON DATABASEDECLARE   sql_text    ora_name_list_t;   state_sql   ddl$trace.ddl_sql%TYPE;BEGIN   FOR i IN 1 .. ora_sql_txt (sql_text)   LOOP      state_sql := state_sql || sql_text (i);   END LOOP;   INSERT INTO ddl$trace (login_user,                          ddl_time,                          ip_address,                          audsid,                          schema_user,                          schema_object,                          ddl_sql)        VALUES (ora_login_user,                SYSDATE,                SYS_CONTEXT ('USERENV', 'IP_ADDRESS'),                USERENV ('SESSIONID'),                                Ora_Dict_Obj_Owner,                Ora_Dict_Obj_Name,                State_Sql);END tr_trace_ddl;/