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 qname trigger_action_time trigger_event [ ON ident ] opt_referencing_list triggered_action trigger_action_time: BEFORE | AFTER trigger_event: INSERT | DELETE | TRUNCATE | UPDATE | UPDATE OF ident ','... | LOGIN 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: update_statement | declare_statement | set_statement | control_statement | select_statement_single_row trigger_procedure_statement_list: [ trigger_procedure_statement ';' trigger_procedure_statement_list ]
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.
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');