Like a stored procedure, a trigger has a specification and a body.
The body of a trigger is the same as that of a stored procedure: BEGIN and END delimiters enclosing a Java snippet. The Java code in the snippet defines the triggered action that executes when the trigger is fired. As with stored procedures, when it processes a CREATE TRIGGER statement, FairCom DB SQL adds wrapper code to create a Java class and method that is invoked when the trigger is fired.
The trigger specification, however, is different from a stored procedure specification. It contains the following elements:
The CREATE clause specifies the name of the trigger. FairCom DB SQL stores the CREATE TRIGGER statement in the database under trigname. It also uses trigname in the name of the Java class that FairCom DB SQL declares to wrap around the Java snippet. The class name uses the format username_trigname_TP, where username is the user name of the database connection that issued the CREATE TRIGGER statement.
The BEFORE or AFTER keywords specify the trigger action time: whether the triggered action implemented by java_snippet executes before or after the triggering INSERT, UPDATE, or DELETE statement.
The INSERT, DELETE, or UPDATE keyword specifies the trigger event: which data modification command activates the trigger. 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.
The ON table_name clause specifies the trigger table: the table for which the specified trigger event activates the trigger. The ON clause cannot specify a view or a remote table.
The optional REFERENCING clause is allowed only if the trigger also specifies the FOR EACH ROW clause. It provides a mechanism for FairCom DB 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. See OLDROW and NEWROW Objects: Passing Values to Triggers for details.
The FOR EACH clause specifies 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.
The IMPORT clause is the same as in stored procedures. It specifies standard Java classes to import.
The following example shows the elements of a trigger.
