AFTER ALTER ON SCHEMA TRIGGER + all_tab_columns
bin wangJan 26 2012 — edited Jan 30 2012Hi everybody,
I'm trying to get the column name of any column added to the table "TEST", So I create a trigger to ensure that job.
My problem is that the view "all_tab_columns" doesn't contain the column "column_test " yet when the trigger audit_ddl_trg is executed.
Please Help me to resolve this problem
CREATE TABLE TEST(id varchar2(10));
-- add a column to the Table "TEST"
alter table TEST add column_test varchar2(10);
-- column_test not detected !!?
CREATE OR REPLACE TRIGGER audit_ddl_trg AFTER ALTER ON SCHEMA
DECLARE
col_name varchar2(30);
BEGIN
IF (ORA_DICT_OBJ_TYPE = 'TABLE' AND ora_dict_obj_owner = 'OP' AND ORA_DICT_OBJ_NAME = 'TEST') THEN
FOR col_name IN (SELECT column_name FROM all_tab_columns WHERE owner = 'OP') LOOP
DBMS_OUTPUT.PUT_LINE('Column : ' || col_name);
END LOOP;
END IF;
END;
Thanks In Advance ;)
Edited by: 909366 on Jan 26, 2012 1:22 AM
Edited by: 909366 on Jan 26, 2012 1:57 AM