Guidelines for Designing Triggers

Use the following guidelines when designing triggers:

• Use triggers to guarantee that when a specific operation is performed, related actions are performed.
• Do not define triggers that duplicate database features.

For example, do not define triggers to reject bad data if you can do the same checking through constraints.

Although you can use both triggers and integrity constraints to define and enforce any type of integrity rule, Oracle strongly recommends that you use triggers to constrain data input only in the following situations:

• To enforce referential integrity when child and parent tables are on different nodes of a distributed database
• To enforce complex business rules not definable using integrity constraints
• When a required referential integrity rule cannot be enforced using the following integrity constraints:
• NOT NULL, UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK
• DELETE CASCADE
• DELETE SET NULL
• Limit the size of triggers.

If the logic for your trigger requires much more than 60 lines of PL/SQL code, put most of the code in a stored subprogram and invoke the subprogram from the trigger.

The size of the trigger cannot exceed 32K.

• Use triggers only for centralized, global operations that must fire for the triggering statement, regardless of which user or database application issues the statement.
• Do not create recursive triggers.

For example, if you create an AFTER UPDATE statement trigger on the employees table, and the trigger itself issues an UPDATE statement on the employees table, the trigger fires recursively until it runs out of memory.

• Use triggers on DATABASE judiciously. They are executed for every user every time the event occurs on which the trigger is created.
• If you use a LOGON trigger to monitor logons by users, include an exception-handling part in the trigger, and include a WHEN OTHERS exception in the exception-handling part. Otherwise, an unhandled exception might block all connections to the database.
• If you use a LOGON trigger only to execute a package (for example, an application context-setting package), put the exception-handling part in the package instead of in the trigger.

Privileges Required to Use Triggers

To create a trigger in your schema:

• You must have the CREATE TRIGGER system privilege
• One of the following must be true:
• You own the table specified in the triggering statement
• You have the ALTER privilege for the table specified in the triggering statement
• You have the ALTER ANY TABLE system privilege

To create a trigger in another schema, or to reference a table in another schema from a trigger in your schema:

• You must have the CREATE ANY TRIGGER system privilege.
• You must have the EXECUTE privilege on the referenced subprograms or packages.

To create a trigger on the database, you must have the ADMINISTER DATABASE TRIGGER privilege. If this privilege is later revoked, you can drop the trigger but not alter it.

The object privileges to the schema objects referenced in the trigger body must be granted to the trigger owner explicitly (not through a role). The statements in the trigger body operate under the privilege domain of the trigger owner, not the privilege domain of the user issuing the triggering statement (this is similar to the privilege model for stored subprograms).

< <Overview of Triggers

Creating Triggers>>