This chapter explains the basic concept of a trigger and how to create it.
Trigger is a kind of schema object. A trigger is automatically executed when a condition, predefined by a database, is met or a certain action is performed. For example, a trigger will be executed when a particular event occurs in a database or when a DDL statement, configured by a user, is executed.
Contents of a trigger consist of persistent stored modules(PSMs). A trigger can be created by calling a predefined PSM object or by declaring it in an anonymous block.
The three components of a trigger are:
Conditional statements or events for executing a trigger
Execution condition constraints
Actions to be performed
The following is an example of creating a trigger named alarm_for_balance:
CREATE OR REPLACE TRIGGER alarm_for_balance BEFORE INSERT OR UPDATE ON balance_tab ... <A> ... FOR EACH ROW WHEN (new.balance < 3000) ... <B> ... CALL alarm_for_balance_fn() ... <C> ...
As shown in <A> of the previous example, when a row is inserted or updated in the balance column of the balance_tab table, the line of code labeled <B> is executed.
The line of code labeled <B> checks if the row value is less than 3000.
If the previous condition is met, the function,
alarm_for_balance_fn, is called, as shown in <C>.
Actions defined in the function will be executed.
Trigger types are divided as follows:
Row and Statement
| Type | Description |
|---|---|
| Row | A trigger is executed for every row in which INSERT, UPDATE, or DELETE occurs. Whenever an operation occurs in a row, the trigger is executed just before or after the operation. |
| Statement | A trigger is executed only one time per statement regardless of the number of rows. |
BEFORE and AFTER
| Type | Description |
|---|---|
| BEFORE | A trigger is executed before a conditional statement is executed. |
| AFTER | A trigger is executed after a conditional statement is executed. |
A trigger type can be one of the following: BEFORE row, BEFORE statement, AFTER row, and AFTER statement.
A trigger can be created as follows:
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
[FOR EACH ROW]
WHEN (condition_constraint)
{[Declaration]
BEGIN
...
END;} |
CALL function_or_procedure_name
For more information about the syntax of a trigger, refer to Tibero SQL Reference Guide.
The following is an example of a row type trigger. A log is recorded whenever the value of a row in the COUNT column of the Deck_tbl table is greater than 1000.
[Example 3.1] Creating a Trigger
CREATE OR REPLACE TRIGGER Log_overflow
AFTER UPDATE ON Deck_tbl
FOR EACH ROW
WHEN (new.count > 1000)
BEGIN
INSERT
INTO Deck_log (Deck_id, Timestamp, New_count, Action)
VALUES (:new.Deck_no, SYSTIMESTAMP, :new.count, 'overflow');
END;
In Tibero, the CREATE statement recognizes the block enclosed by BEGIN and END as PSM. If this statement is compiled, a PSM schema object is created and saved in the database. When an error occurs during the compilation, the error can be checked statically.