CREATE TRIGGER

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

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

Syntax

CREATE TRIGGER [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

Parameters

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.

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

Denotes the trigger action time. The trigger action time specifies whether the triggered action, implemented by java_snippet, executes BEFORE or AFTER the invoking INSERT, UPDATE, or DELETE statement.

Denotes the trigger event. The trigger event is the statement that activates the trigger.

If UPDATE is the triggering statement, this clause can include an optional column list. Only updates to any of the specified columns will activate the trigger. If UPDATE is the triggering statement and does not include the optional column list, then any UPDATE on the table will activate the trigger.

Identifies the name of the table where the trigger is defined. A triggering statement that specifies table_name causes the trigger to execute. table_name cannot be the name of a view.

Provides a mechanism for SQL 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. This clause is allowed only if the trigger specifies the FOR EACH ROW clause.

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

INSERT...REFERENCING NEWROW means the triggered action can access values of columns of each row inserted. SQL 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 OLDROW means the triggered action can access the values of columns, before they are changed, of each row updated. SQL 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. SQL 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 values 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.

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.

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.

Controls the execution frequency of the triggered action implemented by java_snippet.

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.

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

Denotes the body of the trigger or 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 that SQL creates and submits to the Java compiler.

The BEGIN and END keywords must be uppercase and on separate lines.

Notes

Triggers can take action on their own table so that they invoke themselves. SQL limits such recursion to five levels.

You can you have multiple triggers on the same table. Multiple UPDATE triggers on the same table must specify different columns. SQL executes all triggers applicable to a given combination of table, trigger event, and action time.

The actions carried out by a trigger can fire another trigger. When this happens, the other trigger's actions execute before the rest of the first trigger finishes executing.

If a constraint and trigger are both invoked by a particular SQL statement, SQL checks constraints first, so any data modification that violates a constraint does not also fire a trigger.

To modify an existing trigger, you must delete it and issue another CREATE TRIGGER statement. You can query the systrigger system table for information about the trigger before you delete it.

The code in java_snippet uses the getValue method of the NEWROW and OLDROW objects. The getValue method is valid on OLDROW before or after an update or delete and NEWROW before or after an update or insert; the setValue method is only valid on NEWROW before an insert or update.

Examples

The following code segment illustrates how to use the CREATE TRIGGER statement:

CREATE TRIGGER TRG_TEST04 BEFORE INSERT ON tst_trg_01
REFERENCING NEWROW
FOR EACH ROW
IMPORT
import java.sql.*;
BEGIN
//Inserting Into tst_trg_03
Integer new_value=newInteger(0);
new_value=(Integer)NEWROW.getValue (1,INTEGER);
SQLIStatement insert_tst3=new SQLIStatement ("INSERT INTO tst_trg_03 values (?)");
insert_tst3.setParam (1,new_value);
insert_tst3.execute();
END

The following code segment illustrates how to set values for a new row in the CREATE TRIGGER statement:

CREATE TRIGGER trg1403
BEFORE INSERT ON tbl1401
REFERENCING NEWROW
FOR EACH ROW
IMPORT
import java.sql.* ;
BEGIN
INTEGER n2 = new INTEGER(12345);
NEWROW.setValue(2, n2);
END

For more information on creating and using triggers, see OpenEdge Data Management: SQL Development.