Database Triggers and event attributes--Introduction

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:

• CREATE TRIGGER: this privilege allows for a schema to create a database trigger on a table they own.
• CREATE ANY TRIGGER: this privilege allows a schema to create a database trigger on a table owned by another schema.
• ADMINISTER DATABASE TRIGGER: this privilege allows a schema to create a database wide database trigger.

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;

[@more@]