Working with Events and Attributes

The best way to demonstrate the possibilities offered by DDL trigger events and attributes is with a series of examples.

Here is a trigger that prevents any and all database objects from being created:

CREATE OR REPLACE TRIGGER no_create AFTER CREATE ON SCHEMABEGIN RAISE_APPLICATION_ERROR (-20000, 'ERROR: Objects cannot be created in the production database.');END;

After installing this trigger, attempts at creating anything meet with failure:

SQL> CREATE TABLE demo (col1 NUMBER); *ERROR at line 1:ORA-20000: Objects cannot be created in the production database.

That is a rather terse and uninformative error message. There was a failure, but what failed? Wouldn't it be nice to have a little more information in the error message, such as what I was attempting to create?

/* File on web: no_create.sql */CREATE OR REPLACE TRIGGER no_createAFTER CREATE ON SCHEMABEGIN RAISE_APPLICATION_ERROR (-20000, 'Cannot create the ' || ORA_DICT_OBJ_TYPE || ' named ' || ORA_DICT_OBJ_NAME || ' as requested by ' || ORA_DICT_OBJ_OWNER || ' in production.');END;

With this trigger installed, an attempt to create my table now offers much more diagnostic information:

SQL> CREATE TABLE demo (col1 NUMBER); *ERROR at line 1:ORA-20000: Cannot create the TABLE named DEMO as requested by SCOTT in production

I could even place this logic within a BEFORE DDL trigger, and take advantage of the ORA_SYSEVENT attribute to respond to specific events:

CREATE OR REPLACE TRIGGER no_createBEFORE DDL ON SCHEMABEGIN IF ORA_SYSEVENT = 'CREATE' THEN RAISE_APPLICATION_ERROR (-20000, 'Cannot create the ' || ORA_DICT_OBJ_TYPE || ' named ' || ORA_DICT_OBJ_NAME || ' as requested by ' || ORA_DICT_OBJ_OWNER); ELSIF ORA_SYSEVENT = 'DROP' THEN -- Logic for DROP operations... END IF;END;

18.2.4.1 What column did I touch?

I can use the ORA_IS_ALTER_COLUMN function to decipher which column was altered by an ALTER TABLE statement. Here is one example:

/* File on web: preserve_app_cols.sql */CREATE OR REPLACE TRIGGER preserve_app_cols AFTER ALTER ON SCHEMADECLARE -- Cursor to get columns in a table CURSOR curs_get_columns (cp_owner VARCHAR2, cp_table VARCHAR2) IS SELECT column_name FROM all_tab_columns WHERE owner = cp_owner AND table_name = cp_table;BEGIN -- if it was a table that was altered... IF ora_dict_obj_type = 'TABLE' THEN -- for every column in the table... FOR v_column_rec IN curs_get_columns (ora_dict_obj_owner, ora_dict_obj_name) LOOP -- Is the current column one that was altered? IF ORA_IS_ALTER_COLUMN (v_column_rec.column_name) THEN -- Reject change to "core application" column IF mycheck.is_application_column (ora_dict_obj_owner, ora_dict_obj_name, v_column_rec.column_name) THEN RAISE_APPLICATION_ERROR ('FAIL', 'Cannot alter core application attributes'); END IF; -- table/column is core END IF; -- current column was altered END LOOP; -- every column in the table END IF; -- table was alteredEND;

Attempts to change core application attributes will now be stopped.

Remember that this logic will not work when the trigger is fired for the addition of new columns. That column information is not yet visible in the data dictionary when the DDL trigger fires.

I can check for attempts todrop specific columns as follows:

IF ORA_IS_DROP_COLUMN ('COL2') THEN do something!ELSE do something else!END IF;

The ORA_IS_DROP_COLUMN and ORA_IS_ALTER_COLUMN functions are blissfully unaware of the table to which the column is attached; they work on column name alone.