Trigger Definitions

Triggers are a convenient programming abstraction activated at transaction commit based on updates to the base tables. All trigger definitions are considered together at the transaction commit. There is no a priori defined order in which they run. Each may in turn activate new triggers, but each trigger definition is also executed only once per transaction commit.

trigger_def:
     CREATE [ OR REPLACE ] TRIGGER ident trigger_action_time trigger_event ON qname  opt_referencing_list  triggered_action
  |  CREATE [ OR REPLACE ] TRIGGER ident trigger_action_time  LOGIN  triggered_action

trigger_action_time: BEFORE | AFTER

trigger_event: INSERT | DELETE | TRUNCATE | UPDATE | UPDATE OF ident ','...

opt_referencing_list: [ REFERENCING old_or_new_values_alias ... ]

old_or_new_values_alias:
      OLD [ ROW ] [ AS ] ident
    | NEW [ ROW ] [ AS ] ident
    | OLD TABLE [ AS ] ident
    | NEW TABLE [ AS ] ident

triggered_action:
     opt_for_each [ WHEN '(' search_condition ')' ] triggered_statement

opt_for_each: [ FOR EACH ROW | FOR EACH STATEMENT ]

triggered_statement:
     trigger_procedure_statement
   | BEGIN ATOMIC trigger_procedure_statement_list END

trigger_procedure_statement_list:
   [ trigger_procedure_statement  ';'  trigger_procedure_statement_list ]

trigger_procedure_statement:
     transaction_statement
   | update_statement
   | grant
   | revoke
   | declare_statement
   | set_statement
   | control_statement
   | call_procedure_statement
   | call_statement
   | analyze_statement
   | select_statement_single_row

Note: FOR EACH STATEMENT is the default when not specified.

The option to specify a schema name qualifier for the trigger name has been removed. Previously the schema name of a full qualified trigger name already had to be the same as the schema name of the table.

Examples

create table t1 (id int, name varchar(1024));

--test FOR EACH STATEMENT (default one)
insert into t1 values(10, 'monetdb');
insert into t1 values(20, 'monet');

create trigger test5
after update on t1 referencing new row as new_row
for each statement
when (new_row.id > 0) insert into t1 values(4, 'update_when_statement_true');

select * from sys.triggers;

See also system table: sys.triggers