You are here: Reference Information > SQL Reference Information > SQL Statements > CREATE TRIGGER

CREATE TRIGGER

CREATE TRIGGER — define a new trigger

Syntax

CREATE TRIGGER trigger_name 
    [FOR] [schema.]table_name|ON DATABASE }
    [[ BEFORE | AFTER ] event]
    [ POSITION number ] 
    [ ACTIVE | INACTIVE ]
    [ LANGUAGE { SQL | JAVA } ]
    [FOR EACH ROW|FOR EACH STATEMENT]
{
    EXTERNAL 'trigger_routine'
| 
    AS 
        sql_code_block
    END_TRIGGER;
}

Description

CREATE TRIGGER creates a new trigger in the current database. The trigger is associated with the specified table and executes the statements when certain events occur. Only users that own the table, or users that have been granted ALL privilege on a table, can create a trigger for a table. A single trigger can be specified for multiple events.

Caution: CREATE TRIGGER on a view object is not supported on BEFORE and AFTER timing events which are the two options supported by NuoDB. A SQL error will be returned.

Caution: Transaction commit and rollback statements cannot be called within a Trigger.

Before or After Event

The trigger can be specified to fire before (BEFORE event) the operation is attempted on a row (for example, before constraints are changed, an INSERT, UPDATE, or DELETE is attempted); or after (AFTER event) the operation has completed (for example, after constraints are checked and the INSERT, UPDATE, or DELETE completes). If the trigger fires before the event, the trigger can skip the operation for the current row, or change the row being inserted or updated. If the trigger fires after the event, all changes, including the effects of other triggers, are "visible" to the trigger.

The trigger is called once for every row the operation modifies. For example, a DELETE operation that affects 10 rows will cause all ON DELETE triggers defined for that table to be called ten (10) separate times, once for each deleted row. If the DELETE operation affects zero (0) rows, no triggers will fire.
Since a SELECT statement does not modify rows, you cannot create triggers on SELECT events.

Triggers make use of two special tables:

See examples below.

Trigger associated with an entire database

When the trigger is associated with the entire database, the event can either be CREATE TABLE, DROP TABLE, CREATE INDEX and DROP INDEX. The trigger is invoked either before or after one of these DDL operations is performed, and can utilize one of three predefined variables to detect the object being manipulated:

Trigger associated with an entire statement

When the trigger is associated with an entire statement, the event can either be COMMIT, DELETE, INSERT and UPDATE. Each trigger is invoked exactly once per statement execution regardless or the number of affected rows. In the case of triggers executed after the DML operation is performed, the procedural code can utilize two predefined tables to detect the rows being manipulated:

Position

The POSITION clause specifies the order in which multiple triggers for a table are defined for the same operation for the same event. Trigger position by default is 0 and triggers are run in order of ascending POSITIONnumber.

Active or Inactive

A trigger can be created as disabled using the INACTIVE keyword and enabled at a later date using ALTER TRIGGER. By default a trigger is created as ACTIVE.

Language

The language for a stored procedure, user defined function or trigger may be SQL or Java. This is specified by declaring the procedure, function or trigger with LANGUAGE SQL or LANGUAGE JAVA. The default language, if none is specified, is SQL. SQL stored procedures, UDFs, and triggers are defined by sql_code_block. See NuoDB SQL Procedural Language for more information.

Stored procedures, user defined functions and triggers written in Java must use the EXTERNAL keyword. See Using Embedded Java Stored Procedures.

FOR EACH ROW/ FOR EACH Statement

If neither option is specified, FOR EACH ROW is the default option.

External Definition

Declaring a trigger as EXTERNAL is done to describe a dynamically loadable Java trigger that is to be executed. For LANGUAGE JAVA, the EXTERNAL trigger defined by sp_routine must be in the format classid:fully_qualified_class.method, where classid is the ID used in the CREATE JAVACLASS command that was used to upload the Jar [not JAR] with the defined code.

. See CREATE JAVACLASS.

SQL Procedural Language - CREATE TRIGGER body

If defining this stored procedure in SQL, sql_code_block is one or more SQL statements. See NuoDB SQL Procedural Language for more informatoin.

Parameters

Examples

One or more of the examples below make use of tables defined in the HOCKEY schema, which is a schema defined in the test database. The test database is created when the QuickStart process is run. See Running the SQL QuickStart.