Skip to Main Content
  • Questions

  • Access updated column names in DDL alter trigger

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Tomasz.

Asked: February 23, 2017 - 2:56 pm UTC

Last updated: February 25, 2017 - 1:05 am UTC

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

Viewed 1000+ times

You Asked

I have a trigger

create or replace
TRIGGER audit_trigger_update
AFTER ALTER OR CREATE ON SCHEMA
WHEN (ORA_DICT_OBJ_TYPE = 'TABLE')
BEGIN
create_audit_trigger(ORA_DICT_OBJ_NAME);
END audit_trigger_update;

with calling procedure to recreate DML trigger (due to lack of dynamic access in PL/SQL to :new or :old in DML trigger)

create_audit_trigger procedure use dynamic sql to "create or replace" new DML trigger (iterate over column names to create sql which is executed immediately at the end of procedure).

to get column names in create_audit_trigger procedure I use

select COLUMN_NAME from user_tab_cols where table_name = v_table_name and hidden_column = 'NO'

the problem is that it not work as I expected for ALTER statement like ALTER TABLE SOURCE ADD some_column VARCHAR2(150)

select COLUMN_NAME from user_tab_cols where table_name = v_table_name and hidden_column = 'NO' always return list of columns without this newly added (for example some_column from line above)

what is interesting same trigger works fine for CREATE statement

and Connor said...

I've replicated your findings - definitely an interesting result

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)

SQL>
SQL> create or replace
  2  procedure create_audit_trigger(o varchar2) is
  3  begin
  4    for i in ( select column_name from dba_tab_columns
  5               where table_name = o
  6               and owner = 'SCOTT'
  7             )
  8    loop
  9      dbms_output.put_line(i.column_name);
 10    end loop;
 11  end;
 12  /

Procedure created.

SQL>
SQL>
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.

SQL> set serverout on
SQL> create table t1 ( x int, y int );
X
Y

Table created.

SQL>
SQL> alter table t add x int;
EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO

Table altered.

SQL>
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)


To workaround that, you can do the dynamic trigger work asynchronously, for example

SQL> create table col_debug ( ts timestamp, col_name varchar2(50));

Table created.

SQL>
SQL> create or replace
  2  procedure create_audit_trigger(o varchar2) is
  3    l_now timestamp := systimestamp;
  4  begin
  5    for i in ( select column_name from dba_tab_columns
  6               where table_name = o
  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>
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.

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.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Tomasz, February 24, 2017 - 7:54 am UTC

Thanks, looks like solution but not working out of box in my environment.

I got
Error(10,5): PLS-00201: identifier 'DBMS_JOB' must be declared
which as I understand mean that I have no access to DBMS_JOB

Is there any other workaround without altering security settings (this may be a formal challenge for me).
Connor McDonald
February 25, 2017 - 1:05 am UTC

By default, dbms_job can be executed by anyone

SQL> select grantee, privilege
  2   from dba_tab_privs
  3  where table_name = 'DBMS_JOB';

GRANTEE                        PRIVILEGE
------------------------------ -----------
PUBLIC                         EXECUTE


so someone has explicitly disabled that capacity on your system. I would be asking for a justification on why, because dbms_job does not bypass or increase your level of privileges on a system.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library