FirebirdSQL logo
 VIEWPROCEDURE 

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

Two other forms of trigger exist:

  1. a “database trigger” can be specified to fire at the start or end of a user session (connection) or a user transaction.

  2. a “DDL trigger” can be specified to fire before or after execution of one or more types of DDL statements.

CREATE TRIGGER

Creates a trigger

Available in

DSQL, ESQL

Syntax
CREATE TRIGGER trigname
  { <relation_trigger_legacy>
  | <relation_trigger_sql>
  | <database_trigger>
  | <ddl_trigger> }
  {<psql_trigger> | <external-module-body>}

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

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

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

<ddl_trigger> ::=
  [ACTIVE | INACTIVE]
  {BEFORE | AFTER} <ddl_event>
  [POSITION number]

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

<mutation> ::= INSERT | UPDATE | DELETE

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

<ddl_event> ::=
    ANY DDL STATEMENT
  | <ddl_event_item> [{OR <ddl_event_item>} ...]

<ddl_event_item> ::=
    {CREATE | ALTER | DROP} TABLE
  | {CREATE | ALTER | DROP} PROCEDURE
  | {CREATE | ALTER | DROP} FUNCTION
  | {CREATE | ALTER | DROP} TRIGGER
  | {CREATE | ALTER | DROP} EXCEPTION
  | {CREATE | ALTER | DROP} VIEW
  | {CREATE | ALTER | DROP} DOMAIN
  | {CREATE | ALTER | DROP} ROLE
  | {CREATE | ALTER | DROP} SEQUENCE
  | {CREATE | ALTER | DROP} USER
  | {CREATE | ALTER | DROP} INDEX
  | {CREATE | DROP} COLLATION
  | ALTER CHARACTER SET
  | {CREATE | ALTER | DROP} PACKAGE
  | {CREATE | DROP} PACKAGE BODY
  | {CREATE | ALTER | DROP} MAPPING

<psql_trigger> ::=
  [SQL SECURITY {INVOKER | DEFINER}]
  <psql-module-body>

<psql-module-body> ::=
  !! See Syntax of Module Body !!

<external-module-body> ::=
  !! See Syntax of Module Body !!
Table 1. CREATE TRIGGER Statement Parameters
Parameter Description

trigname

Trigger name.The maximum length is 63 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_sql

Relation trigger declaration compliant with the SQL 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

ddl_event

List of metadata change events

ddl_event_item

One of the metadata change events

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 relation events), for a database event, or for a DDL 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 DML trigger), the phase of the trigger, the event(s) it applies to, and the position to determine an order between triggers.

The trigger 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 semicolons (‘;’).

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