Section 19.2.  DDL Triggers

19.2. DDL Triggers Oracle allows you to define triggers that will fire when Data Definition Language (DDL) statements are executed. Simply put, DDL is any SQL statement used to create or modify a database object such as a table or an index. Here are some examples of DDL statements: CREATE TABLE ALTER INDEX DROP TRIGGER Each of these statements results in the creation, alteration, or removal of a database object. The syntax for creating these triggers is remarkably similar to that of DML triggers, except that the firing events differ, and they are not applied to individual tables. The INSTEAD OF CREATE TABLE trigger, described at the end of this section, allows the default behavior of a CREATE TABLE event to be manipulated and is a somewhat idiosyncratic DDL trigger. Not all of the aspects of syntax and usage described in the following subsections apply to this trigger type. 19.2.1. Creating a DDL Trigger To create (or replace) a DDL trigger, use the syntax shown here: 1 CREATE [OR REPLACE] TRIGGER trigger name 2 {BEFORE | AFTER } {DDL event} ON {DATABASE | SCHEMA} 3 [WHEN (...)] 4 DECLARE 5 Variable declarations 6 BEGIN 7 ... some code... 8 END; The following table summarizes what is happening in this code: Line(s) Description 1 Specifies that a trigger is to be created with the name supplied. Specifying OR REPLACE is optional. If the trigger exists, and REPLACE is not specified, then good old Oracle error 4081 will appear stating just that. 2 This line has a lot to say. It defines whether the trigger will fire before, after, or instead of the particular DDL event as well as whether it will fire for all operations within the database or just within the current schema. Note that the INSTEAD OF option is available only in Oracle9i Release 1 and higher. 3 An optional WHEN clause that allows you to specify logic to avoid unnecessary execution of the trigger. 4-7 These lines simply demonstrate the PL/SQL contents of the trigger. Here's an example of a somewhat uninformed town crier trigger that announces the creation of all objects: /* File on web: uninformed_town_crier.sql */ SQL> CREATE OR REPLACE TRIGGER town_crier 2 AFTER CREATE ON SCHEMA 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE('I believe you have created something!'); 5 END; 6 / Trigger created. SQL> SET SERVEROUTPUT ON SQL> CREATE TABLE a_table 2 (col1 NUMBER); Table created. SQL> CREATE INDEX an_index ON a_table(col1); Index created. SQL> CREATE FUNCTION a_function RETURN BOOLEAN AS 2 BEGIN 3 RETURN(TRUE); 4 END; 5 / Function created. SQL> /*-- flush the DBMS_OUTPUT buffer */ SQL> BEGIN NULL; END; 2 / I believe you have created something! I believe you have created something! I believe you have created something! PL/SQL procedure successfully completed. Text displayed using the DBMS_OUTPUT built-in package within DDL triggers will not display until you successfully execute a PL/SQL block, even if that block does nothing. Over time, this town crier would be ignored due to a lack of information, always proudly announcing that something had been created but never providing any details. Thankfully, there is a lot more information available to DDL triggers, allowing for a much more nuanced treatment, as shown in this version: /* File on web: informed_town_crier.sql */ SQL> CREATE OR REPLACE TRIGGER town_crier 2 AFTER CREATE ON SCHEMA 3 BEGIN 4 -- use event attributes to provide more info 5 DBMS_OUTPUT.PUT_LINE('I believe you have created a ' || 6 ORA_DICT_OBJ_TYPE || ' called ' || 7 ORA_DICT_OBJ_NAME); 8 END; 9 / Trigger created. SQL> SET SERVEROUTPUT ON SQL> CREATE TABLE a_table 2 (col1 NUMBER); Table created. SQL> CREATE INDEX an_index ON a_table(col1); Index created. SQL> CREATE FUNCTION a_function RETURN BOOLEAN AS 2 BEGIN 3 RETURN(TRUE); 4 END; 5 / Function created. SQL> /*-- flush the DBMS_OUTPUT buffer */ SQL> BEGIN NULL; END; / I believe you have created a TABLE called A_TABLE I believe you have created a INDEX called AN_INDEX I believe you have created a FUNCTION called A_FUNCTION PL/SQL procedure successfully completed. Much more attention will be paid now that the town crier is more forthcoming. The above examples touch upon two important aspects of DDL triggers: the specific events to which they can be applied and the event attributes available within the triggers. 19.2.2. Available Events Table 19-1 lists the DDL events for which triggers can be coded. Each event can have a BEFORE and an AFTER trigger. Table 19-1. Available DDL events DDL event Fires when... ALTER Any database object is altered using the SQL ALTER command ANALYZE Any database object is analyzed using the SQL ANALYZE command ASSOCIATE STATISTICS Statistics are associated with a database object AUDIT Auditing is turned on using the SQL AUDIT command COMMENT Comments are applied to a database object CREATE Any database object is created using the SQL CREATE command DDL Any of the events listed here occur DISASSOCIATE STATISTICS Statistics are disassociated from a database object DROP Any database object is dropped using the SQL DROP command GRANT Privileges are granted using the SQL GRANT command NOAUDIT Auditing is turned off using the SQL NOAUDIT command RENAME A database object is renamed using the SQL RENAME command REVOKE Privileges are revoked using the SQL REVOKE command TRUNCATE A table is truncated using the SQL TRUNCATE command As with DML triggers, these DDL triggers fire when the event to which they are attached occurs within the specified database or schema. There is no limit to the number of trigger types that can exist in the database or schema. 19.2.3. Available Attributes Oracle provides a set of functions (defined in the DBMS_STANDARD package) that provide information about what fired the DDL trigger and other information about the trigger state (e.g., the name of the table being dropped). Table 19-2 displays these trigger attribute functions. The following sections offer some examples of usage. Table 19-2. DDL trigger event and attribute functions Name Description ORA_CLIENT_IP_ADDRESS Returns the IP address of the client. ORA_DATABASE_NAME Returns the name of the database. ORA_DES_ENCRYPTED_PASSWORD Returns the DES-encrypted password of the current user. ORA_DICT_OBJ_NAME Returns the name of the database object affected by the firing DDL. ORA_DICT_OBJ_NAME_LIST Returns the count of objects affected. It also returns a complete list of objects affected in the NAME_LIST parameter, which is a collection of type DBMS_STANDARD.ORA_NAME_LIST_T. ORA_DICT_OBJ_OWNER Returns the owner of the database object affected by the firing DDL. ORA_DICT_OBJ_OWNER_LIST Returns the count of objects affected. It also returns a complete list of object owners affected in the NAME_LIST parameter, which is a collection of type DBMS_STANDARD.ORA_NAME_LIST_T. ORA_DICT_OBJ_TYPE Returns the type of database object affected by the firing DDL (e.g., TABLE or INDEX). ORA_GRANTEE Returns the count of grantees. The USER_LIST argument contains the full list of grantees, which is a collection of type DBMS_STANDARD.ORA_NAME_LIST_T. ORA_INSTANCE_NUM Returns the number of the database instance. ORA_IS_ALTER_COLUMN Returns TRUE if the specified COLUMN_NAME argument is being altered, or FALSE if not. ORA_IS_CREATING_NESTED_TABLE Returns TRUE if a nested table is being created, or FALSE if not. ORA_IS_DROP_COLUMN Returns TRUE if the specified COLUMN_NAME argument is indeed being dropped, or FALSE if not. ORA_LOGIN_USER Returns the name of the Oracle user for which the trigger fired. ORA_PARTITION_POS Returns the position in the SQL command where a partitioning clause could be correctly added. ORA_PRIVILEGE_LIST Returns the number of privileges being granted or revoked. The PRIVILEGE_LIST argument contains the full list of privileges affected, which is a collection of type DBMS_STANDARD.ORA_NAME_LIST_T. ORA_REVOKEE Returns the count of revokees. The USER_LIST argument contains the full list of revokees, which is a collection of type DBMS_STANDARD.ORA_NAME_LIST_T. ORA_SQL_TXT Returns the number of lines in the SQL statement firing the trigger. The SQL_TXT argument returns each line of the statement, which is an argument of type DBMS_STANDARD.ORA_NAME_LIST_T. ORA_SYSEVENT Returns the type of event that caused the DDL trigger to fire (e.g., CREATE, DROP, or ALTER). ORA_WITH_GRANT_OPTION Returns TRUE if privileges were granted with the GRANT option, or FALSE if not. Note the following about the event and attribute functions: The datatype ORA_NAME_LIST_T is defined in the DBMS_STANDARD package as: TYPE ora_name_list_t IS TABLE OF VARCHAR2(64); In other words, this is a nested table of strings, each of which can contain up to 64 characters. The DDL trigger event and attribute functions are also defined in the DBMS_STANDARD package. Oracle creates a standalone function (which adds the "ORA_" prefix to the function name) for each of the packaged functions by executing the $ORACLE_HOME/rdbms/dbmstrig.sql script during database creation. In some releases of Oracle, there are errors in this script that cause the standalone functions to not be visible or executable. If you feel that these elements have not been properly defined, you should ask your DBA to check the script for problems and make the necessary corrections. The USER_SOURCE data dictionary view does not get updated until after both BEFORE and AFTER DDL triggers are fired. In other words, you cannot use these functions to provide a "before and after" version control system built entirely within the database and based on database triggers. 19.2.4. 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 SCHEMA BEGIN 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 the object I was attempting to create? /* File on web: no_create.sql */ CREATE OR REPLACE TRIGGER no_create AFTER CREATE ON SCHEMA BEGIN 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_create BEFORE DDL ON SCHEMA BEGIN 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; 19.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 SCHEMA DECLARE -- 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 altered END; 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 to drop 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. 19.2.4.2 Lists returned by attribute functions Some of the attribute functions return two pieces of data: a list of items and a count of items. For example, the ORA_GRANTEE function returns a list and a count of users that were granted a privilege, and the ORA_PRIVILEGE_LIST function returns a list and a count of privileges granted. These two functions are perfect for use in AFTER GRANT triggers. The what_privs.sql file available on the book's web site offers an extended example of how to use both of these functions. Below is just a portion of the total code: /* File on web: what_privs.sql */ CREATE OR REPLACE TRIGGER what_privs AFTER GRANT ON SCHEMA DECLARE v_grant_type VARCHAR2 (30); v_num_grantees BINARY_INTEGER; v_grantee_list ora_name_list_t; v_num_privs BINARY_INTEGER; v_priv_list ora_name_list_t; BEGIN -- Retrieve information about grant type and then the lists. v_grant_type := ORA_DICT_OBJ_TYPE; v_num_grantees := ORA_GRANTEE (v_grantee_list); v_num_privs := ORA_PRIVILEGE_LIST (v_priv_list); IF v_grant_type = 'ROLE PRIVILEGE' THEN DBMS_OUTPUT.put_line ( 'The following roles/privileges were granted'); -- For each element in the list, display the privilege. FOR counter IN 1 .. v_num_privs LOOP DBMS_OUTPUT.put_line ('Privilege ' || v_priv_list (counter)); END LOOP; This trigger is great for detailing what privileges and objects are affected by grant operations, as shown below. In a more sophisticated implementation, you might consider storing this information in database tables so that you have a detailed history of changes that have occurred. SQL> GRANT DBA TO book WITH ADMIN OPTION; Grant succeeded. SQL> EXEC DBMS_OUTPUT.PUT_LINE('Flush buffer'); The following roles/privileges were granted Privilege UNLIMITED TABLESPACE Privilege DBA Grant Recipient BOOK Flush buffer SQL> GRANT SELECT ON x TO system WITH GRANT OPTION; Grant succeeded. SQL> EXEC DBMS_OUTPUT.PUT_LINE('Flush buffer'); The following object privileges were granted Privilege SELECT On X with grant option Grant Recipient SYSTEM Flush buffer 19.2.5. Dropping the Undroppable We have shown that one use for DDL triggers is preventing a particular type of DDL on a particular object or type of object. But what if we create a trigger that prevents DROP DDL and then attempt to drop the trigger itself? Will we be left with a trigger that is essentially undroppable? Fortunately, Oracle has thought of this scenario, as you can see here: SQL> CREATE OR REPLACE TRIGGER undroppable 2 BEFORE DROP ON SCHEMA 3 BEGIN 4 RAISE_APPLICATION_ERROR(-20000,'You cannot drop me! I am invincible!'); 5 END; SQL> DROP TABLE employee; * ERROR at line 1: ORA-20000: You cannot drop me! I am invincible! SQL> DROP TRIGGER undroppable; Trigger dropped. 19.2.6. The INSTEAD OF CREATE Trigger Oracle provides the INSTEAD OF CREATE trigger to allow you to automatically partition a table. To do so, the trigger must trap the SQL statement being executed, insert the partition clause into it, and then execute it using the ORA_SQL_TXT function. The following trigger demonstrates these steps. CREATE TRIGGER io_create INSTEAD OF CREATE ON DATABASE WHEN (ORA_DICT_OBJ_TYPE = 'TABLE') DECLARE v_sql VARCHAR2 (32767); -- sql to be built v_sql_t ora_name_list_t; -- table of sql BEGIN -- get the SQL statement being executed FOR counter IN 1 .. ora_sql_txt (v_sql_t) LOOP v_sql := v_sql || v_sql_t (counter); END LOOP;   -- Determine the partition clause and add it. -- We will call the my_partition function v_sql := SUBSTR (v_sql, 1, ora_partition_pos) || magic_partition_function || SUBSTR (v_sql, ora_partition_pos + 1); /* Prepend table name with login username. | Replace CRLFs with spaces. | Requires an explicit CREATE ANY TABLE privilege, | unless you switch to AUTHID CURRENT_USER. */ v_sql := REPLACE (UPPER (REPLACE (v_sql, CHR (10), ' ')) , 'CREATE TABLE ' , 'CREATE TABLE ' || ora_login_user || '.' );   -- now execute the SQL EXECUTE IMMEDIATE v_sql; END; Now tables will be partitioned automatically, as determined by the logic in the my_partition function. Oracle offers several partitioning options (e.g., range, hash) and logical partitioning choices (e.g., by primary key, by unique key). You must decide which of these you want to utilize in your partitioning function. If you do not include the WHEN clause shown above, you will find that attempts to create objects that are not tables will fail with this error: ORA-00604: error occurred at recursive SQL level 1 ORA-30511: invalid DDL operation in system triggers Further, if you try to create an INSTEAD OF trigger for any other DDL operation besides CREATE, you will receive this compilation error: ORA-30513: cannot create system triggers of INSTEAD OF type INSTEAD OF triggers for DML operations (insert, update, and delete) are addressed later in this chapter. These triggers share some syntax with the INSTEAD OF CREATE trigger for tables, but that is the extent of their similarity,