Switch to: V10V9V8V7V6V5

CREATE TRIGGER

To create and enable a database trigger.

A database trigger is a stored SQL block that is associated with a table. Valentina automatically executes a trigger when a specified SQL statement is issued against the table.

Syntax

trigger_definition
    :    CREATE [OR REPLACE] TRIGGER [IF NOT EXISTS] trigger_name
         trigger_time trigger_event_list
         ON table_or_view_name
         [REFERENCING [NEW [AS] new_row_name] [OLD [AS] old_row_name] ]
         FOR EACH { ROW [WHEN condition] | STATEMENT }   
         compound_statement      

trigger_time 
    :    BEFORE | AFTER | INSTEAD OF

trigger_event_list
    :    trigger_event [ {OR|,} trigger_event ]* 
       
trigger_event 
    :    DELETE | INSERT | UPDATE [OF column_list]

old_row_name : IDENT
new_row_name : IDENT
table_or_view_name : IDENT

Arguments

OR REPLACE

Recreates the trigger if it already exists. You can use this option to change the definition of an existing trigger without first dropping it.

NOTE: 'OR REPLACE' syntax Valentina have take from Oracle/Postgre world.

IF NOT EXISTS

The IF NOT EXISTS option allows you supress the error message in case if such trigger already exists. This makes it much easier to perform SQL dumps without interruption.

NOTE: 'IF NOT EXISTS' syntax Valentina have take from mySQL world.

trigger_name

Is the name of the trigger to be created.

:!: Trigger name should be unique in the scope of database.

BEFORE

Specifies that the trigger is fired before executing the triggering statement.

:!: You cannot specify a BEFORE trigger on a view.

AFTER

Specifies that trigger is fired only when all operations specified in the triggering SQL statement have executed successfully. All referential cascade actions and constraint checks also must succeed before this trigger executes.

:!: You cannot specify an AFTER trigger on a view.

INSTEAD OF

Specifies that the trigger is executed instead of the triggering SQL statement, thus overriding the actions of the triggering statements.

At most, one INSTEAD OF trigger per INSERT, UPDATE, or DELETE statement can be defined on a table or view.

:!: INSTEAD OF is a valid clause only for views. You cannot specify an INSTEAD OF trigger on a table.
:!: INSTEAD OF trigger cannot have WHEN clause.
:!: INSTEAD OF trigger cannot have UPDATE with OF <columns> clause. Only pure UPDATE event.

DELETE

Specifies that trigger is fired whenever a DELETE statement removes a row from the table.

INSERT

Specifies that trigger is fired whenever an INSERT statement adds a row to table.

UPDATE...OF

Specifies that trigger is fired whenever an UPDATE statement changes a value in one of the columns specified in the OF clause. If you omit the OF clause, Valentina fires the trigger whenever an UPDATE statement changes a value in any column of the table.

:!: OF cannot be used with INSTEAD OF trigger on view. Only pure UPDATE event.

ON

Specifies the name of the table or view on which the trigger is to be created.

REFERENCING

Specifies correlation names. You can use correlation names in the SQL block and WHEN clause of a row trigger to refer specifically to old and new values of the current row. The default correlation names are OLD and NEW. If your row trigger is associated with a table named OLD or NEW, you can use this clause to specify different correlation names to avoid confusion between the table name and the correlation name.

FOR EACH ROW

Designates the trigger to be a row trigger. Valentina fires a row trigger once for each row that is affected by the triggering statement and meets the optional trigger constraint defined in the WHEN clause.

If you omit this clause, the trigger is a statement trigger. Valentina fires a statement trigger only once when the triggering statement is issued if the optional trigger constraint is met.

WHEN

Specifies the trigger restriction. The trigger restriction contains a SQL condition that must be satisfied for Valentina to fire the trigger. This condition must contain correlation names and cannot contain a query.

You can only specify a trigger restriction for a row trigger. Valentina evaluates this condition for each row affected by the triggering statement.

compound_statement

Is a SQL block that Valentina executes to fire the trigger. Note that the SQL block of a trigger cannot contain transaction control SQL statements (COMMIT, ROLLBACK, and SAVEPOINT).

Notes

  • You can create only BEFORE and AFTER triggers for tables. INSTEAD OF triggers are only available for views.
  • You may specify up to three triggering events as list separated by OR or COMA. OR keyword provides Oracle compatibility, COMA follow to Sybase.
... INSERT ON R ...
... INSERT OR DELETE OR UPDATE ON R ...
  • Furthermore, UPDATE can be optionally followed by the keyword OF and a list of attribute(s) in <table_name>. If present, the OF clause defines the event to be only an update of the attribute(s) listed after OF. Here are some examples:
... UPDATE OF A, B OR INSERT ON R ...
  • If FOR EACH ROW option is specified, the trigger is row-level; otherwise, the trigger is statement-level.
  • Only for row-level triggers:
  • The special variables NEW and OLD are available to refer to new and old records respectively.
  • The REFERENCING clause can be used to assign aliases to the variables NEW and OLD.
  • A trigger restriction can be specified in the WHEN clause, enclosed by parentheses. The trigger restriction is a SQL condition that must be satisfied in order for Valentina to fire the trigger. This condition cannot contain subqueries. Without the WHEN clause, the trigger is fired for each row.
  • <trigger_body> is a SQL block, rather than sequence of SQL statements. Valentina has placed certain restrictions on what you can do in <trigger_body>, in order to avoid situations where one trigger performs an action that triggers a second trigger, which then triggers a third, and so on, which could potentially create an infinite loop. The restrictions on <trigger_body> include:
    • You cannot modify the same relation whose modification is the event triggering the trigger.
    • You cannot modify a relation connected to the triggering relation by another constraint such as a foreign-key constraint.

CREATE TRIGGER Errors

  • “Trigger with name %U already exists for Table %U on event %U.”

You need to drop this trigger before create a new one, or use OR REPLACE syntax.

Trigger Examples

Example 1

We illustrate Valentina's syntax for creating a trigger through an example based on the following two tables:

CREATE TABLE T4 (a INTEGER, b CHAR(10));
CREATE TABLE T5 (c CHAR(10), d INTEGER);

We create a trigger that may insert a record into T5 when a record is inserted into T4. Specifically, the trigger checks whether the new record has a first component 10 or less, and if so inserts the reverse record into T5:

CREATE TRIGGER trig1
    AFTER INSERT ON T4
    REFERENCING NEW AS newRow
    FOR EACH ROW
    WHEN (newRow.a <= 10)
    BEGIN
        INSERT INTO T5 VALUES(newRow.b, newRow.a);
    END;

Example 2

This trigger allow automatic assigment of NOW() values into fields when you insert a new record:

CREATE TABLE T1 (create_time datetime, update_time datetime, f1 ulong);
 
CREATE TRIGGER T1_BI BEFORE INSERT 
    ON T1 FOR EACH ROW
    BEGIN
        NEW.create_time = NOW(); 
        NEW.update_time = NOW();
    END;
 
CREATE TRIGGER T1_BU BEFORE UPDATE 
    ON T1 FOR EACH ROW
    BEGIN
        NEW.update_time = NOW();
    END;
 
INSERT INTO T1 (f1) VALUES (55); // two other FIELDS get NOW() value.
 
UPDATE T1 SET f1 = 68; // now update_time FIELD contains newer value.