Progress
SQL-92
Guide and Reference


OLDROW and NEWROW Objects: Passing Values to Triggers

The OLDROW and NEWROW objects allow 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, the SQL server 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:

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 Progress SQL-92 Java classes:

SYNTAX
getValue ( col_num , sql_data_type ) ; 

col_num

Specifes the integer column number of the affected row. getValue retrieves the value in the column denoted by col_num. ’1’ denotes the first column of the result set, ’2’ denotes the second, n denotes the nth.

sql_data_type

Specifies the corresponding SQL data type. For a complete list of appropriate data types, refer to Table 5–2.

EXAMPLE

This example shows an excerpt from a trigger that uses getValue to assign values from both OLDROW and NEWROW objects:

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 (
     " update BUG_STATUS set bugs_fixed_cnt = bugs_fixed_cnt + 1 "
     " where  month = ?  and year = ?"
     );
          .
          .
          . 


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095