Triggers in Firebird

General

Triggers are self-contained routines (special Stored Procedures), which are connected to a table or view
A trigger is automatically executed ("fired") when a record is inserted, updated or deleted from a table/view
Triggers are never called directly but only through one of the commands INSERT, UPDATE or DELETE
Triggers are written in Stored Procedure and Trigger Language and can use Exceptions

Advantages

Automatic enforcement of conditions and limitations for the contents
Reduced maintenance for the applications
You can implement a modification logging mechanism for tables
Generators can be called automatically and their values assigned to fields
Automatic notification of applications by calling of Events
Automatic upper-casing of fields to enable case-insensitive searching

General Syntax

CREATE [OR ALTER] TRIGGER name FOR tablename
  [ACTIVE | INACTIVE]
  {BEFORE | AFTER} {INSERT | UPDATE | DELETE}
    [OR {INSERT | UPDATE | DELETE}
      [OR {INSERT | UPDATE | DELETE}]]
  [POSITION number]
  AS <trigger_body>

ACTIVE/INACTIVE: A trigger can be deactivated, e.g. for development. Can also be used with ALTER TRIGGER.
BEFORE/AFTER: Tells the system if the trigger must be fired before or after the Insert/Update/Delete operation
POSITION: Trigger calling order. Triggers with low numbers are fired first. Triggers with the same position number are fired in an undefined order. The position defaults to 0.
trigger_body: Same as body in a Stored Procedure

Differences to Stored Procedures

Context variables (OLD.colname and NEW.colname) can only be used in triggers
Triggers do not have in- and/or out-parameters. There is no SUSPEND and no EXIT.
NEW.colname: New column value for INSERT or UPDATE operations
OLD.colname: Old column value for UPDATE or DELETE operations

Context Variables

Variable Name	Contents
OLD.field	Content of field before insert/update/delete
NEW.field	New field content for an insert or update operation
INSERTING	True if the trigger is called for an INSERT operation
UPDATING	True if the trigger is called for an UPDATE operation
DELETING	True if the trigger is called for a DELETE operation

Altering and deleting triggers

ALTER TRIGGER name ACTIVE | INACTIVE: activate/deactivate a trigger
ALTER TRIGGER name ...: Change a complete trigger definition
DROP TRIGGER name: Delete trigger. Can only be performed by the owner of the trigger. Can not be performed when the trigger is currently running.
CREATE OR ALTER TRIGGER name ...: Create trigger if it doesn't exist, alter trigger otherwise

Triggers and Transactions

Triggers run in the context of their transaction, they are treated as a part of the current operation
The actions of a trigger which fired in a transaction which is ROLLBACKed, will be rolled back, too.

Examples

Insert a Generator value for a newly inserted record

CREATE TRIGGER Create_Log_ID FOR Logs
BEFORE INSERT
AS BEGIN
  NEW.ID = GEN_ID (LogIdGenerator, 1);
END;

Log Changes to the PRICE field

CREATE TRIGGER Log_Price_Upd FOR Articles
AFTER INSERT OR UPDATE
AS BEGIN

  IF (INSERTING) THEN
    INSERT INTO PRICE_LOGS (ART_ID, ACTION, DATETIME, NEW_PRICE) VALUES (NEW.ART_ID, 'I', 'NOW', NEW.PRICE);

  ELSE
    INSERT INTO PRICE_LOGS (ART_ID, ACTION, DATETIME, NEW_PRICE) VALUES (NEW.ART_ID, 'U', 'NOW', NEW.PRICE);

END;

Case insensitive searching

Suppose you have a table of employees and want to be able to search for employee last name, independent of case:

CREATE TABLE EMPLOYEES (

  EMPNO     INTEGER  NOT NULL  PRIMARY KEY,

  LASTNAME  VARCHAR (50),

  FIRSTNAME VARCHAR (50));

Now include a new field LASTNAME_UPPER which will contain the uppercased lastname. You will also need a collation so that the UPPER() function can work at all.

CREATE TABLE EMPLOYEES (

  EMPNO INTEGER NOT NULL PRIMARY KEY,

  LASTNAME       VARCHAR (50) COLLATE DE_DE,
  LASTNAME_UPPER VARCHAR (50) COLLATE DE_DE,

  FIRSTNAME      VARCHAR (50) COLLATE DE_DE);

Now you need a trigger which will fill the LASTNAME_UPPER field at every INSERT or UPDATE operation. There is no need for your application to fill LASTNAME_UPPER.

CREATE OR ALTER TRIGGER BIU_EMPLOYEES

FOR EMPLOYEES
BEFORE INSERT OR UPDATE AS

BEGIN

  NEW.LASTNAME_UPPER = UPPER (NEW.LASTNAME);

END;

Next you need an index on the LASTNAME_UPPER field so that searching is fast:

CREATE INDEX IDX_EMPL_LASTNAME ON EMPLOYEES (LASTNAME_UPPER);

Now you can search for employees using a normal SELECT and by using LASTNAME_UPPER instead of LASTNAME. Remember to uppercase the term you are searching for:

SELECT EMPNO FROM EMPLOYEES WHERE LASTNAME_UPPER = 'HEYMANN';

Stefan Heymann, 2004-09-05. Edited 2021-08-09.

This documentation is licensed under (choose your favorite): GPL, LGPL, CC, IDPL, GFDL, BSD, (did I forget one?)