ORA_DICT_OBJ_TYPE, ORA_DICT_OBJ_OWNER, ORA_DICT_OBJ_NAME : Schema Trigger « Trigger « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Trigger
3. Schema Trigger

SQL>
SQL> CREATE OR REPLACE TRIGGER what_column
  2  AFTER ALTER ON SCHEMA
  3  DECLARE
  4    -- cursor to get columns in a table
  5    CURSOR curs_get_columns ( cp_owner VARCHAR2,
  6                              cp_table VARCHAR2 ) IS
  7    SELECT column_name
  8      FROM all_tab_columns
  9     WHERE owner = cp_owner
 10       AND table_name = cp_table;
 11
 12  BEGIN
 13
 14    IF ORA_DICT_OBJ_TYPE = 'TABLE' THEN
 15
 16      FOR v_column_rec IN curs_get_columns(ORA_DICT_OBJ_OWNER,
 17                                           ORA_DICT_OBJ_NAME) LOOP
 18
 19        IF ORA_IS_ALTER_COLUMN(v_column_rec.column_name) THEN
 20
 21          DBMS_OUTPUT.PUT_LINE(ORA_DICT_OBJ_OWNER        || '.' ||
 22                               ORA_DICT_OBJ_NAME         || '.' ||
 23                               v_column_rec.column_name);
 24
 25        END IF;
 26
 27      END LOOP;
 28
 29    END IF;
 30
 31  END;
 32  /

Trigger created.