Database Triggers and event attributes

Introduction

Creation of Database Triggers
A database trigger is created and dropped with the following commands:
    CREATE OR REPLACE TRIGGER trigger_name (BEFORE|AFTER)
                 database_trigger_event ON (DATABASE|schema.SCHEMA)…;
    DROP TRIGGER trigger_name;

When a database trigger is created, the trigger is checked for syntax, the dependency tree and privileges are checked,  and then the trigger is compiled into pcode and stored in the database. Therefore, triggers are similar to stored packages and procedures in the sense of creation, storage, and execution of pcode. The main differences are that database triggers source code is stored in a different data dictionary table and database triggers execute implicitly based on actions, whereas, packages and procedures are explicitly called. If errors occur during the creation or compilation of a database trigger, then the trigger is still created and enabled. If a database event executes that causes the database trigger to execute, the database event will fail. Therefore, if an error occurs during creation or compilation, the trigger needs to be either dropped, fixed and re-created, or disabled to ensure that processing does not stop. To view errors, the SHOW ERRORS command can be executed or the errors can be retrieved from the USER_ERRORS data dictionary view.
 
Security of Database Triggers
In order to create a database trigger, the schema must have one of 3 Oracle system privileges: Once a trigger is created, it is executed implicitly. Internally, Oracle fires the trigger in the existing user transaction.
Triggers are the same as stored packages and procedures and therefore, have dependencies that can cause a trigger to become invalidated. Any time a referenced stored package or procedure is modified, the trigger becomes invalidated. If a trigger ever becomes invalidated, then Oracle will attempt to internally re-compile the trigger the next time it is referenced. As a standard, a trigger that becomes invalidated, should be recompiled manually to ensure that the trigger will compile successfully. To compile a trigger manually, the ALTER TRIGGER command is used. This is shown below:
    ALTER TRIGGER logon_trigger COMPILE;
 
To recompile a trigger, you must either own the trigger or have the ALTER ANY TRIGGER system privilege. 

Enabling and Disabling Database Triggers

Disabled database triggers are companions to invalid objects. In some respects, a disabled trigger is far more dangerous than an invalid object because it doesn’t fail; it just doesn’t execute! This can have severe consequences for applications (and, consequently, for business processes) that depend on business logic stored within procedural code in database triggers. For this reason, you MUST run the following script regularly to ensure there are not any disabled triggers that you are not aware of:
 
SELECT trigger_name, trigger_type, base_object_type, triggering_event
FROM   user_triggers
WHERE  status <> 'ENABLED'
AND    db_object_type IN ('DATABASE        ', 'SCHEMA')
ORDER BY trigger_name;
 
TRIGGER_NAME        TRIGGER_TYPE  BASE_OBJECT_TYPE TRIGGERING_EVEN
------------------- ------------- ---------------- ---------------
DB_STARTUP_TRIGGER  AFTER EVENT   DATABASE         STARTUP        
 
Once the triggers are identified, they can be enabled manually or a dynamic SQL or PL/SQL script can be created to build the SQL statements to ENABLE the triggers. To enable database triggers, the following three commands could be executed.
 ALTER TRIGGER db_startup_trigger ENABLE;      -- enabling a database trigger
 ALTER TRIGGER before_insert_customer ENABLE;  -- enabling a table trigger
 ALTER TABLE s_customer ENABLE ALL TRIGGERS;   -- enabling all triggers on a table
 
The preceding commands allow you to enable one trigger at a time or all the triggers on a table. To enable all triggers under a schema, the following script can be used to build an ENABLE script dynamically:

SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SELECT   'ALTER TRIGGER ' || trigger_name || ' ENABLE;'
FROM     user_triggers
ORDER BY table_name;

 
ALTER TRIGGER DB_STARTUP_TRIGGER ENABLE;
ALTER TRIGGER BEFORE_INSERT_CUSTOMER ENABLE;
ALTER TRIGGER BEFORE_UPDATE_CUSTOMER ENABLE;

  

New Database Triggers
The 20 new triggers are broken into two main categories by Oracle, namely, database system events and DDL/client events. For each event, there are event attributes set internally by Oracle when the event takes place. These event attributes can be referenced in the database trigger logic. For example, the CREATE database trigger can reference the schema name, the type of object created, the name of the object, etc. for the object just created.

Database System Events

There are six database system event triggers. The six database system event triggers are outlined below, along with a description and the event attributes that are set for each event.
Database Trigger BEFORE/AFTER Execution  
Description
 
Attribute Event
LOGOFF BEFORE Executed when a user logs off, at the start of the logoff process ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
LOGON AFTER Executed when a user logs into the database, after a successful login of the user ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_client_ip_address
STARTUP AFTER Executed when the database is opened; starts a separate transaction and commits after this trigger is complete ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
 
SHUTDOWN BEFORE Executed when the instance is shutdown; prior to the shutdown of the instance process; not always executed on abnormal shutdown; starts a separate transaction and commits after this trigger is complete ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
 
SERVERERROR AFTER Executes when an Oracle error occurs (can check for a specific error number to only execute for (errno=eno)); does not execute for certain errors (1034, 1403, 1422, 1423, 4030); starts a separate transaction and commits after this trigger is complete ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_server_error
ora_is_servererror
space_error_info
SUSPEND AFTER Executed whenever a server error causes a transaction to be suspended (example: out-of-space error) ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_server_error
ora_is_servererror
space_error_info
 
The startup and shutdown triggers can only be created at the database level. The other four database system events can be created at the database or schema levels. The STARTUP trigger returns a success, even if the trigger fails.
The SERVERERROR trigger does not execute when the following Oracle errors are returned:
·               ORA-01403: data not found
·               ORA-01422: exact fetch returns more than requested number of rows
·               ORA-01423: error encountered while checking for extra rows in exact fetch
·               ORA-01034: ORACLE not available
·               ORA-04030: out of process memory
For these triggers, Oracle opens an autonomous transaction scope, fires the trigger, and commits any separate transaction.

DDL/Client Events

There are 14 DDL/client event triggers and these can be created at the database level and will execute for all schemas, or these can be created at the schema level and will execute only for the schema it is created for. When a trigger is created at the schema level, the trigger is created in the schema specified and executes only for that schema.
This provides a great deal of flexibility depending on your environment and what you want to monitor or respond to. The 14 DDL/client event triggers are outlined below, along with a description and the event attributes that are set for each event.
Database Trigger BEFORE/AFTER Execution  
Description
Attribute Events
 
 
 
 
 
ALTER
 
 
 
 
 
BEFORE/AFTER
 
 
 
 
 
Executed when object altered
ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_type
ora_dict_obj_name
ora_dict_obj_owner
ora_des_encrypted_password
(for ALTER USER events)
ora_is_alter_column,
ora_is_drop_column (for ALTER TABLE events)
 
 
 
DROP
 
 
 
BEFORE/AFTER
 
 
 
Executed when object is dropped
ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_type
ora_dict_obj_name
ora_dict_obj_owner
 
 
 
ANALYZE
 
 
 
BEFORE/AFTER
 
 
 
Executed when the analyze command is executed
ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_type
ora_dict_obj_owner
 
 
 
ASSOCIATE STATISTICS
 
 
 
BEFORE/AFTER
 
 
 
Executed when the associate statistics command is executed
ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_type
ora_dict_obj_owner
ora_dict_obj_name_list
ora_dict_obj_owner_list
 
AUDIT/NOAUDIT
 
BEFORE/AFTER
 
Executed when the audit or noaudit command is executed
ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
 
 
COMMENT
 
 
BEFORE/AFTER
 
 
Executed when the comment command is executed
ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_type
ora_dict_obj_owner
 
 
 
 
CREATE
 
 
 
 
BEFORE/AFTER
 
 
 
 
Executed when an object is created
ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_type
ora_dict_obj_name
ora_dict_obj_owner
ora_is_creating_nested_table
(for CREATE TABLE events)
DDL BEFORE/AFTER Executed when SQL DDL commands are executed (not executed when and ALTER/CREATE DATABASE, CREATE CONTROLFILE, or DDL issued through the PL/SQL procedure interface) ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_type
ora_dict_obj_owner
 
 
 
DISASSOCIATE STATISTICS
 
 
 
BEFORE/AFTER
 
 
 
Executed when the disassociate statistics command is executed
ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_type
ora_dict_obj_owner
ora_dict_obj_name_list
ora_dict_obj_owner_list
 
 
 
 
GRANT
 
 
 
 
BEFORE/AFTER
 
 
 
 
Executed when the grant command is executed
ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_type
ora_dict_obj_owner
ora_grantee
ora_with_grant_option
ora_privileges
 
 
RENAME
 
 
BEFORE/AFTER
 
 
Executed when the rename command is executed
ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_owner
ora_dict_obj_type
 
 
 
 
REVOKE
 
 
 
 
BEFORE/AFTER
 
 
 
 
Executed when the revoke command is executed
ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_type
ora_dict_obj_owner
ora_revokee
ora_privileges
 
 
 
TRUNCATE
 
 
 
BEFORE/AFTER
 
 
 
Execute when a table is truncated
ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_type
ora_dict_obj_owner
 
The new triggers are ideal for DBAs to build mechanisms based on certain events. When the database is started, the objects that need to be pinned can now be moved from the startup SQL script to the STARTUP trigger. When the database is shut down, statistics scripts can be executed to log information into monitoring tables with the SHUTDOWN trigger. Error trapping can be enhanced with the SERVERERROR trigger. Capturing user connect time can be handled through the LOGON and LOGOFF triggers. An object audit trail can be created through the CREATE, ALTER, and DROP triggers.

Event Attributes

With the introduction of the 20 new database triggers, came the creation of attribute events or variables that are set when a certain database trigger event is executed. The previous section highlighted each of the database triggers, along with the event attributes that are set and can be referenced for each trigger. Below is a list of each of the attribute events, the data type and a short description.
Attribute Event Data Type Description
ora_client_ip_address VARCHAR2 Provides the IP address of the client machine when using TCP/IP
ora_database_name VARCHAR2(50) Provides the database name
 
ora_des_encrypted_password VARCHAR2 Provides the DES encrypted password of the user being created or altered
ora_dict_obj_name VARCHAR(30) Provides the object name of the object being manipulated
ora_dict_obj_name_list
(name_list OUT
ora_name_list_t)
BINARY_INTEGER Provides a list of object names being manipulated
ora_dict_obj_owner VARCHAR(30) Provides the owner of the object being manipulated
ora_dict_obj_owner_list(owner_list OUT ora_name_list_t) BINARY_INTEGER Provides the owners of the objects being manipulated
ora_dict_obj_type VARCHAR(20) Provides the type of object being manipulated
ora_grantee(
  user_list
  OUT ora_name_list_t)
BINARY_INTEGER Provides the number of grantees
ora_instance_num NUMBER Provides the instance number.
ora_is_alter_column(
column_name IN VARCHAR2)
BOOLEAN Provides a return value of TRUE if the specified column is altered
ora_is_creating_nested_table BOOLEAN Provides a return value of TRUE if the current event is creating a nested table
ora_is_drop_column(
column_name IN VARCHAR2)
BOOLEAN Provides a return value of TRUE if the specified column is dropped
ora_is_servererror BOOLEAN Provides a return value of TRUE is the error specified is on the error stack
ora_login_user VARCHAR2(30) Provides the login schema
ora_partition_pos BINARY_INTEGER Provides the position in a CREATE TABLE command where the partition clause can be inserted when using the INSTEAD OF trigger
ora_privilege_list(
privilege_list OUT
ora_name_list_t)
BINARY_INTEGER Provides the list of privileges being granted or revoked
ora_revokee (user_list OUT
ora_name_list_t)
BINARY_INTEGER Provides a list of the revokees of the revoke command
ora_server_error NUMBER Provides the error on the error stack for the position specified in the stack (1 meaning the top of the stack)
ora_server_error_depth BINARY_INTEGER Provides the total number of errors on the error stack
ora_server_error_msg
(position in binary_integer)
VARCHAR2 Provides the error on the error stack for the position specified in the stack (1 meaning the top of the stack)
ora_server_error_num_params
(position in binary_integer)
BINARY_INTEGER Provides the number of strings that have been substituted into the error message on the error stack for the position specified in the stack (1 meaning the top of the stack)
ora_server_error_param
(position in binary_integer,
param in binary_integer)
VARCHAR2 Provides the matching substitution value in the error message for the parameter number specified in conjunction with the position specified on the stack ( 1 meaning the top of the stack)
ora_sql_txt (sql_text out
ora_name_list_t)
BINARY_INTEGER Provides the SQL statement of the statement that caused the trigger to execute (if the statement is lengthy, it will separate it into multiple PL/SQL table elements); the value returned specifies the number of elements
ora_sysevent VARCHAR2(20) Provides the system or client event that caused the trigger to execute
ora_with_grant_option BOOLEAN Provides a return value of TRUE if the privileges are granted with the grant option
space_error_info(
error_number OUT NUMBER,
error_type OUT VARCHAR2,
object_owner OUT VARCHAR2,
table_space_name OUT
VARCHAR2,
object_name OUT VARCHAR2,
sub_object_name OUT
VARCHAR2)
BOOLEAN
 
Provides a return value of true if the error is related to an out-of-space error and provides the object information of the object with the error
 
These attribute events allow extreme flexibility and functionality in each of the database triggers and should be used as necessary.

Database Trigger Examples (DBA and Developer)

The power and flexibility is endless with the 20 new database triggers. This section provides a variety of examples to illustrate this power. A list of the examples is provided below.
·         Logging Connection Time (LOGON/LOGOFF)
·         Pinning Objects Upon Startup (STARTUP)
·         Audit Trail of Objects (CREATE/ALTER/DROP)
·         Disable the Ability to Drop Objects (DROP)
·         Disable Logins Dynamically (LOGON)
·         Source Version History (CREATE)
·         Log SQL Information Upon Shutdown (SHUTDOWN)
 
Logging Connection Time (LOGON/LOGOFF)
The following example creates a logon statistics table and a LOGON and LOGOFF database trigger to capture the time when a user connects/disconnects to/from the database.

CREATE TABLE session_logon_statistics
(sid         NUMBER,
 user_logged VARCHAR2(30),
 start_time  DATE,
 end_time    DATE);


CREATE OR REPLACE TRIGGER logon_log_trigger
AFTER LOGON ON DATABASE
BEGIN
   INSERT INTO session_logon_statistics
   (sid, user_logged, start_time)
   SELECT DISTINCT sid, ora_login_user, SYSDATE
   FROM   v$mystat;
END;
/
 

CREATE OR REPLACE TRIGGER logoff_log_trigger
BEFORE LOGOFF ON DATABASE
BEGIN
   UPDATE session_logon_statistics
   SET    end_time    = SYSDATE
   WHERE  sid = (select distinct sid from v$mystat)
   AND    end_time IS NULL;
END;
/

create or replace trigger
   logoff_audit_trigger
BEFORE LOGOFF ON DATABASE
BEGIN
-- *************************************************
-- Update the last action accessed
-- *************************************************
update stats_user_log
set last_action = (select action from v$session
                   where sys_context('USERENV','SESSIONID') = audsid)
where sys_context('USERENV','SESSIONID') = session_id;
--*************************************************
-- Update the last program accessed
-- *************************************************
update stats_user_log
set last_program = (select program from v$session where     
                     sys_context('USERENV','SESSIONID') = audsid)
where sys_context('USERENV','SESSIONID') = session_id;
-- *************************************************
-- Update the last module accessed
-- *************************************************
update stats_user_log
set last_module = (select module from v$session where     
                   sys_context('USERENV','SESSIONID') = audsid)
where sys_context('USERENV','SESSIONID') = session_id;
-- *************************************************
-- Update the logoff day
-- *************************************************
update stats_user_log
set logoff_day = sysdate
where sys_context('USERENV','SESSIONID') = session_id;
-- *************************************************
-- Update the logoff time
-- *************************************************
update stats_user_log
set logoff_time = to_char(sysdate, 'hh24:mi:ss')
where sys_context('USERENV','SESSIONID') = session_id;
-- *************************************************
-- Compute the elapsed minutes
-- *************************************************
update stats_user_log
set elapsed_minutes = round((logoff_day - logon_day)*1440)
where sys_context('USERENV','SESSIONID') = session_id;
COMMIT;
END;
/
 

The SID is selected from the V$MYSTAT view. SELECT privilege must be granted on the V_$MYSTAT view to the creator of these triggers. The following script retrieves the information from the SESSION_LOGON_STATISTICS table.

COLUMN user_logged FORMAT a15
COLUMN start_time  FORMAT a20
COLUMN end_time    FORMAT a20
SELECT sid, user_logged,
       TO_CHAR(start_time, 'MM/DD/YYYY HH24:MI:SS') start_time,
       TO_CHAR(end_time, 'MM/DD/YYYY HH24:MI:SS') end_time
FROM   session_logon_statistics
order by sid, user_logged, start_time;
 
SID USER_LOGGED   START_TIME          END_TIME
--- ------------- ------------------- -------------------
12  TRIGGER_TEST  01/22/2003 19:11:53 01/22/2003 19:17:22
12  TRIGGER_TEST  01/22/2003 19:17:24 01/22/2003 19:17:46
13  PLSQL_USER    01/22/2003 19:12:19 01/22/2003 19:18:13
13  SYS           01/22/2003 19:18:38 01/22/2003 19:19:34
13  SYS           01/22/2003 19:19:35 01/22/2003 19:19:53
13  SYS           01/22/2003 19:19:59
14  TRIGGER_TEST  01/22/2003 19:12:29 01/22/2003 19:18:03
 
Pinning Objects Upon Startup (STARTUP)
The following example creates a startup mechanism that pins objects in the shared pool. It provides a dynamic and flexible method to control the pinning by merely inserting and deleting from a database table.
Because Oracle uses an LRU algorithm for the caching of objects in the Shared Pool, objects can be flushed out of the Shared Pool. If the objects are large, this will cause degradation in performance and possible errors because objects are loaded into the Shared Pool in contiguous segments. Heavily executed or large and important stored PL/SQL program units should be cached or pinned in the Shared Pool. Stored PL/SQL program units get pinned in the object cache and cursors get pinned in the SQL Area.
Oracle provides a procedure in the DBMS_SHARED_POOL package to pin these objects. This is the only method of pinning these objects in the shared pool and this package is not created by default. The dbmspool.sql script must be executed under the SYS schema to create this package. By default, even in Oracle 9.2, no objects, not even the STANDARD package is pinned by default.
The following is a pinning mechanism that allows stored PL/SQL program units to be pinned or unpinned by inserting or deleting the name of the program unit from a database table.
The following command creates the table to store the names of the stored PL/SQL program units to pin. This should be created under a user with DBA privilege.
CREATE TABLE objects_to_pin
   (owner    VARCHAR2(30) NOT NULL,
    object   VARCHAR2(128) NOT NULL,
    type     VARCHAR2(1) NOT NULL);
 
The following is a basic method of inserting objects to pin into the table.
INSERT INTO objects_to_pin (owner, object, type)
VALUES (UPPER('&owner'), UPPER('&object'), UPPER('&type'));
 
The type needs to follow the same conventions as defined in the SHARED_POOL package. This is outlined in the creation script of the DBMS_SHARED_POOL script (dbmspool.sql) and is included for reference below:
Value  Kind of Object to keep
-----  ----------------------
P      package/procedure/function
Q      sequence
R      trigger
T      type
JS     java source
JC     java class
JR     java resource
JD     java shared data
C      cursor
 
The following procedure takes one parameter to signify if the objects in the table should be pinned or unpinned. The default is to pin the objects. A U as input will unpin the objects in the table.
CREATE OR REPLACE PROCEDURE pin_objects
   (p_pin_flag_txt IN VARCHAR2 := 'P') IS
   -- The p_pin_flag_txt is either 'P' for pin
   -- or 'U' for unpin.
   CURSOR cur_pin_objects IS
      SELECT owner || '.' owner, object, type
      FROM   objects_to_pin
      ORDER BY owner, object;
BEGIN
    FOR cur_pin_objects_rec IN cur_pin_objects LOOP
      IF p_pin_flag_txt = 'U' THEN
         DBMS_SHARED_POOL.UNKEEP(cur_pin_objects_rec.owner ||
            cur_pin_objects_rec.object, cur_pin_objects_rec.type);
      ELSE
         DBMS_SHARED_POOL.KEEP(cur_pin_objects_rec.owner ||
            cur_pin_objects_rec.object, cur_pin_objects_rec.type);
      END IF;
   END LOOP;
END pin_objects;
/
 
The pin_objects procedure should be called from the database startup script to make certain the PL/SQL objects are pinned immediately, which will ensure the Shared Pool space is contiguously allocated in memory. The STANDARD package and SOURCE_HISTORY were insert into the OBJECTS_TO_PIN table using the INSERT script above. The following output displays the contents of this table.
COLUMN object FORMAT a30
SELECT * FROM   objects_to_pin;
 
OWNER           OBJECT                         TYPE                                                
--------------- ------------------------------ ------------                                        
SYS             STANDARD                       P                                                    
TRIGGER_TEST    SOURCE_HISTORY                 R
 
The following query displays the stored PL/SQL program units information regarding pinning. The script below has been modified to focus on the two objects that we are attempting to pin. The WHERE clause would usually only contain the WHERE  kept = 'YES' condition.



The modified script is shown and executed below.
COLUMN owner FORMAT a15
COLUMN name  FORMAT a25
COLUMN type  FORMAT a12
SET PAGESIZE 58
SELECT owner, name, type, kept
FROM   v$db_object_cache
WHERE  name in ('STANDARD', 'SOURCE_HISTORY');
 

OWNER           NAME                      TYPE         KEP                                              
--------------- ------------------------- ------------ ---                                              
SYS             STANDARD                  PACKAGE      NO                                               
SYS             STANDARD                  PACKAGE BODY NO                                                
TRIGGER_TEST    SOURCE_HISTORY            TABLE        NO                                               
TRIGGER_TEST    SOURCE_HISTORY            TRIGGER      NO
 
The previous query can be executed to list the sharable memory (sharable_mem column) required for the object and the number of times the object was loaded and executed (loads and executions columns) to determine which objects should be pinned.
In order to take this example full circle, a database startup script is created that calls the PIN_OBJECTS procedure. This will ensure that objects will be pinned upon startup no matter where the database is being started from (whether manually or through a script).
 
CREATE OR REPLACE TRIGGER db_startup_trigger
AFTER STARTUP ON DATABASE
BEGIN
   pin_objects;
END;
/
 
The database is then shutdown and started up and the query against the V$DB_OBJECT_CACHE view is executed again as shown below.
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
 

Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
 

OWNER           NAME              TYPE         KEP
--------------- ------------------------------ ---
SYS             STANDARD          PACKAGE      YES
SYS             STANDARD          PACKAGE BODY YES
TRIGGER_TEST    SOURCE_HISTORY    TABLE        NO
TRIGGER_TEST    SOURCE_HISTORY    TRIGGER      YES
 

Audit modification of Objects (CREATE/ALTER/DROP)

The next example provides the ability to build your own flexible object audit trail. The example below creates database triggers to capture and log every CREATE, ALTER and DROP operation on the database. First a table is created to store the audit information.
 
CREATE TABLE audit_object_mods
(mod_date      DATE,
 type_of_mod   VARCHAR2(20),
 mod_user      VARCHAR2(30),
 instance_num  NUMBER,
 database_name VARCHAR2(50),
 object_owner  VARCHAR2(30),
 object_type   VARCHAR2(20),
 object_name   VARCHAR2(30));
 
Next, the CREATE, ALTER, and DROP database triggers are created. These are created for all schemas. If the audit was only focused on one particular schema, then the “ON DATABASE” line would be changed to “ON TRIGGER_TEST.SCHEMA” where TRIGGER _TEST is the name of the schema desired to audit.
 
CREATE OR REPLACE TRIGGER create_object_trigger
AFTER CREATE ON DATABASE
BEGIN
   INSERT INTO audit_object_mods
      (mod_date, type_of_mod, mod_user,
       instance_num, database_name,
       object_owner, object_type, object_name)
   VALUES
      (sysdate, ora_sysevent, ora_login_user,
       ora_instance_num, ora_database_name,
       ora_dict_obj_owner, ora_dict_obj_type, ora_dict_obj_name);
END;
/
 

CREATE OR REPLACE TRIGGER alter_object_trigger
AFTER ALTER ON DATABASE
BEGIN
   INSERT INTO audit_object_mods
      (mod_date, type_of_mod, mod_user,
       instance_num, database_name,
       object_owner, object_type, object_name)
   VALUES
      (sysdate, ora_sysevent, ora_login_user,
       ora_instance_num, ora_database_name,
       ora_dict_obj_owner, ora_dict_obj_type, ora_dict_obj_name);
END;
/
 

CREATE OR REPLACE TRIGGER drop_object_trigger
AFTER DROP ON DATABASE
BEGIN
   INSERT INTO audit_object_mods
      (mod_date, type_of_mod, mod_user,
       instance_num, database_name,
       object_owner, object_type, object_name)
   VALUES
      (sysdate, ora_sysevent, ora_login_user,
       ora_instance_num, ora_database_name,
       ora_dict_obj_owner, ora_dict_obj_type, ora_dict_obj_name);
END;
/
 
At this point, the audit is set up and as these operations take place in the database, they will be logged to the AUDIT_OBJECT_MODS table. To view the audit, the following script can be executed.
 
COLUMN type_of_mod   FORMAT a10
COLUMN mod_user      FORMAT a12
COLUMN database_name FORMAT a10
COLUMN object_owner  FORMAT a12
COLUMN object_type   FORMAT a10
COLUMN object_name   FORMAT a25
SET LINESIZE 130
SELECT mod_date, type_of_mod, mod_user, instance_num,
       database_name, object_owner, object_type, object_name
FROM   audit_object_mods
ORDER BY mod_date, type_of_mod, object_owner, object_type, object_name;
 

MOD_DATE  TYPE_OF_MO MOD_USER     I_NUM DATABASE_N OBJECT_OWNER OBJECT_TYP OBJECT_NAME                                    
--------- ---------- ------------ ----- ---------- ------------ ---------- ------------------------                      
27-JAN-03 CREATE     TRIGGER_TEST     1 TUSC.WORLD TRIGGER_TEST TRIGGER    ALTER_OBJECT_TRIGGER
27-JAN-03 CREATE     TRIGGER_TEST     1 TUSC.WORLD TRIGGER_TEST TRIGGER    DROP_OBJECT_TRIGGER
27-JAN-03 CREATE     TRIGGER_TEST     1 TUSC.WORLD TRIGGER_TEST PROCEDURE  PIN_OBJECTS      
27-JAN-03 CREATE     TRIGGER_TEST     1 TUSC.WORLD TRIGGER_TEST TRIGGER    DB_STARTUP_TRIGGER
27-JAN-03 DROP       TRIGGER_TEST     1 TUSC.WORLD TRIGGER_TEST TRIGGER    DATABASE_STARTUP_TRIGGER
27-JAN-03 CREATE     TRIGGER_TEST     1 TUSC.WORLD TRIGGER_TEST TABLE      TEST14
27-JAN-03 ALTER      TRIGGER_TEST     1 TUSC.WORLD TRIGGER_TEST TRIGGER    DB_STARTUP_TRIGGER
27-JAN-03 ALTER      TRIGGER_TEST     1 TUSC.WORLD TRIGGER_TEST TRIGGER    DB_STARTUP_TRIGGER

 
The individual CREATE, ALTER, and DROP database triggers created previously can be replaced with one trigger creation with the use of the OR condition as shown below.
 
CREATE OR REPLACE TRIGGER c_a_d_object_trigger
AFTER CREATE OR ALTER OR DROP ON DATABASE
BEGIN
   INSERT INTO audit_object_mods
      (mod_date, type_of_mod, mod_user,
       instance_num, database_name,
       object_owner, object_type, object_name)
   VALUES
      (sysdate, ora_sysevent, ora_login_user,
       ora_instance_num, ora_database_name,
       ora_dict_obj_owner, ora_dict_obj_type, ora_dict_obj_name);
END;
/
 
Disable the Ability to Drop Objects (DROP)
The following example illustrates how a trigger can be created for specific schema to disallow the ability to DROP objects. The trigger is for the XXX user.
 
CREATE OR REPLACE TRIGGER stop_drop_trigger
   BEFORE DROP ON XXX.SCHEMA
   BEGIN
      RAISE_APPLICATION_ERROR (
         num => -20000,
         msg => 'Cannot DROP Objects');
   END;
/
 
When the XXX USER attempts to DROP any object an error will result as shown below.
SHOW USER
USER is "XXX"
 

DROP TABLE temp;
drop table temp
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: Cannot DROP Objects
ORA-06512: at line 2
 

An example to avoid alter or drop column:
create table scott.test ( c number);

create trigger trtest
  before drop or alter on database
begin
  if ora_sysevent = 'ALTER' and ora_dict_obj_type = 'TABLE' and ora_dict_obj_name = 'TEST' then
     if ora_is_alter_column('C') then
        raise_application_error(-20001,'Cannot Alter column');
     elsif ora_is_drop_column('C') then
        raise_application_error(-20001,'Cannot Drop column');
     end if;
   end if;
end;
/

 

Protecting from Dropping
TRIGGER SCHEMA_DDL_PROTECT
   BEFORE DDL ON SCHEMA
DECLARE
   v_exec_user VARCHAR2(1000) := sys_context('USERENV', 'OS_USER');
   NO_PRIVS EXCEPTION;
   PRAGMA EXCEPTION_INIT(NO_PRIVS, -20202);
BEGIN
   IF ora_sysevent IN('TRUNCATE', 'ALTER') AND LOWER(V_EXEC_USER) NOT IN( 'lee.s', 'lee', 'oracle') THEN
      INSERT INTO REPORTS.AUD_DDL(OBJECT_OWNER, OBJECT_NAME, OBJECT_TYPE, DDL, OS_USER, LOGIN_USER, TSP)
         VALUES(ora_dict_obj_owner, ora_dict_obj_name, ora_dict_obj_type, ora_sysevent, V_EXEC_USER, ora_login_user, SYSDATE);
   ELSE
      IF LOWER(V_EXEC_USER) NOT IN( 'lee.s', 'lee', 'oracle') AND V_EXEC_USER IS NOT NULL THEN
         RAISE_APPLICATION_ERROR(-20202, 'Un-authorize DDL, next time you try this Oracle will blow-up your computer');
      END IF;
   END IF;
END SCHEMA_DDL_PROTECT;
/

Disable Logins Dynamically (LOGON)
Oracle has added features and commands to allow a DBA to perform more and more of their responsibilities while the database is up and running, thus reducing the time of off-hours administration. With database triggers, the logon trigger allows a DBA to disable new logons by setting a flag in a table. The following table can be used for this toggle flag.
 
CREATE TABLE logons_allowed
(logons_allowed VARCHAR2(3) NOT NULL);
 
A record is inserted into this table with the value of YES. The following logon trigger is created to reference this table upon logon and look for the value of the record to determine if logins are allowed.
 
CREATE OR REPLACE TRIGGER logon_allow_trigger
AFTER LOGON  ON DATABASE
DECLARE
   CURSOR logon_allowed IS
      SELECT logons_allowed
         FROM   logons_allowed;
   lv_allowed   logons_allowed.logons_allowed%TYPE;
BEGIN
   OPEN logon_allowed;   
   FETCH logon_allowed INTO lv_allowed;
   IF lv_allowed != 'YES' THEN
      CLOSE logon_allowed;
      RAISE_APPLICATION_ERROR (
         num => -20000,
         msg => 'Logins Not Allowed.');
   ELSE
      CLOSE logon_allowed;
   END IF;
END;
/
 
The trigger looks for a value of YES and if the value is NOT YES, then the login will not succeed and the login will fail with an error. To illustrate, the table is updated and the logons_allowed value is set to NO. A user attempts to login and they get the following error:
 
SQL*Plus: Release 9.2.0.1.0 - Production on Wed Jan 22 2003
Copyright (c) 1982, 2002, Oracle Corporation.
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: Logins Not Allowed.
ORA-06512: at line 12
 
Any time an error is returned either at runtime as forced in the above trigger or if the above trigger was INVALID due to an error upon creation, the SYS and SYSTEM users are exempt from errors in the logon triggers and will be allowed to logon. Likewise, any schema with ADMINISTER DATABASE TRIGGER system privilege follows the same logic. Therefore, when the creator of the trigger above (TRIGGER_TEST who has the above privilege), as well as the SYS and SYSTEM users attempt to logon, they succeed. If the trigger was created with an error and is INVALID, the 3 users and any schema with the privilege above would still succeed upon logon. However, if the trigger was created with an error and is INVALID and anyone else attempts to logon, they will receive the following message.
 
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Jan 28 2003
Copyright (c) 1982, 2002, Oracle Corporation.
ERROR:
ORA-04098: trigger 'TRIGGER_TEST.LOGON_ALLOW_TRIGGER' is invalid and failed
re-validation
Enter user-name:
 
Source Version History (CREATE)
The ability to view the source code of stored PL/SQL objects in the database provides a lot of advantages, however, the biggest disadvantage is that developers often times bypass version control mechanisms in place to make a quick fix and forget to update the main source code version of the PL/SQL object that resides outside of the database. The problem comes in when the next change is made the correct way by modifying the code outside the database and then recreating it in the database. The prior change made in the database is now lost. I have heard about this many times.
The new triggers provide a mechanism to help this problem and create an audit trail/version of all PL/SQL source code created in the database. This is accomplished by inserting the new source code for an object into a history table each time an object is created or recreated. It is accomplished behind the scenes with the CREATE trigger. Each of the creations is also timestamped with the creation date.
A history table is first created that mirrors the DBA_SOURCE table as shown below.
 
CREATE TABLE source_history
(change_date DATE           NOT NULL,
 owner       VARCHAR2(30)   NOT NULL,
 name        VARCHAR2(30)   NOT NULL,
 type        VARCHAR2(20),
 line        NUMBER         NOT NULL,
 text        VARCHAR2(4000));
 
A CREATE trigger is then created that will insert into the SOURCE_HISTORY table after each new object creation as shown below.
 
CREATE OR REPLACE trigger source_history
AFTER CREATE ON DATABASE
BEGIN
   INSERT INTO source_history
   SELECT SYSDATE, owner, name, type, line, text
   FROM  dba_source
   WHERE owner = ORA_DICT_OBJ_OWNER
   AND   name  = ORA_DICT_OBJ_NAME
   AND   type  = ORA_DICT_OBJ_TYPE;
END source_history;
/
 
The same scripts created for the DBA_SOURCE view can now be used on this view by only changing the view name to SOURCE_HISTORY. A sample script is shown below with the query limited to one object.
 
COLUMN owner FORMAT a12
COLUMN name  FORMAT a11
COLUMN line  FORMAT 9999
COLUMN text  FORMAT a60 WORD_WRAPPED
SELECT change_date, owner, name, type, line, text
FROM   source_history
WHERE  name = 'PIN_OBJECTS'
order by change_date, owner, name, type, line;
 

CHANGE_DA OWNER        NAME        TYPE        LINE TEXT                                                                         
--------- ------------ ----------- ---------- ----- ------------------------------------------------------------                 
27-JAN-03 TRIGGER_TEST PIN_OBJECTS PROCEDURE      1 PROCEDURE pin_objects                                                        
27-JAN-03 TRIGGER_TEST PIN_OBJECTS PROCEDURE      2 (p_pin_flag_txt IN VARCHAR2 := 'P') IS                                       
27-JAN-03 TRIGGER_TEST PIN_OBJECTS PROCEDURE      3 -- The p_pin_flag_txt is either 'P' for pin                                  
27-JAN-03 TRIGGER_TEST PIN_OBJECTS PROCEDURE      4 -- or 'U' for unpin.                                                         
27-JAN-03 TRIGGER_TEST PIN_OBJECTS PROCEDURE      5 CURSOR cur_pin_objects IS                                                    
27-JAN-03 TRIGGER_TEST PIN_OBJECTS PROCEDURE      6 SELECT owner || '.' owner,                                                   
27-JAN-03 TRIGGER_TEST PIN_OBJECTS PROCEDURE      7 object                                                                       
27-JAN-03 TRIGGER_TEST PIN_OBJECTS PROCEDURE      8 FROM   objects_to_pin                                                        
27-JAN-03 TRIGGER_TEST PIN_OBJECTS PROCEDURE      9 ORDER BY owner, object;                                                      
27-JAN-03 TRIGGER_TEST PIN_OBJECTS PROCEDURE     10 BEGIN                                                                        
27-JAN-03 TRIGGER_TEST PIN_OBJECTS PROCEDURE     11 FOR cur_pin_objects_rec IN cur_pin_objects LOOP                              
27-JAN-03 TRIGGER_TEST PIN_OBJECTS PROCEDURE     12 IF p_pin_flag_txt = 'U' THEN                                                 
27-JAN-03 TRIGGER_TEST PIN_OBJECTS PROCEDURE     13 DBMS_SHARED_POOL.UNKEEP(cur_pin_objects_rec.owner ||                         
27-JAN-03 TRIGGER_TEST PIN_OBJECTS PROCEDURE     14 cur_pin_objects_rec.object, 'P');                                            
27-JAN-03 TRIGGER_TEST PIN_OBJECTS PROCEDURE     15 ELSE                                                                          
27-JAN-03 TRIGGER_TEST PIN_OBJECTS PROCEDURE     16 DBMS_SHARED_POOL.KEEP(cur_pin_objects_rec.owner ||                           
27-JAN-03 TRIGGER_TEST PIN_OBJECTS PROCEDURE     17 cur_pin_objects_rec.object, 'P');                                             
27-JAN-03 TRIGGER_TEST PIN_OBJECTS PROCEDURE     18 END IF;                                                                      
27-JAN-03 TRIGGER_TEST PIN_OBJECTS PROCEDURE     19 END LOOP;                                                                     
27-JAN-03 TRIGGER_TEST PIN_OBJECTS PROCEDURE     20 END pin_objects;
 
This query can also be changed to include the time .

Log SQL Information Upon Shutdown (SHUTDOWN)

The following illustrates a method of logging information when the database is shutdown. This can be extended to log more information or perform certain operations. The example logs information from the V$SQLAREA view. First, a log table is created with a timestamp column as shown below.
 
CREATE TABLE sqlarea_history
(log_date         DATE,
 disk_reads       NUMBER,
 executions       NUMBER,
 execution_ratio      NUMBER,
 sql_text         VARCHAR2(1000));
 
A procedure is created to log the desired information from the view into the log table. There is a low threshold in the example below for the number of READS. This can be modified for your system to only log the SQL for statements over a large number of disk reads.
 
CREATE OR REPLACE PROCEDURE SQLAREA_LOG AS
   CURSOR c1 IS
      SELECT disk_reads reads, executions exe,
          ROUND(disk_reads/DECODE(executions, 0, 1, executions)) ratio,
          sql_text text
        FROM   v$sqlarea
        WHERE  disk_reads > 100
        ORDER BY disk_reads/DECODE(executions, 0, 1, executions) DESC;
   lv_current_date DATE := SYSDATE;
BEGIN
   FOR c1_rec in c1 LOOP
      INSERT INTO sqlarea_history
         (log_date, disk_reads, executions, execution_ratio, sql_text)
      VALUES
         (lv_current_date, c1_rec.reads, c1_rec.exe, c1_rec.ratio,c1_rec.text);
   END LOOP;
END;
/
 
Lastly, the database shutdown trigger is created to call the SQLAREA_LOG procedure.
 
CREATE OR REPLACE TRIGGER db_shutdown_trigger
BEFORE SHUTDOWN ON DATABASE
BEGIN
   sqlarea_log;
END;
/
 
The SQL can then be queried at anytime in the future with the following statement.
 
COLUMN sql_text FORMAT a25
COLUMN log_date FORMAT a19
SET PAGESIZE 58
SELECT TO_CHAR(log_date, 'MM/DD/YYYY:HH24:MI:SS') log_date,
       disk_reads, executions exe, execution_ratio ratio, sql_text
FROM   sqlarea_history;
 

LOG_DATE            DISK_READS   EXE   RATIO SQL_TEXT                  
------------------- ---------- ----- ------- ------------------------- 
01/22/2003:22:55:09        379     1     379 select o.owner#,o.obj#,de 
                                             code(o.linkname,null, dec 
                                             ode(u.name,null,'SYS',u.n 
                                             ame),o.remoteowner), o.na 
                                             me,o.linkname,o.namespace 
                                             ,o.subname from user$ u,  
                                             obj$ o where u.user#(+)=o 
                                             .owner# and o.type#=:1 an 
                                             d not exists (select p_ob 
                                             j# from dependency$ where 
                                             p_obj# = o.obj#) for upd 
                                             ate                       
                                       
01/22/2003:22:55:09        213     1     213 select distinct i.obj# fr 
                                             om sys.idl_ub1$ i where i 
                                             .obj#>=:1 and i.obj# not  
                                             in (select d.p_obj# from  
                                             sys.dependency$ d)        
                                        
01/22/2003:22:55:09        115     1     115 select distinct d.p_obj#, 
                                             d.p_timestamp from sys.de 
                                             pendency$ d, obj$ o where 
                                             d.p_obj#>=:1 and d.d_obj 
                                             #=o.obj# and o.status!=5  

                                                                               

Data Dictionary Views for Database Triggers

There are several data dictionary views that reveal information about triggers. The main two views center on the source code view that contains the source code for package, procedures and functions, but also stores the compilation status of triggers. This is important to determine if a trigger has been compiled successfully. The source code for triggers and the important information regarding being ENABLED or DISABLED is also stored in the trigger views. There are 3 views for each that center on the scope of the information. These are listed below.
 
      user_triggers     user_source
      all_triggers      all_source
      dba_triggers      dba_source
 
Some examples follow to exemplify the usefulness of these views.

The first example script provides information on the compilation status of triggers. A status of VALID indicates the trigger is compiled and is ready for execution. A status of INVALID means that the trigger needs to be compiled prior to execution. This will be automatically attempted by Oracle the next time the trigger is fired or manually with the ALTER…COMPILE command prior to the next firing of the trigger. I always recommend recompiling manually to ensure the compilation will be valid and if not, then changes can be made until it does become VALID. You always want to know prior to production if there will be compilation issues that you need to address.
 
COLUMN object_name FORMAT a24
SELECT object_name, object_type, status
  FROM   user_objects
  WHERE  object_type = 'TRIGGER';
 

OBJECT_NAME              OBJECT_TYP STATUS 
------------------------ ---------- -------
ALTER_OBJECT_TRIGGER     TRIGGER    VALID   
CREATE_OBJECT_TRIGGER    TRIGGER    VALID   
DB_SHUTDOWN_TRIGGER      TRIGGER    VALID   
DB_STARTUP_TRIGGER       TRIGGER    VALID   
DROP_OBJECT_TRIGGER      TRIGGER    VALID  
LOGOFF_LOG_TRIGGER       TRIGGER    VALID   
LOGON_ALLOW_TRIGGER      TRIGGER    VALID  
LOGON_LOG_TRIGGER        TRIGGER    VALID

SOURCE_HISTORY           TRIGGER    VALID
STOP_DROP_TRIGGER        TRIGGER    VALID
TEST                     TRIGGER    VALID
TEST_LOGON               TRIGGER    VALID 
 
As evidenced in the output, all triggers are VALID at the current time. If an object that is referenced by one of these triggers is dropped or altered in any manner, this will cause the trigger to become INVALID.
 
The second example provides information about the triggers in the system. This is useful to understand the database and schema triggers that are defined in a database.
 
COLUMN trigger_name FORMAT a24
COLUMN triggering_event FORMAT a15
SELECT trigger_name, trigger_type, base_object_type, triggering_event, status
  FROM   user_triggers
  WHERE  db_object_type IN ('DATABASE        ', 'SCHEMA');
 

TRIGGER_NAME          TRIGGER_TYPE  BASE_OBJECT_TYPE TRIGGERING_EVEN STATUS
--------------------- ------------------------------ --------------- -------
ALTER_OBJECT_TRIGGER  AFTER EVENT   DATABASE         ALTER           ENABLED
CREATE_OBJECT_TRIGGER AFTER EVENT   DATABASE         CREATE          ENABLED
DB_SHUTDOWN_TRIGGER   BEFORE EVENT  DATABASE         SHUTDOWN        ENABLED
DB_STARTUP_TRIGGER    AFTER EVENT   DATABASE         STARTUP         ENABLED
DROP_OBJECT_TRIGGER   AFTER EVENT   DATABASE         DROP            ENABLED
LOGOFF_LOG_TRIGGER    BEFORE EVENT  DATABASE         LOGOFF          ENABLED
LOGON_ALLOW_TRIGGER   AFTER EVENT   DATABASE         LOGON           ENABLED
LOGON_LOG_TRIGGER     AFTER EVENT   DATABASE         LOGON           ENABLED
SOURCE_HISTORY        AFTER EVENT   DATABASE         CREATE          ENABLED
TEST_LOGON            AFTER EVENT   DATABASE         LOGON           ENABLED
STOP_DROP_TRIGGER     BEFORE EVENT  SCHEMA           DROP            ENABLED
TEST                  AFTER EVENT   SCHEMA           LOGON           ENABLED
 
The output identifies the triggers with the type of triggers, events that cause each to execute, the timing of the execution (when) and the execution status. Refer to the previous section on ENABLING and DISABLING triggers for more information on this view.
 
The last example provides the source code of each of the triggers (limited to the DB_STARTUP_TRIGGER trigger).
 
COLUMN trigger_name FORMAT a24
COLUMN triggering_event FORMAT a15
COLUMN trigger_body FORMAT a25 word_wrapped
SELECT trigger_name, trigger_type, base_object_type, triggering_event, trigger_body
  FROM   user_triggers
  WHERE  trigger_name = 'DB_STARTUP_TRIGGER';
 

TRIGGER_NAME       TRIGGER_TYPE BASE_OBJECT_TYPE TRIGGERING_EVEN TRIGGER_BODY
------------------ ------------ ---------------- --------------- ------------
DB_STARTUP_TRIGGER AFTER EVENT  DATABASE         STARTUP         BEGIN       
                                                                 pin_objects;
                                                                 END;
 
The output provides attributes on the triggers, as well as, the entire contents of the trigger that was created.