Database triggers are procedures that are stored in the database and implicitly executed when a triggering statement such as INSERT, UPDATE, or DELETE is issued against the associated table. Triggers can be defined only on tables, not on views. However, triggers on the base table of a view are fired if an INSERT, UPDATE, or DELETE statement is issued against a view.
A trigger can include SQL and PL/SQL statements that execute as a unit, and can invoke other stored procedures. Use triggers only when necessary. Excessive use of triggers can result in cascading or recursive triggers. For example, when a trigger is fired, a SQL statement in the trigger body potentially can fire other triggers.
By using database triggers, you can enforce complex business rules and ensure that all applications behave in a uniform manner. Use the following guidelines when designing triggers:
Use triggers to guarantee that when a specific operation is performed, related actions are performed.
Use database triggers only for centralized, global operations that should be fired for the triggering statement, regardless of which user or database application issues the statement.
Do not define triggers that duplicate the functionality already built into Oracle. For example, do not define triggers to enforce data integrity rules that can be easily enforced using declarative integrity constraints.
Limit the size of triggers (60 lines or fewer is a good guideline). If the logic for your trigger requires much more than 60 lines of PL/SQL code, it is better to include most of the code in a stored procedure, and call the procedure from the trigger.
Be careful not to create recursive triggers. For example, creating an AFTER UPDATE statement trigger on the EMP table that itself issues an UPDATE statement on EMP causes the trigger to fire recursively until it has run out of memory.
For additional information about how triggers are used in applications, see the Oracle Application Developer's Guide. See the Oracle Concepts Manual for more information about the different types of triggers.
Copyright © 1984, 2005, Oracle. All rights reserved.