How can I get the sequence after an insert

[lalomx2000]

Hi,

how can I get the sequence after an insert, I need it to insert it in details records, something like the scope_identity function in SQL Server. Is this the usual way or there is a better in Progress? I'm using OpenEdge 10.1A, connected via ODBC.

 

[JamesBowen]

Here is an example of our SQL Trigger for insert:

SET SCHEMA 'PUB';
DROP TRIGGER CONTACTS_INS;
CREATE TRIGGER CONTACTS_INS
AFTER INSERT ON PUB.CONTACTS
REFERENCING NEWROW
FOR EACH ROW
IMPORT
import java.sql.*;
BEGIN

  String syncGUID;
  Integer idMembers;
  Timestamp ts;

  syncGUID  = (String) NEWROW.getValue(13, CHARACTER);
  idMembers = (Integer) NEWROW.getValue(2, INTEGER);
  ts        = new Timestamp(System.currentTimeMillis());

  SQLIStatement updateStatement = new SQLIStatement(
    "UPDATE PUB.CONTACTS SET ID_Contacts=[B]pub.ID_Contacts.NEXTVAL[/B], audit_user=USER, Audit_Stamp=? WHERE syncGUID=? AND ID_Members=?"
    );
    
  updateStatement.setParam(1, ts);
  updateStatement.setParam(2, syncGUID);
  updateStatement.setParam(3, idMembers);
  
  updateStatement.execute();

END;
COMMIT;

NOte: If using the JDBC driver make sure your classpath includes openedge.jar, base.jar and util.jar when the database is started. Without these the trigger(s) will not execute.