CREATE TRIGGER DML

trigger_definition
    :    CREATE [OR REPLACE] TRIGGER [IF NOT EXISTS] trigger_name
         trigger_action_time
         {    trigger_dml_event_clause 
         |    trigger_db_event_clause 
         |    trigger_ddl_event_clause 
         }
         compound_statement

trigger_action_time 
    :    BEFORE | AFTER | INSTEAD OF

trigger_dml_event_clause
    :    trigger_dml_event_list
         ON table_or_view_name [REFERENCING [NEW [AS] new_row_name] [OLD [AS] old_row_name] ]
         FOR EACH { ROW [WHEN condition] | STATEMENT }   
         

trigger_dml_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

This statement creates a new DML trigger. A trigger is a named database object that is associated with a table or view and that activates when a particular DML event occurs for that table/view.

DML - Data Modification Language of SQL.

The trigger becomes associated with the table/view named tbl_or_view_name. A table must refer to a permanent table. You cannot associate a trigger with a TEMPORARY table.

Valentina DB has non-standard triggers for a view, with INSTEAD OF trigger time.

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 taken from Oracle/Postgre world.

The IF NOT EXISTS option allows you suppress 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 has taken from the MySQL world.

Is the name of the trigger to be created.

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

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

You cannot specify a BEFORE trigger on a view.

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.

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.

The trigger activates whenever a row is deleted from the table. DROP TABLE and TRUNCATE TABLE statements on the table do not activate this trigger, because they do not use DELETE.

The trigger activates whenever a new row is inserted into the table.

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 a view. Only pure UPDATE event.

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

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.

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.

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.

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).

• 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 a 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 attributes 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.

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

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

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;

This trigger allows automatic assignment 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.