FirebirdSQL logo
  • Anglaiskeyboard_arrow_down
 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.

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 semicolon.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.

SQL Security

The SQL SECURITY clause specifies the security context for executing other routines or inserting into other tables.

By default, a trigger applies the SQL Security property defined on its table (or — if the table doesn’t have the SQL Security property set — the database default), but it can be overridden by specifying it explicitly.

Note

If the SQL Security property is changed for the table, triggers that do not have an explicit SQL Security property will not see the effect of the change until the next time the trigger is loaded into the metadata cache.

See also SQL Security in chapter Security.