Try OpenEdge Now
skip to main content
SQL Development
Stored Procedures and Triggers : Working with triggers : getValue method for NEWROW and OLDROW
 

getValue method for NEWROW and OLDROW

The getValue method of the NEWROW and OLDROW classes assigns a single value from a SQL statement object (from the SQL statement that made the trigger fire) to a procedure variable or a new SQL statement parameter using the following syntax:
isNULL ( col_num , sql_data_type ) ;
col_num
Specifies the desired column of the result set as integer. getValue retrieves the value in the currently fetched record of the column denoted by col_num. 1 denotes the first column of the result set, 2 denotes the second, n denotes the nth.
The following example shows a procedure using the is.NULL method. The is.NULL method is used to check for a null value.
if (!NEWROW.isNULL(1))
callStmt.setParam(1, (Integer) NEWROW.getValue(1, INTEGER));
if (!NEWROW.isNULL(2))
callStmt.setParam(2, (String) NEWROW.getValue(2, VARCHAR));
if (!NEWROW.isNULL(3))
callStmt.setParam(3, (java.math.BigDecimal) NEWROW.getValue(3, DECIMAL))
The following 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 = ?"
);
.
.
.