13 Triggers
How do I…
13.1 Create a database trigger?
13.2 Use column values within database triggers?
13.3 Fire triggers selectively using trigger predicates?
13.4 Perform cascading deletes with database triggers?
13.5 Disable and enable triggers?
13.6 List information about triggers?
13.7 Rebuild CREATE TRIGGER statements?
Database triggers execute when a specific DML operation on a table is performed. Triggers can be used to ensure that specific business rules related to a table are performed when records are inserted, updated, or deleted. As triggers are stored in the database and tied to the table, they fire regardless of which application performed the operation.
You can use database triggers to ensure the integrity and consistency of data. Like stored procedures and functions, triggers are a fundamental part of application partitioning. Moving business rules from the application code to the database ensures that the business rules are enforced and performance improved. Triggers are typically used for auditing and event logging purposes, as well as security authorizations, to prevent invalid transactions, data replication, and most importantly to enforce complex integrity constraints.
Triggers should be used to augment the power of declarative integrity constraints functionality, which is already built into Oracle (refer to Chapter 6). The INSTEAD-OF triggers provide a way of updating object views as well as relational views, which will be examined in Chapter 16.
13.1 Create a Database Trigger?
Database triggers ensure that an action is performed when a table is modified. They can be used to enforce business rules regardless of the source of the operation. This How-To demonstrates the creation of database triggers and the different types of triggers that can be created.
13.2 Use Column Values Within Database Triggers?
It is almost always necessary to use the column values from the table containing the trigger. The :NEW and :OLD pseudo-records allow the column values to be used within a database trigger. If a statement within a trigger requires a value from the updated or new record, then :NEW is used. If a statement requires the value of a column before it was modified, then :OLD is used. This How-To explores the use of column values within database triggers.
13.3 Fire Triggers Selectively Using Trigger Predicates?
Instead of writing three different triggers for INSERT, UPDATE, and DELETE operations, you might want to write a single trigger that handles any DML operation on a given table. The trigger body can use conditional predicates to recognize the type of DML operation that caused the trigger to fire. This How-To presents the use of trigger predicates to conditionally execute code within the trigger.
13.4 Perform Cascading Deletes with Database Triggers?
There will be situations when you will want to use database triggers to delete the data related to a record deleted from a table. If you want to delete a record referenced as the foreign key to a record in another table, the delete operation must be cascaded through the data model. This How-To presents the method for deleting related data using database triggers.
13.5 Disable and Enable Triggers?
Database triggers will fire whenever a table is modified. You need to disable database triggers in order to perform management tasks or to perform batch operations. When a trigger is disabled, it will not fire when the triggering event occurs on the table. This How-To presents the method used to disable and enable triggers when necessary.