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:
- 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 Progress SQL-92 Java classes:
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.
EXAMPLEThis example shows an excerpt from a trigger that uses getValue to assign values from both OLDROW and NEWROW objects:
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |