Using Triggers

Go Up to Triggers (Data Definition Guide)

Triggers are a powerful feature with a variety of uses. Among the ways that triggers can be used are:

• To make correlated updates. For example, to keep a log file of changes to a database or table.
• To enforce data restrictions, so that only valid data is entered in tables.
• Automatic transformation of data. For example, to automatically convert text input to uppercase.
• To notify applications of changes in the database using event alerters.
• To perform cascading referential integrity updates.

Triggers are stored as part of a database, like stored procedures and exceptions. Once defined to be ACTIVE, they remain active until deactivated with ALTER TRIGGER or removed from the database with DROP TRIGGER.

A trigger is never explicitly called. Rather, an active trigger automatically fires when the specified action occurs on the specified table.

Important: If a trigger performs an action that causes it to fire again—or fires another trigger that performs an action that causes it to fire—an infinite loop results. For this reason, it is important to ensure that a trigger’s actions never cause the trigger to fire, even indirectly. For example, an endless loop will occur if a trigger fires on INSERT to a table and then performs an INSERT into the same table.

Topics

• Triggers and Transactions
• Triggers and Security
• Triggers as Event Alerters
• Updating Views with Triggers