Compiling Triggers

Triggers are similar to PL/SQL anonymous blocks with the addition of the :NEW and :OLD capabilities, but their compilation is different. A PL/SQL anonymous block is compiled each time it is loaded into memory. Triggers, in contrast, are fully compiled when the CREATE TRIGGER statement is entered, and the code is stored in the data dictionary. This means that a trigger is executed directly.

This section contains the following topics:

Trigger Errors
Dependencies for Triggers
Recompiling Triggers

Trigger Errors

If errors occur during the compilation of a trigger, then the trigger is still created. If a DML statement fires this trigger, then the DML statement fails. You can use the SHOW ERRORS statement in SQL Command Line to display any compilation errors when you create a trigger in SQL, or you can use the SELECT statement to display the errors from the USER_ERRORS view as follows:

SELECT * FROM USER_ERRORS WHERE TYPE = 'TRIGGER';

Dependencies for Triggers

Compiled triggers have dependencies on database objects and become invalid if these objects, such as a table accessed from or a stored procedure called from the trigger body, is modified. Triggers that are invalidated for dependency reasons are recompiled the next time they are invoked.

You can examine the ALL_DEPENDENCIES view to see the dependencies for a trigger. Example: Viewing the Dependencies for a Trigger shows the use of the SQL SELECT statement to display the dependencies for a trigger in the HR schema.

Viewing the Dependencies for a Trigger

SELECT NAME, REFERENCED_OWNER, REFERENCED_NAME, REFERENCED_TYPE
    FROM ALL_DEPENDENCIES
    WHERE OWNER = 'HR' and TYPE = 'TRIGGER' AND NAME = 'LOG_EMP_UPDATE';

You can also view information about a trigger with Object Browser. See "Viewing a Trigger With Object Browser".

Triggers can depend on other functions, procedures, or packages. If the function, procedure, or package specified in the trigger is dropped, then the trigger is marked invalid. An attempt is made to validate the trigger when the event occurs. If the trigger cannot be validated successfully, then it is marked VALID WITH ERRORS, and the event fails. For information on viewing invalid triggers in a database, see "Viewing Information With Object Reports".

Note:

There is an exception for STARTUP events. STARTUP events succeed even if the trigger fails. There are also exceptions for SHUTDOWN events and for LOGON events if you login as SYSTEM.

Recompiling Triggers

Use the ALTER TRIGGER statement to recompile a trigger manually. Example: Recompiling a Trigger shows the use of the SQL ALTER TRIGGER statement to recompile the emp_log_update trigger.

Recompiling a Trigger

ALTER TRIGGER log_emp_update COMPILE;
-- cleanup: drop the log_emp_update trigger and emp_update_log table 
DROP TRIGGER log_emp_update;
DROP TABLE emp_update_log;

To recompile a trigger, you must own the trigger or have the ALTER ANY TRIGGER system privilege.