1) Available Events/Attributes in Triggers
1@@@@ All the DDL 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 comm 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. 2@@@@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. Like userenv() function return information about client. Table 19-2. DDL trigger event and attribute functions Name Returns... ORA_CLIENT_IP_ADDRESS IP address of the client. ORA_DATABASE_NAME Name of the database. ORA_DES_ENCRYPTED_ PASSWORD DES-encrypted password of the current user. ORA_DICT_OBJ_NAME Name of the database object affected by the firing DDL. ORA_DICT_OBJ_NAME_LIST 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 Owner of the database object affected by the firing DDL. ORA_DICT_OBJ_OWNER_LIST 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 Type of database object affected by the firing DDL (e.g., TABLE or INDEX). ORA_GRANTEE 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 Number of the database instance. ORA_IS_ALTER_COLUMN TRUE if the specified COLUMN_NAME argument is being altered, or FALSE if not. ORA_IS_CREATING_NESTED_ TABLE TRUE if a nested table is being created, or FALSE if not. ORA_IS_DROP_COLUMN TRUE if the specified COLUMN_NAME argument is indeed being dropped, or FALSE if not. ORA_LOGIN_USER Name of the Oracle user for which the trigger fired. ORA_PARTITION_POS Position in the SQL command where a partitioning clause could be correctly added. ORA_PRIVILEGE_LIST 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 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 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 Type of event that caused the DDL trigger to fire (e.g., CREATE, DROP, or ALTER). ORA_WITH_GRANT_OPTION 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 the Oracle database, 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. 3@@@@Working with Events and Attributes Here is a trigger that prevents any and all database objects from being created: 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 */ 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: @@@Example: HR@ocm> !cat tmp.sql 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 DBMS_OUTPUT.put_line('You droped the table ' || ORA_DICT_OBJ_NAME || '. I knew it.'); END IF; END; / HR@ocm> @tmp.sql HR@ocm> CREATE TABLE test_ora_sysevent(a number); CREATE TABLE test_ora_sysevent(a number) * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-20000: Cannot create the TABLE named TEST_ORA_SYSEVENT as requested by HR ORA-06512: at line 4 HR@ocm> DROP TRIGGER audit_test; You droped the table AUDIT_TEST. I knew it. I can use the ORA_IS_ALTER_COLUMN function to decipher which column was altered by an ALTER TABLE statement. Here is one example: @@@Example: you could control the logic using ORA_DICT_OBJ_XXX. here I apply it to ALTER TABLE DDL language. HR@ocm> !cat tmp.sql CREATE OR REPLACE TRIGGER preserve_app_cols AFTER ALTER ON SCHEMA DECLARE CURSOR curs_get_col ( owner_in IN VARCHAR2 , tname_in IN VARCHAR2 ) IS SELECT column_name FROM all_tab_columns WHERE owner= owner_in AND table_name = tname_in; --private function to judge whether it is key table column --cannot alter defination of hr.employees.salary FUNCTION is_application_column ( owner_in IN VARCHAR2 , tname IN VARCHAR2 , col_name IN VARCHAR2 ) RETURN BOOLEAN IS BEGIN IF owner_in = 'HR' THEN IF tname = 'EMPLOYEES' THEN IF col_name = 'SALARY' THEN RETURN TRUE; ELSE RETURN FALSE; END IF; END IF; END IF; END is_application_column; BEGIN IF ORA_DICT_OBJ_TYPE = 'TABLE' THEN FOR rec IN curs_get_col( ORA_DICT_OBJ_OWNER , ORA_DICT_OBJ_NAME ) LOOP IF ora_is_alter_column ( rec.column_name ) THEN IF is_application_column ( ORA_DICT_OBJ_OWNER , ORA_DICT_OBJ_NAME , rec.column_name ) THEN RAISE_APPLICATION_ERROR( -20000, '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; / HR@ocm> alter table employees modify (salary NUMBER(10,2)); alter table employees modify (salary NUMBER(10,2)) * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-20000: Cannot alter core application attributes ORA-06512: at line 39 referencing "Oracle PL/SQL Program Design 5th Edition"