Restrictions For Creating Triggers

When creating triggers with PL/SQL code, there are some restrictions that are not required for standard PL/SQL blocks. The following sections discuss these restrictions.

SQL Statements Allowed in Trigger Bodies

The body of a trigger can contain DML SQL statements. It can also contain SELECT statements, but they must be SELECT... INTO... statements or the SELECT statement in the definition of a cursor.

DDL statements are not allowed in the body of a trigger and transaction control statements are not allowed in a trigger. ROLLBACK, COMMIT, and SAVEPOINT statements cannot be used. For system triggers, CREATE, ALTER, and DROP TABLE statements and ALTER...COMPILE statements are allowed.

Note:

A procedure called by a trigger cannot run the previous transaction control statements because the procedure runs within the context of the trigger body.

Statements inside of a trigger can reference remote schema objects. However, pay special attention when calling remote procedures from within a local trigger. If a timestamp or signature mismatch is found during execution of the trigger, then the remote procedure is not run, and the trigger is invalidated.

System Trigger Restrictions

Only committed triggers are fired. For example, if you create a trigger that should be fired after all CREATE events, then the trigger itself does not fire after the creation, because the correct information about this trigger was not committed at the time when the trigger on CREATE events was fired.

For example, if you execute the following SQL statement, trigger my_trigger is not fired after the creation of my_trigger. Oracle Database XE does not fire a trigger that is not committed.

CREATE OR REPLACE TRIGGER my_trigger
AFTER CREATE ON DATABASE
BEGIN
NULL;
END;