Consultor Eletrônico

Status: Verified

GOAL:

How to create a increasing ID for each inserted record with SQL-92 trigger

GOAL:

How to create a SQL trigger that uses a sequence to assign to fields in a record

FACT(s) (Environment):

All Supported Operating Systems
Progress 9.x
OpenEdge 10.x

FIX:

This example uses a Sequence.


// First DROP the trigger then recreate it
DROP TRIGGER <triggername>;
CREATE TRIGGER <triggername> BEFORE INSERT ON pub.<tablename>

// REFERENCING gives access to the newly created row
REFERENCING NEWROW
FOR EACH ROW

IMPORT
import java.sql.*;
BEGIN
// Define an Integer variable to store the sequence value
Integer iSequenceID = new Integer(0);

// Use a SELECT statement to execute the NEXTVAL function on the sequence
// Choose a record in a table that you know is unique so that you don't end up executing NEXTVAL more than once
// Note that the trigger name is always stored as uppercase and is case-sensitive
SQLCursor sqlc = new SQLCursor ( "SELECT pub.<sequencename>.NEXTVAL FROM pub.\"_Systrigger\" WHERE \"_Triggername\" = '<triggername>' ");
sqlc.open();
sqlc.fetch();

if ( sqlc.found () ) {
iSequenceID = (Integer) sqlc.getValue(1, INTEGER);
}

sqlc.close();
// The number 15 below represents the field's position in the table relative to order
NEWROW.setValue(15, iSequenceID);
END;
COMMIT WORK;