About Triggers - Firebird

A trigger is a self-contained routine associated with a table or view that automatically performs an action when a row in the table or view is inserted, updated, or deleted.

A trigger is never called directly. Instead, when an application or user attempts to INSERT, UPDATE, or DELETE a row in a table, any triggers associated with that table and operation are automatically executed, or fired. Triggers can make use of exceptions and can trigger events.They can also call stored procedures.

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

  • To make correlated updates when DML is performed on the table. For example, a trigger could insert records to an internal or external change log. An AFTER DELETE trigger could insert a row to a history table.
  • To validate input data.
  • To transform data, for example, to automatically convert text input to uppercase or to fetch an auto-incrementing key value from a generator.
  • To notify applications of changes in the database using event alerters.
  • To perform custom cascading referential integrity updates.
  • To make a read-only view updatable.

Triggers are stored as objects in 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—an active trigger fires automatically when the specified DML operation occurs on the owning table.

Benefits of Using Triggers

The benefits of using triggers include

  • Automatic enforcement of data restrictions, to make sure users enter only valid values into columns.
  • Reduced application maintenance, since changes to a trigger are automatically reflected in all applications that use the associated table without the need to recompile and relink.
  • Automatic logging of changes to tables. An application can keep a running log of changes with a trigger that fires whenever a table is modified.
  • Automatic notification of changes to the database with event alerters in triggers.

Triggers As an Auto-increment Mechanism

Triggers can be used in combination with generators to implement an auto-incrementing key.

Triggers and Transactions

Triggers always operate within the context of a specific DML operation, as part of that operation and inside the transaction that makes the DML statement request. They are in no sense separated from the transaction or from the operation that causes them to fire. If the transaction is rolled back, then any actions performed by triggers are also rolled back.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics