Kbase 21690: HowTo auto-increment a field in SQL-92 after adding a record
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
09/10/2003 |
|
Status: Unverified
GOAL:
How to auto increment an integer primary key field in SQL 92 after inserting a record into a Progress database.
FIX:
In order to create an auto-increment field, you must use a combination of sequences and database triggers. When working with the SQL-92 engine, the 4GL-based Progress database triggers cannot be invoked. Therefore, equivalent database triggers need to be written using Java with the intention of accomplishing the same functionality on the SQL-92 side. The sample code placed below in the text demonstrates to you how the customer number field (custNum) of the customer table from the sports2000 database can be auto increased in value
after inserting a record into that database table through an SQL-92 command
(i.e.: INSERT INTO pub.customer (name,contact) VALUES ('Progress Software','John Smith');).
CREATE TRIGGER increaseCustNum
AFTER INSERT ON pub.customer
IMPORT
import java.sql.*;
BEGIN
Integer max = new Integer(0);
SQLCursor sqlc = new SQLCursor("SELECT MAX(custNum) FROM pub.customer");
sqlc.open();
sqlc.fetch();
if ( sqlc.found () ) {
max = (Integer) sqlc.getValue(1, INTEGER);
}
sqlc.close();
int maxv = 0;
maxv = max.intValue();
maxv ++;
String stmt = "UPDATE pub.customer SET custNum = " + maxv + " WHERE custNum = 0 ";
SQLIStatement update_stmt = new SQLIStatement( stmt );
update_stmt.execute();
END;