Skip to content
≡

Administrator

Developer

Tools

External Tools

CREATE TRIGGER

CREATE TRIGGER#

Syntax#

create_trigger ::=#

create_trigger

simple_dml_trigger ::=, instead_of_dml_trigger ::=

simple_dml_trigger ::=#

simple_dml_trigger_image151

trigger_event ::=, referencing_clause ::=, trigger_action ::=, psm_body ::=

trigger_event ::=#

trigger_event_image152

referencing_clause ::=#

referencing_clause_image152_1

trigger_action ::=#

psm_body ::=#

instead_of_dml_trigger ::=#

psm_body ::=

Prerequisites#

At least one of the following conditions must be met:

  • The SYS user
  • Users have the CREATE TRIGGER or CREATE ANY TRIGGER system privilege to create a trigger in their own schema
  • Users have the CREATE ANY TRIGGER system privilege to create a trigger in another user's schema

Description#

This command is used to create a trigger having the specified name.

OR REPLACE#

This is used to replace a trigger of the same name if a trigger already exists. That is, this changes the definition of an existing trigger instead of removing and recreating an existing trigger.

user_name#

This is used to specify the name of the owner of the trigger to be created. If this value is omitted, Altibase will assume that the trigger is to be created in the current user's schema.

trigger_name#

This is used to specify the name of the trigger to be created. Refer to "Rules for Object Names" in Chapter 2 for more information on specifying names.

AFTER#

Choose the AFTER option to fire the trigger after the execution of trigger_event, that is, the event that caused the trigger to fire.

BEFORE#

Choose the BEFORE option to fire the trigger before the execution of trigger_event, that is, the event that caused the trigger to fire.

INSTEAD OF#

Choose the INSTEAD OF option to fire the trigger, instead of performing the triggering DML statement. INSTEAD OF triggers can only be created on views. If the view has a LOB column, a trigger can be created with the INSTEAD OF option; however, an error is raised when the triggering DML statement is executed.

trigger_event#

This is an event that changes the data in a table and causes the trigger to fire. Note that in order to preserve database integrity, DML operations that change the data and are initiated by the replication Receiver thread in a replication target table (i.e. a table that is cited in an active replication object) are not treated as a trigger event (i.e. do not fire the trigger on the table). Multiple trigger events can be applied to one trigger. trigger_event can specify the three following DML statements.

DELETE#

DELETE option is used to fire the trigger when data in the table are deleted by executing a DELETE statement.

INSERT#

INSERT option is used to fire the trigger when data are inserted into the table by executing an INSERT statement. Tables with LOB columns can create a trigger with the 'BEFORE INSERT … FOR EACH ROW' statement. However, an error occurs when the DML statement firing the trigger is executed.

UPDATE#

UPDATE option is used to fire the trigger when data in the table are updated by executing an UPDATE statement. The OF clause specifies that the trigger will only be fired when an UPDATE statement changes one of the columns specified therein. Tables with LOB columns can create a trigger with the 'BEFORE UPDATE … FOR EACH ROW' statement. However, an error occurs when the DML statement firing the trigger is executed.

ON table_name#

This is used to specify the table that is referenced when determining whether the trigger will fire. The trigger will fire in response to a change made to the table specified in table_name.

Triggers can only reference regular tables. They cannot be created on the basis of objects such as views, sequences, or stored procedures.

Triggers cannot be created on the basis of tables that are referenced in replications. Likewise, any attempt to create a replication object that references a table that is already referenced by a trigger will fail.

If the user_name is omitted, Altibase will assume that the trigger is to be created for a table in the current user's schema.

REFERENCING Clause#

One characteristic of triggers is the concept of old and new rows. When the data in the table referenced by a trigger are changed, an individual row that was changed will consequently have both old and new values. The REFERENCING clause makes it possible to refer to either the old value or the new value as desired.

The REFERENCING clause has the following restrictions:

  • The REFERENCING clause must be used together with the FOR EACH ROW option.
  • The REFERENCING clause must have the following structures so that it can be referred to in the trigger_action clause.
{OLD|OLD ROW|OLD ROW AS|OLD AS} alias_name#

This indicates the data contained in a record before it is modified. Old values can be referenced in the WHEN clause or in psm_body in trigger_action. It has NULL value when the trigger event is an INSERT trigger event, because there is no old value.

{NEW|NEW ROW|NEW ROW AS|NEW AS} alias_name#

This indicates the data contained in a record after it is modified. Note that when the trigger is a BEFORE trigger, it is possible to change these data in the body of the trigger. It has NULL value when the trigger event is a DELETE trigger event and does not affect it, because there is no new value.

trigger_action#

The trigger action clause consists of the following three parts:

  • Action granularity: Determines the unit (row or statement) by which the trigger operates.
  • Action WHEN condition: Optionally used to set an additional condition to determine whether the trigger will fire.
  • Action body: Determines what the trigger actually does.
FOR EACH {ROW|STATEMENT}#

This is used to specify the unit of operation of the trigger. The changes to the data in the table take place according to this unit. The default is FOR EACH STATEMENT.

  • FOR EACH ROW: The operations specified in action body are conducted once for each row that is affected by trigger_event and satisfies the WHEN clause. The FOR EACH ROW clause must be used when either the REFERENCING clause or the WHEN clause is used.
  • FOR EACH STATEMENT: The trigger will be fired only once, either after or before execution of the DML statement that caused the trigger to fire.
WHEN search_condition#

This is used to specify the conditions that are used to determine whether to fire the trigger. The action body of the trigger is executed only if the search_condition in the WHEN clause evaluates to TRUE. If the search_condition in the WHEN clause evaluates to FALSE, the action body of the trigger will not be executed. If no WHEN clause is specified, the action body of the trigger will be executed every time the trigger event occurs.

To use a condition in the WHEN clause, the following constraints must be satisfied:

  • The WHEN clause can only be used with the FOR EACH ROW clause.
  • Only the alias_name defined in the REFERENCING clause can be used in the WHEN search_condition.
  • Subqueries cannot be used in the WHEN search_condition.
  • Stored procedures cannot be used in the WHEN search_condition.
psm_body#

This is the so-called "action body" of the trigger, and is used to specify the actual operations that are to be carried out by the trigger. psm_body can be specified in the same way as a block statement in a stored procedure.

psm_body must satisfy the following constraints:

Due to the nature and concept of a trigger, the following SQL statement syntax for the action body cannot be used.

  • Transaction-related statements such as COMMIT and ROLLBACK cannot be used.
  • Session-related statements such as CONNECT cannot be used.
  • Schema-related statements such as CREATE TABLE cannot be used.
  • Stored procedures cannot be called.
  • Recursive triggers, that is, triggers that perform the operation specified in trigger_event, cannot be created.
ENABLE | DISABLE#

The user can select an enable or a disable database trigger when creating a trigger. The default value is set to enable.

  • If the status is set to disable, it does not operate when creating a trigger, but the trigger status can be modified by the ALTER TRIGGER statement.

For more detailed information about the use of block statements in stored procedures, please refer to the Stored Procedures Manual.

Considerations#

  • Order of Trigger Execution
    It is possible to create multiple triggers that reference the same table. In such cases, the order in which the triggers fire is not fixed. If it is important to control the order in which the actions of multiple triggers occur, rewrite the triggers as a single trigger.
  • Trigger Execution Failure
    If an error occurs while a trigger is executing, the DML statement that caused the trigger to fire will also fail.
  • Execution of DDL on Tables Referenced in Triggers
    When a table that causes triggers to fire is deleted using the DROP TABLE statement, the triggers that fire in response to changes made to the table are also deleted. However, when a table that is referred to by the action body of a trigger is altered or dropped, the trigger is not dropped. In the case where the table is dropped, when it becomes impossible to perform the operations in the action body of such a trigger, any DML statements that cause the trigger to fire will fail. In the case where the table is altered, the trigger will be internally recompiled and executed at the time it is fired.
  • Triggers and Replication
    DML statements that are executed in the course of replication do not cause triggers to fire.

Examples#

<Query> The following example shows how to use a trigger to track the deletion of rows. In this example, the trigger operates on a FOR EACH ROW basis, and references the original values in the ono, cno, qty and arrival_date columns of the orders table when data about completed (processing='D') delivery orders are deleted from the table. The trigger creates a record of the deleted rows by inserting rows into the log_tbl table.

iSQL> CREATE TABLE log_tbl(
  ono BIGINT,
  cno BIGINT,
  qty INTEGER,
  arrival_date DATE,
  sysdate DATE);
Create success.

iSQL> CREATE TRIGGER del_trigger
  AFTER DELETE ON orders
  REFERENCING OLD ROW old_row
  FOR EACH ROW
  AS BEGIN
    INSERT INTO log_tbl VALUES(old_row.ono, old_row.cno, old_row.qty, old_row.arrival_date, sysdate);
  END;
/
Create success.

iSQL> DELETE FROM orders WHERE processing = 'D';
2 rows deleted.
iSQL> SELECT * FROM log_tbl;
ONO                  CNO                  QTY         ARRIVAL_DATE
------------------------------------------------------------------------
SYSDATE
---------------
11290011             17                   1000        05-DEC-2011
25-APR-2012
11290100             11                   500         07-DEC-2011
25-APR-2012
2 rows selected.

<Query> In the following example, when a record is inserted into the SCORES table, a value of 0 is set for the SCORE column if NULL is specified. This is accomplished using a BEFORE INSERT trigger that fires FOR EACH ROW.

iSQL> CREATE TABLE scores( id INTEGER, score INTEGER );
Create success.
iSQL> CREATE TRIGGER scores_trigger
BEFORE INSERT ON scores
REFERENCING NEW ROW NEW_ROW
FOR EACH ROW
AS BEGIN
  IF NEW_ROW.SCORE IS NULL THEN
     NEW_ROW.SCORE := 0;
  END IF;
END;
/
Create success.

iSQL> INSERT INTO scores VALUES( 1, 20 );
1 row inserted.
iSQL> INSERT INTO scores VALUES( 5, NULL );
1 row inserted.
iSQL> INSERT INTO scores VALUES( 17, 75 );
1 row inserted.

iSQL> SELECT * FROM SCORES;
ID          SCORE       
---------------------------
1           20          
5           0           
17          75          
3 rows selected.

<Query> Confirm the operating status by creating a disable trigger, and modifies it to enable to check the status again.

iSQL> CREATE TABLE scores( id INTEGER, score INTEGER );
Create success.

iSQL> CREATE TRIGGER scores_trigger
BEFORE INSERT ON scores
REFERENCING NEW ROW NEW_ROW
FOR EACH ROW
DISABLE
AS BEGIN
IF NEW_ROW.SCORE IS NULL THEN
NEW_ROW.SCORE := 0;
END IF;
END;
/
Create success.

iSQL> INSERT INTO scores VALUES( 1, 20 );
1 row inserted.
iSQL> INSERT INTO scores VALUES( 5, NULL );
1 row inserted.
iSQL> INSERT INTO scores VALUES( 17, 75 );
1 row inserted.

iSQL> SELECT * FROM SCORES;
ID SCORE
---------------------------
1 20
5
17 75
3 rows selected.

iSQL> ALTER TRIGGER scores_trigger ENABLE;
Alter success.

iSQL> INSERT INTO scores VALUES( 100, NULL );
1 row inserted.

iSQL> SELECT * FROM SCORES;
ID SCORE
---------------------------
1 20
5
17 75
100 0
4 rows selected.