Product Documentation

SQL Reference Guide

Previous Topic

Next Topic

CREATE TRIGGER

Description

Creates a trigger for the specified table. A trigger is a special type of stored procedure that helps insure referential integrity for a database.

Triggers contain Java source code which can use FairCom DB SQL Java classes to carry out database operations. Triggers are automatically activated when an INSERT, UPDATE, or DELETE statement affects the trigger’s target table. The Java source code details what actions the trigger takes when it is activated.

For more detail on creating and using triggers, see the FairCom DB SQL Guide to Using Stored Procedures and Triggers.

Syntax

CREATE TRIGGER [ IF NOT EXISTS ] [ owner_name. ] trigname

{ BEFORE | AFTER } { INSERT

| DELETE

| UPDATE [ OF ( column_name [ , ... ] ) }

ON table_name

[ REFERENCING { OLDROW [ , NEWROW ] | NEWROW [ , OLDROW ] } ]

[ FOR EACH { ROW | STATEMENT } ]

[ IMPORT

java_import_clause ]

BEGIN

java_snippet

END

Arguments

IF NOT EXISTS

This argument avoids failure by creating the trigger only if a trigger of the same name does not already exist.

owner_name

Specifies the owner of the trigger. If the name is different from the user name of the user executing the statement, then the user must have DBA privileges.

trigname

Names the trigger. DROP TRIGGER statements specify the trigger name defined here. FairCom DB SQL also uses trigname in the name of the Java class that it creates from the Java snippet.

BEFORE | AFTER

The trigger action time. Specifies whether the triggered action implemented by java_snippet executes before or after the triggering INSERT, UPDATE, or DELETE statement.

INSERT | DELETE | UPDATE [ OF column_name [ , ... ] ]

The statement that activates the trigger. Also called the trigger event.

If UPDATE is the trigger event, this clause can include an optional column list. Updates to any of the specified columns or use of a specified column in a search condition to update other values will activate the trigger. As long as a specified column is not used in either case then the trigger will not be activated. If an UPDATE trigger does not include the optional column list, an update statement specifying any of the table columns will activate the trigger.

ON table_name

The name of the table for which the trigger is defined. table_name cannot be the name of a view or a remote table. A triggering statement that specifies table_name causes the trigger to execute.

REFERENCING { OLDROW [ , NEWROW ] | NEWROW [ , OLDROW ] }

Allowed only if the trigger also specifies the FOR EACH ROW clause. The REFERENCING clause provides a mechanism for c-treeSQL to pass row values as input parameters to the stored procedure implemented by java_snippet. The code in java_snippet uses the getValue() method of the NEWROW and OLDROW objects to retrieve values of columns in rows affected by the trigger event and store them in procedure variables.

The meaning of the OLDROW and NEWROW arguments of REFERENCING clause depends on whether the trigger event is INSERT, UPDATE, or DELETE:

  • INSERT...REFERENCING NEWROW means the triggered action can access values of columns of each row inserted. c-treeSQL passes the column values specified by the INSERT statement.
  • INSERT...REFERENCING OLDROW is meaningless, since there are no existing values for a row being inserted. INSERT...REFERENCING OLDROW generates a syntax error.
  • UPDATE...REFERENCING NEWROW means the triggered action can access the values of columns, after they are changed, of each row updated. SQL passes the column values specified by the UPDATE statement.
  • UPDATE...REFERENCING OLDROW means the triggered action can access the values of columns, before they are changed, of each row updated. c-treeSQL passes the column values of the row as it exists in the database before the update operation.
  • DELETE...REFERENCING OLDROW means the triggered action can access values of columns of each row deleted. c-treeSQL passes the column values of the row as it exists in the database before the delete operation.
  • DELETE...REFERENCING NEWROW is meaningless, since there are no new existing to pass for a row being deleted. DELETE...REFERENCING OLDROW generates a syntax error.

UPDATE is the only triggering statement that allows both NEWROW and OLDROW in the REFERENCING clause.

Note that the trigger action time (BEFORE or AFTER) does not affect the meaning of the REFERENCING clause. For instance, BEFORE UPDATE...REFERENCING NEWROW still means the values of columns after they are updated will be available to the triggered action.

The REFERENCING clause generates an error if the trigger does not include the FOR EACH ROW clause.

FOR EACH { ROW | STATEMENT }

The frequency with which the triggered action implemented by java_snippet executes:

  • FOR EACH ROW means the triggered action executes once for each row being updated by the triggering statement. CREATE TRIGGER must include the FOR EACH ROW clause if it also includes a REFERENCING clause.
  • FOR EACH STATEMENT means the triggered action executes only once for the whole triggering statement. FOR EACH STATEMENT is the default.

IMPORT
java_import_clause

Specifies standard Java classes to import. The IMPORT keyword must be upper case and on a separate line.

BEGIN
java_snippet

END

The body of the trigger. Also called the triggered action. The body contains the Java source code that implements the actions to be completed when a triggering statement specifies the target table. The Java statements become a method in a class the FairCom DB SQL engine creates and submits to the Java compiler.

The BEGIN and END keywords must be upper case and on separate lines.

Notes

  • Triggers can take action on their own table so that they invoke themselves. FairCom DB SQL limits such recursion to five levels.
  • You can you have multiple triggers on the same table. FairCom DB SQL executes all triggers applicable to a given combination of table, trigger event, and action time. If more than one trigger is applicable for a particular combination, FairCom DB SQL executes the triggers in the order they were created. (You can determine the creation order from the triggerid column in the admin.systrigger system table. The higher the triggerid value, the later the trigger was created.) The interaction of multiple triggers on the same table can be confusing, so exercise care.
  • The actions carried out by a trigger may fire another trigger. When this happens, the other trigger’s actions execute before the rest of the first trigger finishes executing. FairCom DB SQL limits such nesting to five levels.
  • If a FairCom DB SQL statement both fires a trigger and violates a constraint (possible if the trigger action time is BEFORE), any actions taken by the trigger are rolled back and do not take effect.
  • To modify an existing trigger, you must delete it and issue another CREATE TRIGGER statement. You can query the admin.systrigger and sysproctxt system tables for details of the trigger before deleting it.

Example

This example illustrates an update trigger on a table called BUG_INFO. If the STATUS or PRIORITY fields are modified, the trigger modifies the BUG_SUMMARY and BUG_STATUS tables appropriately based on some conditions.

CREATE TRIGGER BUG_UPDATE_TRIGGER

AFTER UPDATE OF STATUS, PRIORITY ON BUG_INFO

REFERENCING OLDROW, NEWROW

FOR EACH ROW

IMPORT

import java.sql.* ;

BEGIN

try

{

// column number of STATUS is 10

String old_status, new_status;

old_status = (String) OLDROW.getValue(10, CHAR);

new_status = (String) NEWROW.getValue(10, CHAR);

if (old_status.equals("OPEN") && new_status.equals("FIXED"))

{

// If STATUS has changed from OPEN to FIXED

// increment the bugs_fixed_cnt by 1 in the

// row corresponding to current month

// and current year

SQLIStatement update_stmt = new SQLIStatement(

" update BUG_STATUS set bugs_fixed_cnt = bugs_fixed_cnt + 1 " +

" where month = ? and year = ?"

);

Integer current_month = 10;

Integer current_year = 1997;

update_stmt.setParam(1, current_month);

update_stmt.setParam(2, current_year);

update_stmt.execute();

SQLIStatement insert_stmt = new SQLIStatement(

" insert into BUG_SUMMARY values (?,?,?)"

);

// Column number for bug_id, priority, reported_on and fixed_on

// are 1, 2, 5, 6

String bug_id, priority;

Date reported_on, fixed_on;

bug_id = (String) NEWROW.getValue(1, CHAR);

priority = (String) NEWROW.getValue(2, CHAR);

reported_on = (Date) NEWROW.getValue(5, DATE);

fixed_on = (Date) NEWROW.getValue(6, DATE);

Integer turn_around_time = fixed_on - reported_on;

insert_stmt.setParam(1, bug_id);

insert_stmt.setParam(2, priority);

insert_stmt.setParam(3, turn_around_time);

insert_stmt.execute();

}

// If PRIORITY has changed to URGENT,

//increment the bugs_escalated by 1 in the month field.

String old_priority, new_priority;

old_priority = (String) OLDROW.getValue(2, CHAR);

new_priority = (String) NEWROW.getValue(2, CHAR);

if(new_priority.equals("URGENT") && old_priority.equals("URGENT"))

{

// If PRIORITY has changed to URGENT

// increment the bugs_escalated by 1 in the row corresponding to current month

// and current year

SQLIStatement update_stmt (

" update BUG_STATUS set bugs_escalated_cnt = bugs_escalated_cnt + 1 " +

" where month = ? and year = ?"

);

Integer current_month = 10;

Integer current_year = 1997;

update_stmt.setParam(1, current_month);

update_stmt.setParam(2, current_year);

update_stmt.execute();

}

}

catch (SQLException e)

{

// Log the exception message from e.

SQLException sqle = new SQLException("UPDATE_BUG_TRIGGER failed");

throw sqle;

}

END

Authorization

Users executing CREATE TRIGGER must have the DBA privilege or RESOURCE privilege.

SQL Compliance

SQL-93, ODBC Core SQL grammar

Environment

Embedded SQL, interactive SQL, ODBC applications

Related Statements

DROP TRIGGER

TOCIndex