Consultor Eletrônico

Status: Unverified

GOAL:

SQL: Sample JAVA CREATE TRIGGER BEFORE INSERT REFERENCING NEWROW.

GOAL:

How to assign the SYSTIMESTAMP Function value to a character column on the CREATE event of a new record?

GOAL:

How to use a query against the sysprogress.systables to return one value of SYSTIMESTAMP function?

GOAL:

How to create a JAVA CREATE TRIGGER that assigns a value to a field in the newly created record?

FACT(s) (Environment):

All Supported Operating Systems
OpenEdge 10.x

FIX:

The following code demonstrates:
1. How to create a JAVA SQL CREATE trigger.
2. How to use a query against the sysprogress.systables to generate one value of the SYSTIMESTAMP function.
3. How to use a JAVA CREATE TRIGGER to assigns a value to a field in the newly created record.
Notice that the filed receiving the value in this example is a character field whose order is 3 in the field list generated when we use SELECT * is executed against the table named Events:

DROP TRIGGER InsertEventsTrigger;
CREATE TRIGGER InsertEventsTrigger
BEFORE INSERT
ON pub.Events
REFERENCING NEWROW
FOR EACH ROW
IMPORT
import java.sql.*;
BEGIN
String cTimeStamp = new String();
SQLCursor sqlc = new SQLCursor ("SELECT SYSTIMESTAMP FROM sysprogress.systables WHERE id = 1");
sqlc.open();
sqlc.fetch();
if (sqlc.found () ){
cTimeStamp = (String) sqlc.getValue(1,CHAR);
}
sqlc.close();
NEWROW.setValue(3,cTimeStamp);
END;
COMMIT WORK;