FairCom Java & .NET Stored Procedures

OLDROW and NEWROW Objects: Passing Values to Triggers

The OLDROW and NEWROW objects provide a mechanism for FairCom DB SQL to pass row values as input parameters to the stored procedure in a trigger that executes once for each affected row. If the CREATE TRIGGER statement contains the REFERENCING clause, FairCom DB SQL implicitly instantiates an OLDROW or NEWROW object (or both, depending on the arguments to the REFERENCING clause) when it creates the Java class.

This allows the Java code in the snippet to use the getValue() method of those objects to retrieve values of columns in rows affected by the trigger event and store them in procedure variables:

• The OLDROW object contains values of a row as it exists in the database before an update or delete operation. It is instantiated when triggers specify an UPDATE...REFERENCING OLDROW or DELETE...REFERENCING OLDROW clause. It is meaningless and not available for insert operations.
• The NEWROW object contains values of a row as specified in an INSERT or UPDATE statement. It is instantiated when triggers specify an UPDATE...REFERENCING NEWROW or INSERT...REFERENCING NEWROW clause. It is meaningless and not available for delete operations.

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

Triggers use the OLDROW.getValue() and NEWROW.getValue() methods to assign a value from a row being modified to a procedure variable. The format and arguments for getValue() are the same as in other c-treeSQL Java classes:

getValue ( col_num , data type ) ;

• col_num is an integer that specifies which column affected row is of interest. getValue() retrieves the value in the column denoted by col_num (1 denotes the first column of the result set, 2 denotes the second, and so on).
• data_type specifies the required FairCom DB SQL type of the returned column value (see Implicit Data Type Conversion Between SQL and Java Types for details on how FairCom DB SQL data types map to Java data types).

The following example shows an excerpt from a trigger that uses getValue() to assign values from both OLDROW and NEWROW objects.

Using getValue() to Process Row Values within Triggers

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.compareTo("OPEN") == 0) &&

(new_status.compareTo("FIXED") == 0))

{

// 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();

}

}

catch(DhSQLException e)

{throw e;}

END