Statement level time travel
1.8.1.15 CREATE TRIGGER
Syntax
CREATE TRIGGER <trigger_name> <trigger_action_time> <trigger_event_list>
ON <subject_table_name> [REFERENCING <transition_list>]
[<for_each_row>]
BEGIN
[<trigger_decl_list>]
[<proc_handler_list>]
<trigger_stmt_list>
END
Syntax Elements
<trigger_name> ::= [<schema_name>.]<identifier>
<schema_name> ::= <identifier>
The name of the trigger to be created, with optional schema name.
<subject_table_name> ::= <identifier>
The table the trigger is defined on.
For more information on identifiers, see Identifiers.
<trigger_action_time> ::= BEFORE | AFTER When trigger action should occur.
BEFORE
Specifies that the trigger will be executed before an operation on the table.
AFTER
Specifies that the trigger will executed after an operation on the table.
<trigger_event_list> ::= <trigger_event> | <trigger_event_list> OR <trigger_event>
<trigger_event> ::= INSERT | DELETE | UPDATE The data modification command that will activate trigger action.
REFERENCING <transition_list>
When a trigger transition variable is declared, the trigger can access records that are being changed by the DML executed by the trigger. For more information see Transition variable and Transition table.
<transition_list> ::= <transition> [{, <transition>}...]
One or more transition list entries.
<transition> ::= <transition_variable> | <transition_table>
A transition variable or table variable.
<transition_variable> ::= {OLD | NEW} ROW [AS] <trans_var_name>
<trans_var_name> ::= <identifier>
During row level trigger execution, <trans_var_name>.<column_name> represents a single record from the corresponding column that is being changed by the DML. <column_name> is the target tables column name. Only a ROW trigger can use a transition variable.
<transition_table> ::= {OLD | NEW} TABLE [AS] <trans_tab_name>
<trans_tab_name> ::= <identifier>
During statement level trigger execution, <trans_tab_name> represent records being changed by the trigger DML as a table variable. Only statement trigger can use a transition table.
OLD
You can access the old row of the DML in the trigger. This is the row that will be replaced by an update, or a deleted old row. UPDATE triggers and DELETE triggers can have OLD ROW transition variables or OLD TABLE transition table variables.
NEW
You can access the new record of the DML in the trigger. This is the row that will be inserted, or the new updated row.
UPDATE triggers and INSERT triggers can have NEW ROW transition variables or NEW TABLE transition table variables.
<for_each_row> ::= FOR EACH { ROW | STATEMENT } Default: ROW
Defines that the trigger will be called in a row-wise or statement-wise fashion.
ROW
Specifies that a row level trigger will be used. This will be fired once for each row affected by the triggering event.
STATEMENT
Specifies that a statement level trigger will be used. This will be fired once for each triggering event.
<trigger_decl_list> ::= {DECLARE <trigger_decl>}...
Trigger declaration.
<trigger_decl> ::= <trigger_var_decl> | <trigger_condition_decl>
You can declare trigger variables or trigger conditions.
Declared variable can be used scalar value assignment or referenced in a trigger SQL statement.
<trigger_var_decl> ::= <var_name> [CONSTANT] <data_type> [<not_null>]
[<trigger_default_assign>] ; Trigger variable declaration.
<var_name> ::= <identifier>
The identifier of the trigger variable.
CONSTANT
When you specify the CONSTANT keyword, you cannot change the variable during trigger execution.
<data_type> ::= DATE | TIME | SECONDDATE | TIMESTAMP | TINYINT | SMALLINT | INTEGER | BIGINT | SMALLDECIMAL | DECIMAL | REAL | DOUBLE
| VARCHAR | NVARCHAR | ALPHANUM | SHORTTEXT | VARBINARY | BLOB | CLOB | NCLOB | TEXT
The datatype of the trigger variable. For more information on data types, see Data Types.
<not_null> ::= NOT NULL Not null condition.
<trigger_default_assign> ::= DEFAULT <expression> | := <expression>
The default value of the trigger variable. For more information on expression, see Expressions.
<trigger_condition_decl> ::= <condition_name> CONDITION ;
| <condition_name> CONDITION FOR <sql_error_code> ; Condition handler declaration.
<condition_name> ::= <identifier>
A declared condition name that you can reference in an exception handler.
<sql_error_code> ::= SQL_ERROR_CODE <int_const>
Error code specification for exception handling.
<proc_handler_list> ::= {<proc_handler>}...
<proc_handler> ::= DECLARE EXIT HANDLER FOR <proc_condition_value_list>
<trigger_stmt>
Declares exception handlers to catch SQL exceptions.
<proc_condition_value_list> ::= <proc_condition_value> [{, <proc_condition_value>}]
One or more condition values.
<proc_condition_value> ::= SQLEXCEPTION | SQLWARNING | <sql_error_code> | <condition_name>
You can use a specific error code number or condition name declared on a condition variable.
<trigger_stmt_list> ::= {<trigger_stmt>}...
<trigger_stmt> ::= <proc_block>
| <proc_assign>
The trigger body syntax is a subset of the procedure body syntax. For more information, see CREATE PROCEDURE.
Sections of your trigger procedures can be nested using BEGIN and END terminals.
<proc_assign> ::= <var_name> := <expression> ; <var_name> ::= <identifier>
Assign values to variables.
<proc_if> ::= IF <condition> THEN <trigger_stmt_list>
[<proc_elsif_list>]
[<proc_else>]
END IF ;
<proc_elsif_list> ::= ELSEIF <condition> THEN <trigger_stmt_list>
<proc_else> ::= ELSE <trigger_stmt_list>
You use IF - THEN - ELSE IF to control execution flow with conditionals.
<condition> ::= <condition> OR <condition>
| <condition> AND <condition>
| NOT <condition>
| ( <condition> ) | <predicate>
Specifies the conditions where the command should be performed. For more information on predicates, see Predicates.
<proc_loop> ::= LOOP <trigger_stmt_list> END LOOP ;
You use LOOP to repeatedly execute a set of trigger statements.
<proc_while> ::= WHILE <condition> DO <trigger_stmt_list> END WHILE ; You use WHILE to repeatedly call a set of trigger statements while a condition is true.
<proc_for> ::= FOR <column_name> IN [<reverse>] <expression> .. <expression>
DO <trigger_stmt_list>
END FOR ;
You use FOR - IN loops to iterate over a set of data.
<column_name> ::= <identifier>
The name of the column where the data iteration is to occur.
<reverse> ::= REVERSE
Specifies the results should be iterated over in reverse order.
<proc_foreach> ::= FOR <column_name> AS <column_name> [<open_param_list>] DO <trigger_stmt_list>
END FOR ;
You use FOR - EACH loops to iterate over all elements in a set of data.
<open_param_list> ::= ( <expr_list> )
<expr_list> ::= <expression>[{, <expression>}]
One or more input expressions to be iterated over.
<proc_signal> ::= SIGNAL <signal_value> [<set_signal_info>] ;
You use the SIGNAL statement to explicitly raise an exception from within your trigger procedures.
<proc_resignal> ::= RESIGNAL [<signal_value>] [<set_signal_info>] ;
You use the RESIGNAL statement to raise an exception on the action statement in an exception handler. If an error code is not specified, RESIGNAL will throw the caught exception.
<signal_value> ::= <signal_name> | <sql_error_code>
<signal_name> ::= <identifier>
<sql_error_code> ::= <unsigned_integer>
You can SIGNAL or RESIGNAL a signal name or an SQL error code.
<set_signal_info> ::= SET MESSAGE_TEXT = '<message_string>' <message_string> ::= <any_character>
You use SET MESSAGE_TEXT to deliver an error message to users when specified error is thrown during trigger execution.
<trigger_sql> ::= <select_into_stmt>
| <insert_stmt>
| <delete_stmt>
| <update_stmt>
| <replace_stmt>
| <upsert_stmt>
For information on <insert_stmt>, see INSERT.
For information on <delete_stmt>, see DELETE.
For information on <update_stmt>, see UPDATE.
For information on <replace_stmt> and <upsert_stmt>, see REPLACE | UPSERT.
<select_into_stmt> ::= SELECT <select_list> INTO <var_name_list>
<from_clause >
For information on <select_list>, <from_clause>, <where_clause>, <group_by_clause>, <having_clause>,
<set_operator>, <subquery, <order_by_clause> and <limit> see SELECT.
<var_name_list> ::= <var_name>[{, <var_name>}]
<var_name> ::= <identifier>
<var_name> is scalar variable. You can assign a selected item value to this scalar variable.
Description
The CREATE TRIGGER command defines a set of statements that are executed when a given operation (INSERT/
UPDATE/DELETE) takes place on a given subject table.
A trigger is special kind of stored procedure that automatically executes when an event occurs on a given table.
Only database users having the TRIGGER privilege for the given <subject_table_name> are allowed to create a trigger for that table.
Current trigger limitations are described below:
● INSTEAD_OF trigger is not supported.
● Access to the subject table that a trigger is defined on is not allowed in trigger body.
● The maximum number of triggers per table and per DML is 1024. This means that a table can have maximum 1024 insert triggers, 1024 update triggers and 1024 delete triggers.
● Procedure features that are not supported by trigger procedures:
○ Result set assignment to a table type.
○ Exit/continue command (execution flow control).
○ Cursors.
○ Procedure call.
○ Dynamic SQL execution.
○ Return (end SQL statement execution).
System Views
TRIGGERS : Defined triggers.
Examples
Basic trigger usage
You create a table that the trigger will be created upon.
CREATE TABLE TARGET ( A INT);
You create a table that the trigger will access and modify.
CREATE TABLE SAMPLE ( A INT);
You create the following trigger.
CREATE TRIGGER TEST_TRIGGER
AFTER INSERT ON TARGET FOR EACH ROW BEGIN
DECLARE SAMPLE_COUNT INT;
SELECT COUNT(*) INTO SAMPLE_COUNT FROM SAMPLE;
IF :SAMPLE_COUNT = 0
Trigger TEST_TRIGGER will be executed after any record insert execution for TARGET table.
Since SAMPLE table record count is zero at the first insert attempt, the trigger TEST_TRIGGER will insert 5 into the SAMPLE table.
On the second insertion to the TARGET table, the trigger will insert 6 into the SAMPLE table because its count is now two.
INSERT INTO TARGET VALUES (1);
SELECT * FROM SAMPLE;