This question came in on AskTom, yielding a very interesting result when it comes to DDL triggers. To set the scene, I’ll first create a table called T which is just a copy of SCOTT.EMP
SQL> create table scott.t as select * from scott.emp;
Table created.
SQL> desc scott.t
Name Null? Type
----------------------------------------------------------------- -------- -----------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
Now let’s say our requirement is to capture anything that relates to columns for tables in the SCOTT schema. So if we create a table, then we want to see all of those columns, and also, if we alter an existing table, then we want to see what the table looks like as a result after the columns have been added or dropped. So let’s build a simple DDL trigger to do that. First, I’ll create a procedure which will list all of the columns for any table name that we pass it. We’ll work exclusively in SCOTT for this example.
SQL> create or replace
2 procedure create_audit_trigger(p_tab varchar2) is
3 begin
4 for i in ( select column_name from dba_tab_columns
5 where table_name = p_tab
6 and owner = 'SCOTT'
7 )
8 loop
9 dbms_output.put_line(i.column_name);
10 end loop;
11 end;
12 /
Procedure created.
And we’ll give it a quick test so make sure it works
SQL> set serverout on
SQL> exec create_audit_trigger('EMP')
EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO
PL/SQL procedure successfully completed.
We’ll now put that inside our DDL trigger any time we perform an ALTER or CREATE DDL operation on a table.
SQL> create or replace
2 TRIGGER audit_trigger_update
3 AFTER ALTER OR CREATE ON scott.SCHEMA
4 WHEN (ORA_DICT_OBJ_TYPE = 'TABLE')
5 BEGIN
6 create_audit_trigger(ORA_DICT_OBJ_NAME);
7 END;
8 /
Trigger created.
SQL> conn scott/tiger
Connected.
Now we’ll create a table and see if it works.
SQL> set serverout on
SQL> create table t1 ( x int, y int );
X
Y
Table created.
And we’ll drop a column to test that
SQL> alter table t1 drop column y;
X
So far so good. Now let us add a column to the table.
SQL> alter table t add x int;
EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO -- ???? no X
Table altered.
This is an unexpected result. At the time within the trigger we query the data dictionary to obtain a list of columns, it does not yet appear in the definition. Now that the statement has completed, if we perform a describe on the table, we can see that the new column X is indeed there.
SQL> desc t
Name Null? Type
----------------------------------------------------------------------- -------- --------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
X NUMBER(38)
The only workaround I can currently think of is to do work asynchronously, via DBMS_JOB. Rather than DBMS_OUTPUT, I’ll log the column names in a table so we can view them after the fact:
SQL> create table col_debug ( ts timestamp, col_name varchar2(50));
Table created.
SQL> create or replace
2 procedure create_audit_trigger(p_tab varchar2) is
3 l_now timestamp := systimestamp;
4 begin
5 for i in ( select column_name from dba_tab_columns
6 where table_name = p_tab
7 and owner = 'SCOTT'
8 )
9 loop
10 insert into col_debug values (l_now,i.column_name);
11 end loop;
12 commit;
13 end;
14 /
Procedure created.
SQL> create or replace
2 TRIGGER audit_trigger_update
3 AFTER ALTER OR CREATE ON scott.SCHEMA
4 WHEN (ORA_DICT_OBJ_TYPE = 'TABLE')
5 declare
6 j int;
7 BEGIN
8 dbms_job.submit(j,'create_audit_trigger('''||ORA_DICT_OBJ_NAME||''');');
9 END;
10 /
Trigger created.
Now I repeat the addition of a column, and the check out debugging table
SQL> alter table t add x1 int;
Table altered.
SQL> select * from col_debug;
TS COL_NAME
--------------------------------------------------------------------------- ---------------
24-FEB-17 03.02.36.553000 PM EMPNO
24-FEB-17 03.02.36.553000 PM ENAME
24-FEB-17 03.02.36.553000 PM JOB
24-FEB-17 03.02.36.553000 PM MGR
24-FEB-17 03.02.36.553000 PM HIREDATE
24-FEB-17 03.02.36.553000 PM SAL
24-FEB-17 03.02.36.553000 PM COMM
24-FEB-17 03.02.36.553000 PM DEPTNO
24-FEB-17 03.02.36.553000 PM X
24-FEB-17 03.02.36.553000 PM X1
10 rows selected.
So the trigger submits a job rather than does the work itself.
Why does an ADD column operation behave differently to DROP and CREATE? That remains a mystery 
