CREATE TRIGGER — create a new trigger


    {[FOR] [schema.]table_name|ON DATABASE }
    [[ BEFORE | AFTER ] event]
    [ POSITION number ] 
    [ LANGUAGE { SQL | JAVA } ]
    EXTERNAL 'trigger_routine'


CREATE TRIGGER creates a new trigger. 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.

When the OR REPLACE clause is used for an existing trigger name, the effect is that of dropping the existing trigger and creating a new one with the given specification. OR REPLACE does not have any effect on the command if a trigger with the specified name does not already exist.

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

Note: When using CREATE OR REPLACE statements, and the same database object is created concurrently from two different transactions, the following error may be returned:

duplicate value in unique index ...

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:


The POSITION clause specifies the order in which multiple triggers for a table are fired for the same operation for the same event. Trigger position by default is 0 and triggers are fired 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.


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.


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.


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.



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.