CREATE TRIGGER

CREATE TRIGGER — create a new trigger

Syntax

CREATE [OR REPLACE] 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. 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.

COMMIT and ROLLBACK transactional SQL statements are not allowed within a trigger.

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

In the event that a user-defined function is called by an expression-based index, any attempt to drop or modify the function will complete with an error warning but the index will not be recreated.

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:

NEW is an alias for a table that contains the data referenced in an INSERT or UPDATE trigger before it is actually committed to the database.
OLD is an alias for a table that contains the data in the underlying table referenced in an UPDATE or DELETE trigger before it is actually updated or removed from the database.

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, or 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:

DDL_SCHEMA_NAME is the name of the schema of the table/index
DDL_OBJECT_NAME is the name of the table/index
DDL_OPERATION_TYPE is either create_table, drop_table, create_index or drop_index

Trigger associated with an entire statement

When the trigger is associated with an entire statement, the event can either be DELETE, INSERT, or 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:

OLD_TABLE is a table holding the rows as they were before being manipulated: it is populated only for DELETE and UPDATE statements
NEW_TABLE is a table holding the rows as they are after being manipulated: it is populated only for UPDATE and INSERT statements

Position

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 POSITION[.var]number.

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 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 SQL Procedural Language for more informatoin.

Parameters

trigger_name: Name to give the new trigger. This must be distinct from the name of any other trigger for the same table. Schema names cannot be included here; the trigger is always created in the same schema as the table for which it is being defined. Trigger names cannot be greater than 128 characters.
schema: Name of the schema that owns the table for which the trigger is being created. If not provided, the table must exist in the current schema.
table_name: Name of the table on which the trigger operates.
ON DATABASE: Indicates that the trigger to modify is a database trigger.
event: One of INSERT, UPDATE, or DELETE; the event is what actuates the trigger.
POSITION pos_number: The position clause specifies the order in which multiple triggers for a table are fired for the same event. Trigger position by default is 0 and triggers are fired in order of ascending POSITION number.
ACTIVE | INACTIVE: Enables or disables a trigger. The default value is ACTIVE.

Examples

Example 1: Create a trigger to fire BEFORE INSERT

Create a trigger to fire BEFORE INSERT. However, the trigger will only perform changes if the boolean condition is true.

SET DELIMITER @
CREATE TRIGGER trg_hockey_insert FOR hockey.hockey BEFORE INSERT
AS
    IF (new.team <> 'Bruins')
        THROW 'Team Must Be "Bruins"';
    END_IF;
END_TRIGGER
@
SET DELIMITER ;

INSERT INTO hockey.hockey (number, name, position, team)
                   VALUES (99,'BOBBY ORR','Forward','Bruins');
INSERT INTO hockey.hockey (number, name, position, team)
                   VALUES (2, 'TOM JONES', 'Fan', 'Rangers');
Team Must Be "Bruins"

SELECT * FROM hockey.hockey WHERE number IN (99,2);
ID  NUMBER    NAME    POSITION   TEAM
 --- ------- --------- --------- ------
 25    99    BOBBY ORR  Forward  Bruins

Example 2: Create a trigger to derive values in a table on INSERT

DROP TABLE IF EXISTS hockey_fan CASCADE;
CREATE TABLE hockey.hockey_fan (id          INTEGER GENERATED ALWAYS AS IDENTITY,
                         last_name   STRING,
                         first_name  STRING,
                         full_name   STRING,
                         teamid      CHAR(3),
                         create_date DATE DEFAULT DATE('now'),
                         modify_date DATE );
SET DELIMITER @
CREATE TRIGGER trg_hockeyfan_b_insert FOR hockey_fan BEFORE INSERT
AS
    new.full_name = new.first_name||' '||new.last_name;
END_TRIGGER
@

SET DELIMITER ;
INSERT INTO hockey_fan (last_name, first_name, teamid)
                VALUES ('JONES','TOM','BOS');

SELECT * FROM hockey_fan;
 ID  LAST_NAME  FIRST_NAME  FULL_NAME  TEAMID  CREATE_DATE  MODIFY_DATE
 --- ---------- ----------- ---------- ------- ------------ ------------
  1    JONES        TOM     TOM JONES    BOS    2013-10-28     <null>

Example 3: Create a trigger to maintain an audit table of updates made to another table

Create a trigger that will maintain an audit table of updates made to another table. This example makes use of the table and trigger created in Example 2. This example also illustrates a trigger with SQL statements that reference a table in another schema.

DROP TABLE IF EXISTS audit.hockey_fan_audit CASCADE;
CREATE TABLE audit.hockey_fan_audit (id          STRING NOT NULL,
                               old_teamid  CHAR(3),
                               new_teamid  CHAR(3),
                               audit_date  DATE DEFAULT DATE('now') );
SET DELIMITER @
CREATE TRIGGER trg_hockeyfan_b_update FOR hockey.hockey_fan BEFORE UPDATE
AS
    new.full_name = new.first_name || ' ' || new.last_name;
    new.modify_date = DATE('now');
END_TRIGGER
@

CREATE TRIGGER trg_hockeyfan_a_update FOR hockey.hockey_fan AFTER UPDATE
AS
    IF (old.teamid != new.teamid)
        INSERT INTO audit.hockey_fan_audit (id, old_teamid, new_teamid)
                            VALUES (old.id, old.teamid, new.teamid);
    END_IF;
END_TRIGGER
@
SET DELIMITER ;


SELECT * FROM hockey.hockey_fan;    /* Created in Example 3 */
 ID  LAST_NAME  FIRST_NAME  FULL_NAME  TEAMID  CREATE_DATE  MODIFY_DATE
 --- ---------- ----------- ---------- ------- ------------ ------------
  1    JONES        TOM     TOM JONES    BOS    2013-10-28     <null>

UPDATE hockey.hockey_fan
   SET teamid = 'NYR'
  WHERE last_name = 'JONES' and first_name = 'TOM';

SELECT * FROM hockey.hockey_fan;
 ID  LAST_NAME  FIRST_NAME  FULL_NAME  TEAMID  CREATE_DATE  MODIFY_DATE
 --- ---------- ----------- ---------- ------- ------------ ------------
  1    JONES        TOM     TOM JONES    NYR    2013-10-28   2013-10-28

SELECT * FROM audit.hockey_fan_audit;
 ID  OLD_TEAMID  NEW_TEAMID  AUDIT_DATE
 --- ----------- ----------- -----------
  1      BOS         NYR     2013-10-28

Example 4: Create a trigger that uses a FOR SELECT loop

Create a trigger that will derive a column value for an inserted row, based on concatenating multiple row column value from another table.

USE HOCKEY;
DROP TABLE IF EXISTS player_years;
CREATE TABLE player_years (Player_Id    STRING NOT NULL,
                           Years_Played  STRING);
SET DELIMITER @
CREATE TRIGGER trg_player_years_before_insert FOR Player_years BEFORE INSERT
  AS
    VAR l_Years_Played string = '';
    FOR SELECT DISTINCT year AS team_year
           FROM scoring
           WHERE playerid = new.Player_Id;
         l_Years_Played = l_Years_Played || team_year || ',';
    END_FOR;
    new.Years_Played = substr(l_Years_Played,1,length(l_Years_Played) -1);
END_TRIGGER;
@

SET DELIMITER ;

INSERT INTO Player_Years (Player_Id) SELECT playerId FROM players limit 5;

SELECT * FROM Player_Years;
 PLAYER_ID        YEARS_PLAYED
 ---------- ------------------------
 aaltoan01  1997,1998,1999,2000
 abbeybr01  1975
 abbotge01  1943
 abbotre01  1952
 abdelju01  2007,2008,2009,2010,2011

Example 5: Create a trigger that updates a table following a CREATE TABLE event

Create an ON DATABASE trigger that audits the creation of tables by inserting associated rows into the newly created ddl_operations table.

CREATE TABLE ddl_operations (schema_name STRING, object_name STRING, operation_type STRING);
SET DELIMITER @
CREATE TRIGGER ddl_trigger_after_create_table ON DATABASE AFTER CREATE TABLE
AS
INSERT INTO ddl_operations VALUES (DDL_SCHEMA_NAME, DDL_OBJECT_NAME, DDL_OPERATION_TYPE);
END_TRIGGER;
@

SET DELIMITER ;
CREATE TABLE a.table1 (value INT);
CREATE TABLE b.table2 (value INT);

SELECT * FROM ddl_operations;

SCHEMA_NAME  OBJECT_NAME  OPERATION_TYPE
------------ ------------ ---------------

A          TABLE1     create_table
B          TABLE2     create_table