5.7TRIGGER

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.

5.7.1CREATE TRIGGER

Creates a trigger

Available inDSQL, 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 5.21CREATE TRIGGER Statement Parameters
ParameterDescription

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.

5.7.1.1Statement 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.

5.7.1.2SQL 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.

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.

5.7.1.3The Trigger Body

The trigger body is either a PSQL body, or an external UDR module body.

See The Module Body in the PSQL chapter for details.

5.7.1.4DML Triggers (on Tables or Views)

DML — or relation — triggers are executed at the row (record) level, every time a row is changed. A trigger can be either ACTIVE or INACTIVE. Only active triggers are executed. Triggers are created ACTIVE by default.

5.7.1.4.1Who Can Create a DML Trigger?

DML triggers can be created by:

  • Administrators

  • The owner of the table (or view)

  • Users with — for a table — the ALTER ANY TABLE, or — for a view — ALTER ANY VIEW privilege

5.7.1.4.2Forms of Declaration

Firebird supports two forms of declaration for relation triggers:

  • The legacy syntax

  • The SQL standard-compliant form (recommended)

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

5.7.1.4.3Phase

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

5.7.1.4.4Row 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.

5.7.1.4.5Firing 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 multiple triggers have the same position and phase, those triggers will be executed in an undefined order, while respecting the total order by position and phase.

5.7.1.4.6Examples of CREATE TRIGGER for Tables and Views
  1. 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
    
  2. Creating a trigger firing before the event of inserting a new record into the CUSTOMER table in the SQL standard-compliant form.

      |CREATE TRIGGER set_cust_no
      |ACTIVE BEFORE INSERT ON customer POSITION 0
      |AS
      |BEGIN
      |  IF (NEW.cust_no IS NULL) THEN
      |    NEW.cust_no = GEN_ID(cust_no_gen, 1);
      |END
    
  3. 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
       |ON CUSTOMER POSITION 10
       |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
    
  4. With DEFINER set for trigger tr_ins, user US needs only the INSERT privilege on tr. If it were set for INVOKER, either the user or the trigger would also need the INSERT privilege on table t.

       |create table tr (i integer);
       |create table t (i integer);
       |set term ^;
       |create trigger tr_ins for tr after insert SQL SECURITY DEFINER
       |as
       |begin
       |  insert into t values (NEW.i);
       |end^
       |set term ;^
       |grant insert on table tr to user us;
       | 
       |commit;
       | 
       |connect 'localhost:/tmp/29.fdb' user us password 'pas';
       |insert into tr values(2);
    

    The result would be the same if SQL SECURITY DEFINER were specified for table TR:

       |create table tr (i integer) SQL SECURITY DEFINER;
       |create table t (i integer);
       |set term ^;
       |create trigger tr_ins for tr after insert
       |as
       |begin
       |  insert into t values (NEW.i);
       |end^
       |set term ;^
       |grant insert on table tr to user us;
       | 
       |commit;
       | 
       |connect 'localhost:/tmp/29.fdb' user us password 'pas';
       |insert into tr values(2);
    

5.7.1.5Database Triggers

Triggers can be defined to fire upon database events; 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

Section 5.7.1.6, “DDL Triggers” are a subtype of database triggers, covered in a separate section.

5.7.1.5.1Who Can Create a Database Trigger?

Database triggers can be created by:

5.7.1.5.2Execution of Database Triggers and Exception Handling

CONNECT and DISCONNECT triggers are executed in a transaction created specifically for this purpose. This transaction uses the default isolation level, i.e. snapshot (concurrency), write and wait. 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.

5.7.1.5.2.1Traps

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.

Suppressing Database Triggers

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
5.7.1.5.2.2Two-phase Commit

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

5.7.1.5.2.3Some Caveats
  1. 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

  2. The DISCONNECT and TRANSACTION ROLLBACK event triggers will not be executed when clients are disconnected via monitoring tables (DELETE FROM MON$ATTACHMENTS)

5.7.1.5.3Examples of CREATE TRIGGER for Database Triggers
  1. 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
    
  2. 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
    

5.7.1.6DDL Triggers

DDL triggers allow restrictions to be placed on users who attempt to create, alter or drop a DDL object. Their other purposes is to keep a metadata change log.

DDL triggers fire on specified metadata changes events in a specified phase. BEFORE triggers run before changes to system tables. AFTER triggers run after changes in system tables.

Important

The event type [BEFORE | AFTER] of a DDL trigger cannot be changed.

In a sense, DDL triggers are a sub-type of database triggers.

5.7.1.6.1Who Can Create a DDL Trigger?

DDL triggers can be created by:

5.7.1.6.2Suppressing DDL Triggers

A DDL trigger is a type of database trigger. See the section called “Suppressing Database Triggers” how to suppress DDL — and database — triggers.

5.7.1.6.3Examples of DDL Triggers
  1. Here is how you might use a DDL trigger to enforce a consistent naming scheme, in this case, stored procedure names should begin with the prefix SP_:

       |set auto on;
       |create exception e_invalid_sp_name 'Invalid SP name (should start with SP_)';
       | 
       |set term !;
       | 
       |create trigger trig_ddl_sp before CREATE PROCEDURE
       |as
       |begin
       |  if (rdb$get_context('DDL_TRIGGER', 'OBJECT_NAME') not starting 'SP_') then
       |    exception e_invalid_sp_name;
       |end!
    

    Test

       |create procedure sp_test
       |as
       |begin
       |end!
       | 
       |create procedure test
       |as
       |begin
       |end!
       | 
       |-- The last command raises this exception and procedure TEST is not created
       |-- Statement failed, SQLSTATE = 42000
       |-- exception 1
       |-- -E_INVALID_SP_NAME
       |-- -Invalid SP name (should start with SP_)
       |-- -At trigger 'TRIG_DDL_SP' line: 4, col: 5
       | 
       |set term ;!
    
  2. Implement custom DDL security, in this case restricting the running of DDL commands to certain users:

       |create exception e_access_denied 'Access denied';
       | 
       |set term !;
       | 
       |create trigger trig_ddl before any ddl statement
       |as
       |begin
       |  if (current_user <> 'SUPER_USER') then
       |    exception e_access_denied;
       |end!
    

    Test

       |create procedure sp_test
       |as
       |begin
       |end!
       | 
       |-- The last command raises this exception and procedure SP_TEST is not created
       |-- Statement failed, SQLSTATE = 42000
       |-- exception 1
       |-- -E_ACCESS_DENIED
       |-- -Access denied
       |-- -At trigger 'TRIG_DDL' line: 4, col: 5
       | 
       |set term ;!
    
    Note

    Firebird has privileges for executing DDL statements, so writing a DDL trigger for this should be a last resort, if the same effect cannot be achieved using privileges.

  3. Use a trigger to log DDL actions and attempts:

       |create sequence ddl_seq;
       | 
       |create table ddl_log (
       |  id bigint not null primary key,
       |  moment timestamp not null,
       |  user_name varchar(63) not null,
       |  event_type varchar(25) not null,
       |  object_type varchar(25) not null,
       |  ddl_event varchar(25) not null,
       |  object_name varchar(63) not null,
       |  sql_text blob sub_type text not null,
       |  ok char(1) not null
       |);
       | 
       |set term !;
       | 
       |create trigger trig_ddl_log_before before any ddl statement
       |as
       |  declare id type of column ddl_log.id;
       |begin
       |  -- We do the changes in an AUTONOMOUS TRANSACTION, so if an exception happens
       |  -- and the command didn't run, the log will survive.
       |  in autonomous transaction do
       |  begin
       |    insert into ddl_log (id, moment, user_name, event_type, object_type,
       |                         ddl_event, object_name, sql_text, ok)
       |      values (next value for ddl_seq, current_timestamp, current_user,
       |              rdb$get_context('DDL_TRIGGER', 'EVENT_TYPE'),
       |              rdb$get_context('DDL_TRIGGER', 'OBJECT_TYPE'),
       |              rdb$get_context('DDL_TRIGGER', 'DDL_EVENT'),
       |              rdb$get_context('DDL_TRIGGER', 'OBJECT_NAME'),
       |              rdb$get_context('DDL_TRIGGER', 'SQL_TEXT'),
       |              'N')
       |      returning id into id;
       |    rdb$set_context('USER_SESSION', 'trig_ddl_log_id', id);
       |  end
       |end!
    

    The above trigger will fire for this DDL command. It’s a good idea to use -nodbtriggers when working with them!

       |create trigger trig_ddl_log_after after any ddl statement
       |as
       |begin
       |  -- Here we need an AUTONOMOUS TRANSACTION because the original transaction
       |  -- will not see the record inserted on the BEFORE trigger autonomous
       |  -- transaction if user transaction is not READ COMMITTED.
       |  in autonomous transaction do
       |     update ddl_log set ok = 'Y'
       |     where id = rdb$get_context('USER_SESSION', 'trig_ddl_log_id');
       |end!
       | 
       |commit!
       | 
       |set term ;!
       | 
       |-- Delete the record about trig_ddl_log_after creation.
       |delete from ddl_log;
       |commit;
    

    Test

       |-- This will be logged one time
       |-- (as T1 did not exist, RECREATE acts as CREATE) with OK = Y.
       |recreate table t1 (
       |  n1 integer,
       |  n2 integer
       |);
       | 
       |-- This will fail as T1 already exists, so OK will be N.
       |create table t1 (
       |  n1 integer,
       |  n2 integer
       |);
       | 
       |-- T2 does not exist. There will be no log.
       |drop table t2;
       | 
       |-- This will be logged twice
       |-- (as T1 exists, RECREATE acts as DROP and CREATE) with OK = Y.
       |recreate table t1 (
       |  n integer
       |);
       | 
       |commit;
    
       |select id, ddl_event, object_name, sql_text, ok
       |  from ddl_log order by id;
       | 
       | ID DDL_EVENT                 OBJECT_NAME                      SQL_TEXT OK
       |=== ========================= ======================= ================= ======
       |  2 CREATE TABLE              T1                                   80:3 Y
       |====================================================
       |SQL_TEXT:
       |recreate table t1 (
       |    n1 integer,
       |    n2 integer
       |)
       |====================================================
       |  3 CREATE TABLE              T1                                   80:2 N
       |====================================================
       |SQL_TEXT:
       |create table t1 (
       |    n1 integer,
       |    n2 integer
       |)
       |====================================================
       |  4 DROP TABLE                T1                                   80:6 Y
       |====================================================
       |SQL_TEXT:
       |recreate table t1 (
       |    n integer
       |)
       |====================================================
       |  5 CREATE TABLE              T1                                   80:9 Y
       |====================================================
       |SQL_TEXT:
       |recreate table t1 (
       |    n integer
       |)
       |====================================================
    

See alsoSection 5.7.2, “ALTER TRIGGER, Section 5.7.3, “CREATE OR ALTER TRIGGER, Section 5.7.5, “RECREATE TRIGGER, Section 5.7.4, “DROP TRIGGER, DDL Triggers in Chapter Procedural SQL (PSQL) Statements

5.7.2ALTER TRIGGER

Alters a trigger

Available inDSQL, ESQL

Syntax

   |ALTER TRIGGER trigname
   |  [ACTIVE | INACTIVE]
   |  [{BEFORE | AFTER} <mutation_list>]
   |  [POSITION number]
   |  [{<psql_trigger> | <external-module-body>}]
   | 
   |<psql_trigger> ::=
   |  [<sql_security>]
   |  [<psql-module-body>]
   | 
   |<sql_security> ::=
   |    SQL SECURITY {INVOKER | DEFINER}
   |  | DROP SQL SECURITY
   | 
   |!! See syntax of CREATE TRIGGER for further rules !!

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

5.7.2.1Permitted Changes to Triggers

  • Status (ACTIVE | INACTIVE)

  • Phase (BEFORE | AFTER) (of DML triggers)

  • Events (of DML triggers)

  • Position in the firing order

  • Modifications to code in the trigger body

If an element is not specified, it remains unchanged.

Note

A DML trigger cannot be changed to a database or DDL trigger.

It is not possible to change the event(s) or phase of a database or DDL trigger.

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 DML 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 multiple triggers have the same position and phase, those triggers will be executed in an undefined order, while respecting the total order by position and phase.

5.7.2.2Who Can Alter a Trigger?

DML triggers can be altered by:

  • Administrators

  • The owner of the table (or view)

  • Users with — for a table — the ALTER ANY TABLE, or — for a view — ALTER ANY VIEW privilege

Database and DDL triggers can be altered by:

5.7.2.3Examples using ALTER TRIGGER

  1. Deactivating the set_cust_no trigger (switching it to the inactive status).

      |ALTER TRIGGER set_cust_no INACTIVE;
    
  2. Changing the firing order position of the set_cust_no trigger.

      |ALTER TRIGGER set_cust_no POSITION 14;
    
  3. 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;
    
  4. 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
    

See alsoSection 5.7.1, “CREATE TRIGGER, Section 5.7.3, “CREATE OR ALTER TRIGGER, Section 5.7.5, “RECREATE TRIGGER, Section 5.7.4, “DROP TRIGGER

5.7.3CREATE OR ALTER TRIGGER

Creates a trigger if it doesn’t exist, or alters a trigger

Available inDSQL

Syntax

  |CREATE OR ALTER TRIGGER trigname
  |  { <relation_trigger_legacy>
  |  | <relation_trigger_sql>
  |  | <database_trigger>
  |  | <ddl_trigger> }
  |  {<psql_trigger> | <external-module-body>}
  | 
  |!! See syntax of CREATE TRIGGER for further rules !!

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.

5.7.3.1Example of 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 ON customer POSITION 0
  |AS
  |BEGIN
  |  IF (NEW.cust_no IS NULL) THEN
  |    NEW.cust_no = GEN_ID(cust_no_gen, 1);
  |END

See alsoSection 5.7.1, “CREATE TRIGGER, Section 5.7.2, “ALTER TRIGGER, Section 5.7.5, “RECREATE TRIGGER

5.7.4DROP TRIGGER

Drops a trigger

Available inDSQL, ESQL

Syntax

  |DROP TRIGGER trigname

Table 5.22DROP TRIGGER Statement Parameter
ParameterDescription

trigname

Trigger name

The DROP TRIGGER statement drops (deletes) an existing trigger.

5.7.4.1Who Can Drop a Trigger?

DML triggers can be dropped by:

  • Administrators

  • The owner of the table (or view)

  • Users with — for a table — the ALTER ANY TABLE, or — for a view — ALTER ANY VIEW privilege

Database and DDL triggers can be dropped by:

5.7.4.2Example of DROP TRIGGER

Deleting the set_cust_no trigger

  |DROP TRIGGER set_cust_no;

See alsoSection 5.7.1, “CREATE TRIGGER, Section 5.7.5, “RECREATE TRIGGER

5.7.5RECREATE TRIGGER

Drops a trigger if it exists, and creates a trigger

Available inDSQL

Syntax

  |RECREATE TRIGGER trigname
  |  { <relation_trigger_legacy>
  |  | <relation_trigger_sql>
  |  | <database_trigger>
  |  | <ddl_trigger> }
  |  {<psql_trigger> | <external-module-body>}
  | 
  |!! See syntax of CREATE TRIGGER for further rules !!

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

🛑
Warning

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

5.7.5.1Example of RECREATE TRIGGER

Creating or recreating the set_cust_no trigger.

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

See alsoSection 5.7.1, “CREATE TRIGGER, Section 5.7.4, “DROP TRIGGER, Section 5.7.3, “CREATE OR ALTER TRIGGER