TRIGGER

Table of Contents

CREATE TRIGGER
ALTER TRIGGER
CREATE OR ALTER TRIGGER
DROP TRIGGER
RECREATE TRIGGER

A trigger is a special type of stored procedure that is not called directly, instead being executed when a specified event occurs in the associated table or view. A trigger is specific to one and only one relation (table or view) and one phase in the timing of the event (BEFORE or AFTER). It can be specified to execute for one specific event (insert, update, delete) or for some combination of two or three of those events.

Another form of trigger—known as a “database trigger”—can be specified to fire in association with the start or end of a user session (connection) or a user transaction.

CREATE TRIGGER

Table of Contents

Statement Terminators
Relation Triggers (on Tables or Views)
Database Triggers

Used for: Creating a new trigger

Available in: DSQL, ESQL

Syntax:

CREATE TRIGGER trigname  {
  <relation_trigger_legacy> |
  <relation_trigger_sql2003> |
  <database_trigger> }
AS
	[<declarations>]
BEGIN
	[<PSQL_statements>]
END

<relation_trigger_legacy> ::=
  FOR {tablename | viewname}
  [ACTIVE | INACTIVE]
  {BEFORE | AFTER} <mutation_list>
  [POSITION number]

<relation_trigger_sql2003> ::=
  [ACTIVE | INACTIVE]
  {BEFORE | AFTER} <mutation_list>
  [POSITION number]
  ON {tablename | viewname}

<database_trigger> ::=
  [ACTIVE | INACTIVE] ON db_event [POSITION number]

<mutation_list> ::=
  <mutation> [OR <mutation> [OR <mutation>]]

<mutation> ::= { INSERT | UPDATE | DELETE }

<db_event> ::= {
  CONNECT |
  DISCONNECT |
  TRANSACTION START |
  TRANSACTION COMMIT |
  TRANSACTION ROLLBACK
}

<declarations> ::= {<declare_var> | <declare_cursor>};
  [{<declare_var> | <declare_cursor>}; …]

Table 5.21. CREATE TRIGGER Statement Parameters

Parameter	Description
trigname	Trigger name consisting of up to 31 characters. It must be unique among all trigger names in the database.
relation_trigger_legacy	Legacy style of trigger declaration for a relation trigger
relation_trigger_sql2003	Relation trigger declaration compliant with the SQL:2003 standard
database_trigger	Database trigger declaration
tablename	Name of the table with which the relation trigger is associated
viewname	Name of the view with which the relation trigger is associated
mutation_list	List of relation (table \| view) events
number	Position of the trigger in the firing order. From 0 to 32,767
db_event	Connection or transaction event
declarations	Section for declaring local variables and named cursors
declare_var	Local variable declaration
declare_cursor	Named cursor declaration
PSQL_statements	Statements in Firebird's programming language (PSQL)

The CREATE TRIGGER statement is used for creating a new trigger. A trigger can be created either for a relation (table | view) event (or a combination of events), or for a database event.

CREATE TRIGGER, along with its associates ALTER TRIGGER, CREATE OR ALTER TRIGGER and RECREATE TRIGGER, is a compound statement, consisting of a header and a body. The header specifies the name of the trigger, the name of the relation (for a relation trigger), the phase of the trigger and the event[s] it applies to. The body consists of optional declarations of local variables and named cursors followed by one or more statements, or blocks of statements, all enclosed in an outer block that begins with the keyword BEGIN and ends with the keyword END. Declarations and embedded statements are terminated with semi-colons (;).

The name of the trigger must be unique among all trigger names.

Statement Terminators

Some SQL statement editors—specifically the isql utility that comes with Firebird and possibly some third-party editors—employ an internal convention that requires all statements to be terminated with a semi-colon. This creates a conflict with PSQL syntax when coding in these environments. If you are unacquainted with this problem and its solution, please study the details in the PSQL chapter in the section entitled Switching the Terminator in isql.

Relation Triggers (on Tables or Views)

Relation triggers are executed at the row (record) level every time the row image changes. A trigger can be either ACTIVE or INACTIVE. Only active triggers are executed. Triggers are created ACTIVE by default.

Forms of Declaration

Firebird supports two forms of declaration for relation triggers:

The original, legacy syntax
The SQL:2003 standard-compliant form (recommended)

The SQL:2003 standard-compliant form is the recommended one.

A relation trigger specifies—among other things—a phase and one or more events.

Phase

Phase concerns the timing of the trigger with regard to the change-of-state event in the row of data:

A BEFORE trigger is fired before the specified database operation (insert, update or delete) is carried out
An AFTER trigger is fired after the database operation has been completed

Row Events

A relation trigger definition specifies at least one of the DML operations INSERT, UPDATE and DELETE, to indicate one or more events on which the trigger should fire. If multiple operations are specified, they must be separated by the keyword OR. No operation may occur more than once.

Within the statement block, the Boolean context variables INSERTING, UPDATING and DELETING can be used to test which operation is currently executing.

Firing Order of Triggers

The keyword POSITION allows an optional execution order (“firing order”) to be specified for a series of triggers that have the same phase and event as their target. The default position is 0. If no positions are specified, or if several triggers have a single position number, the triggers will be executed in the alphabetical order of their names.

Variable Declarations

The optional declarations section beneath the keyword AS in the header of the trigger is for defining variables and named cursors that are local to the trigger. For more details, see DECLARE VARIABLE and DECLARE CURSOR in the Procedural SQL chapter.

The Trigger Body

The local declarations (if any) are the final part of a trigger's header section. The trigger body follows, where one or more blocks of PSQL statements are enclosed in a structure that starts with the keyword BEGIN and terminates with the keyword END.

Only the owner of the view or table and administrators have the authority to use CREATE TRIGGER.

Examples of CREATE TRIGGER for Tables and Views:

Creating a trigger in the “legacy” form, firing before the event of inserting a new record into the CUSTOMER table occurs.

CREATE TRIGGER SET_CUST_NO FOR CUSTOMER
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.CUST_NO IS NULL) THEN
    NEW.CUST_NO = GEN_ID(CUST_NO_GEN, 1);
END

Creating a trigger firing before the event of inserting a new record into the CUSTOMER table in the SQL:2003 standard-compliant form.

CREATE TRIGGER set_cust_no
ACTIVE BEFORE INSERT POSITION 0 ON customer
AS
BEGIN
  IF (NEW.cust_no IS NULL) THEN
    NEW.cust_no = GEN_ID(cust_no_gen, 1);
END

Creating a trigger that will file after either inserting, updating or deleting a record in the CUSTOMER table.

CREATE TRIGGER TR_CUST_LOG
ACTIVE AFTER INSERT OR UPDATE OR DELETE POSITION 10
ON CUSTOMER
AS
BEGIN
  INSERT INTO CHANGE_LOG (LOG_ID,
                          ID_TABLE,
                          TABLE_NAME,
                          MUTATION)
  VALUES (NEXT VALUE FOR SEQ_CHANGE_LOG,
          OLD.CUST_NO,
          'CUSTOMER',
          CASE
            WHEN INSERTING THEN 'INSERT'
            WHEN UPDATING  THEN 'UPDATE'
            WHEN DELETING  THEN 'DELETE'
          END);
END

Database Triggers

Triggers can be defined to fire upon “database events”, which really refers to a mixture of events that act across the scope of a session (connection) and events that act across the scope of an individual transaction:

CONNECT

DISCONNECT

TRANSACTION START

TRANSACTION COMMIT

TRANSACTION ROLLBACK

Execution of Database Triggers and Exception Handling

CONNECT and DISCONNECT triggers are executed in a transaction created specifically for this purpose. If all goes well, the transaction is committed. Uncaught exceptions cause the transaction to roll back, and

for a CONNECT trigger, the connection is then broken and the exception is returned to the client
for a DISCONNECT trigger, exceptions are not reported. The connection is broken as intended

TRANSACTION triggers are executed within the transaction whose start, commit or rollback evokes them. The action taken after an uncaught exception depends on the event:

In a TRANSACTION START trigger, the exception is reported to the client and the transaction is rolled back
In a TRANSACTION COMMIT trigger, the exception is reported, the trigger's actions so far are undone and the commit is cancelled
In a TRANSACTION ROLLBACK trigger, the exception is not reported and the transaction is rolled back as intended.

Traps

Obviously there is no direct way of knowing if a DISCONNECT or TRANSACTION ROLLBACK trigger caused an exception. It also follows that the connection to the database cannot happen if a CONNECT trigger causes an exception and a transaction cannot start if a TRANSACTION START trigger causes one, either. Both phenomena effectively lock you out of your database until you get in there with database triggers suppressed and fix the bad code.

Trigger Suppression

Some Firebird command-line tools have been supplied with switches that an administrator can use to suppress the automatic firing of database triggers. So far, they are:

gbak -nodbtriggers
isql -nodbtriggers
nbackup -T

Two-phase Commit

In a two-phase commit scenario, TRANSACTION COMMIT triggers fire in the prepare phase, not at the commit.

Some Caveats

The use of the IN AUTONOMOUS TRANSACTION DO statement in the database event triggers related to transactions (TRANSACTION START, TRANSACTION ROLLBACK, TRANSACTION COMMIT) may cause the autonomous transaction to enter an infinite loop
The DISCONNECT and TRANSACTION ROLLBACK event triggers will not be executed when clients are disconnected via monitoring tables (DELETE FROM MON$ATTACHMENTS)

Only the database owner and administrators have the authority to create database triggers.

Examples of CREATE TRIGGER for “Database Triggers”:

Creating a trigger for the event of connecting to the database that logs users logging into the system. The trigger is created as inactive.

CREATE TRIGGER tr_log_connect
INACTIVE ON CONNECT POSITION 0
AS
BEGIN
  INSERT INTO LOG_CONNECT (ID,
                           USERNAME,
                           ATIME)
  VALUES (NEXT VALUE FOR SEQ_LOG_CONNECT,
          CURRENT_USER,
          CURRENT_TIMESTAMP);
END

Creating a trigger for the event of connecting to the database that does not permit any users, except for SYSDBA, to log in during off hours.

CREATE EXCEPTION E_INCORRECT_WORKTIME 'The working day has not started yet.';

CREATE TRIGGER TR_LIMIT_WORKTIME ACTIVE
ON CONNECT POSITION 1
AS
BEGIN
  IF ((CURRENT_USER <> 'SYSDBA') AND
      NOT (CURRENT_TIME BETWEEN time '9:00' AND time '17:00')) THEN
     EXCEPTION E_INCORRECT_WORKTIME;
END

ALTER TRIGGER

Table of Contents

Permitted Changes to Triggers

Used for: Modifying and deactivating an existing trigger

Available in: DSQL, ESQL

Syntax:

ALTER TRIGGER trigname
[ACTIVE | INACTIVE]
[{BEFORE | AFTER} <mutation_list> | ON db_event]
[POSITION number]
[
 AS
  [<declarations>]
	 BEGIN
    [<PSQL_statements>]
	 END
]

<mutation_list> ::=
  <mutation> [OR <mutation> [OR <mutation>]]

<mutation> ::= { INSERT | UPDATE | DELETE }

<db_event> ::= {
  CONNECT |
  DISCONNECT |
  TRANSACTION START |
  TRANSACTION COMMIT |
  TRANSACTION ROLLBACK
}

<declarations> ::= {<declare_var> | <declare_cursor>};
	[{<declare_var> | <declare_cursor>}; …]

Table 5.22. ALTER TRIGGER Statement Parameters

Parameter	Description
trigname	Name of an existing trigger
mutation_list	List of relation (table \| view) events
number	Position of the trigger in the firing order. From 0 to 32,767
declarations	Section for declaring local variables and named cursors
declare_var	Local variable declaration
declare_cursor	Named cursor declaration
PSQL_statements	Statements in Firebird's programming language (PSQL)

The ALTER TRIGGER statement allows certain changes to the header and body of a trigger.

Permitted Changes to Triggers

Status (ACTIVE | INACTIVE)
Phase (BEFORE | AFTER)
Events; but relation trigger events cannot be changed to database trigger events, nor vice versa
Position in the firing order
Modifications to code in the trigger body

If some element was not specified, it remains unchanged.

	Reminders
The BEFORE keyword directs that the trigger be executed before the associated event occurs; the AFTER keyword directs that it be executed after the event. More than one relation event—INSERT, UPDATE, DELETE—can be covered in a single trigger. The events should be separated with the keyword OR. No event should be mentioned more than once. The keyword POSITION allows an optional execution order (“firing order”) to be specified for a series of triggers that have the same phase and event as their target. The default position is 0. If no positions are specified, or if several triggers have a single position number, the triggers will be executed in the alphabetical order of their names.

Reminders

The BEFORE keyword directs that the trigger be executed before the associated event occurs; the AFTER keyword directs that it be executed after the event.

More than one relation event—INSERT, UPDATE, DELETE—can be covered in a single trigger. The events should be separated with the keyword OR. No event should be mentioned more than once.

Administrators and the following users have the authority to use ALTER TRIGGER:

For relation triggers, the owner of the table
For database triggers, the owner of the database

Examples using ALTER TRIGGER:

Deactivating the set_cust_no trigger (switching it to the inactive status).
```
ALTER TRIGGER set_cust_no INACTIVE;
            
```

Changing the firing order position of the set_cust_no trigger.

ALTER TRIGGER set_cust_no POSITION 14;

Switching the TR_CUST_LOG trigger to the inactive status and modifying the list of events.
```
ALTER TRIGGER TR_CUST_LOG
INACTIVE AFTER INSERT OR UPDATE;
            
```

Switching the tr_log_connect trigger to the active status, changing its position and body.

ALTER TRIGGER tr_log_connect
ACTIVE POSITION 1
AS
BEGIN
  INSERT INTO LOG_CONNECT (ID,
                           USERNAME,
                           ROLENAME,
                           ATIME)
  VALUES (NEXT VALUE FOR SEQ_LOG_CONNECT,
          CURRENT_USER,
          CURRENT_ROLE,
          CURRENT_TIMESTAMP);
END

CREATE OR ALTER TRIGGER

Used for: Creating a new trigger or altering an existing trigger

Available in: DSQL

Syntax:

CREATE OR ALTER TRIGGER trigname  {
  <relation_trigger_legacy> |
  <relation_trigger_sql2003> |
  <database_trigger> }
AS
[<declarations>]
BEGIN
[<PSQL_statements>]
END

For the full detail of the syntax, see CREATE TRIGGER.

The CREATE OR ALTER TRIGGER statement creates a new trigger if it does not exist; otherwise it alters and recompiles it with the privileges intact and dependencies unaffected.

Example using CREATE OR ALTER TRIGGER: Creating a new trigger if it does not exist or altering it if it does exist.

CREATE OR ALTER TRIGGER set_cust_no
ACTIVE BEFORE INSERT POSITION 0 ON customer
AS
BEGIN
  IF (NEW.cust_no IS NULL) THEN
    NEW.cust_no = GEN_ID(cust_no_gen, 1);
END

DROP TRIGGER

Used for: Deleting an existing trigger

Available in: DSQL, ESQL

Syntax:

DROP TRIGGER trigname

Table 5.23. DROP TRIGGER Statement Parameter

Parameter	Description
trigname	Trigger name

The DROP TRIGGER statement deletes an existing trigger.

Administrators and the following users have the authority to use DROP TRIGGER:

For relation triggers, the owner of the table
For database triggers, the owner of the database

Example using DROP TRIGGER: Deleting the set_cust_no trigger.

DROP TRIGGER set_cust_no;

RECREATE TRIGGER

Used for: Creating a new trigger or recreating an existing trigger

Available in: DSQL

Syntax:

RECREATE TRIGGER trigname {
  <relation_trigger_legacy> | 
  <relation_trigger_sql2003> | 
  <database_trigger> }
AS
[<declarations>]
BEGIN
[<PSQL_statements>]
END

For the full detail of the syntax, see CREATE TRIGGER.

The RECREATE TRIGGER statement creates a new trigger if no trigger with the specified name exists; otherwise the RECREATE TRIGGER statement tries to delete the existing trigger and create a new one. The operation will fail on COMMIT if the trigger dependencies.

	Warning
	Be aware that dependency errors are not detected until the COMMIT phase of this operation.

Example using RECREATE TRIGGER: Creating or recreating the set_cust_no trigger.

RECREATE TRIGGER set_cust_no
ACTIVE BEFORE INSERT POSITION 0 ON customer
AS
BEGIN
  IF (NEW.cust_no IS NULL) THEN
    NEW.cust_no = GEN_ID(cust_no_gen, 1);
END