www.destructor.de

About | Contact | Impressum


Home |  Code |  Articles |  Misc |  x
Firebird |  Talks |  Character Sets |  x
General |  1.0 |  1.5 |  2.0 |  2.1 |  2.5 |  3.0 |  x

Triggers in Firebird

General

Advantages

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>

Differences to Stored Procedures

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

Triggers and Transactions

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

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