Posted by 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 
Learning is not a spectator sport 
Connor,
Thank you for the nice explaination.
I guess someone has already asked this on AskTom and as far as I can remember, Tom’s own position on DBMS_JOB being desupported/deprecated/removed was, if DBMS_SCHEDULER does not add transactional capability then he will file a bug.
So do we know what is oracle’s stand on using DBMS_JOB, especially in situations like above where transaction integrity matters?
Yeah, I’d be devastated if dbms_job went away without something being added to scheduler to be able to do transactional job submission. I’ll ask around internally.
>>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.
Are you able to retry with an SQL Trace ?
This issue suddenly reminded me one of the past PL/SQL Challenge quizzes ( how else 🙂 )
dealing with the same topic of a DDL trigger, and which demonstrated that the effects of a
CREATE OR REPLACE PROCEDURE are NOT yet visible in the dictionary, if queried
from an AFTER CREATE OR ALTER trigger.
So, I was sure that this is a general behavior.
But, I performed some additional tests and, for example setting a column to UNUSED is visible immediately,
while MODIFY-ing a column is only visible after the operation.
The explanation probably relies on the order of the timepoint in which the dictionary changes are performed in the SYS.COL$ table for the various DDL operations, versus the timepoint when the DDL trigger is fired.
It looks indeed like a bug.
In my opinion, the AFTER trigger should always fire as the LAST STEP in the sequence of operations
that happen during the triggering DDL event, and at that point, all the effects of the DDL should already be visible.