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');