Trigger definitions

Trigger definitions mk Sun, 03/28/2010 - 23:32

Triggers are a convenient programming abstraction. They are activated at transaction commit based on updates to the base tables.

trigger_def: CREATE TRIGGER qname trigger_action_time trigger_event ON ident opt_referencing_list triggered_action

trigger_action_time: BEFORE | AFTER

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

opt_referencing_list: [ REFERENCING 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: /* default for each statement */ | FOR EACH ROW | FOR EACH STATEMENT

   | BEGIN ATOMIC trigger_procedure_statement_list END

trigger_procedure_statement: transaction_statement | update_statement | grant | revoke | declare_statement | set_statement | control_statement | select_statement_single_row

Example The following example provides a glimpse of their functionality:

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
for each statement
when id >0 insert into t1 values(4, 'update_when_statement_true');

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.