Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Check for Column Addition in a DDL Trigger

717039Aug 12 2009 — edited Aug 13 2009
I'm writing a trigger in Oracle 10g that looks like the following code. It can only recognize when a column is being dropped from the table, but I would like the trigger to also recognize when the table is having a column added. Can anyone help me with this?
CREATE OR REPLACE TRIGGER <my trigger>
BEFORE DDL ON <my schema>
BEGIN
  IF ora_dict_obj_type = 'TABLE' AND ora_sysevent = 'ALTER' THEN
    FOR r_TableColumns IN (
      SELECT column_name
        FROM all_tab_columns
       WHERE table_name = ora_dict_obj_name
    ) LOOP
      -- If the column is being dropped, do some processing.
      IF ora_is_drop_column(r_TableColumns.column_name) THEN
        NULL; -- Do some processing...

        EXIT LOOP; -- Because the processing only needs to happen once, exit the loop.
      END IF;
    END LOOP;
  END IF;
END;
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 10 2009
Added on Aug 12 2009
5 comments
1,409 views