Consultor Eletrônico

Status: Verified

GOAL:

SQL: How to auto-increment a newly created record's identity field?

GOAL:

How to modify the NEWROW object in a BEFORE INSERT TRIGGER?

GOAL:

How use the BEFORE INSERT TRIGGER to assign the NEWROW fields?

GOAL:

How to assign a sequence value to the identity column of a newly created record?

FACT(s) (Environment):

All Supported Operating Systems
OpenEdge 10.x

FIX:

Before OpenEdge 10.x, Progress SQL-92 version 9.x did not offer the capability of modifying the NEWROW object in a BEFORE INSERT TRIGGER. The following steps demonstrate how to use this OpenEdge 10.x capability to write a BEFORE INSERT TRIGGER that auto increments a field. This examples uses the CustNum field of the Customer table in the sports2000 demo database:
1. Use the prodb command to create a copy of the Sports2000 demo database.
prodb sports2000 sports2000
2. Use the data dictionary to add a dummy table with one dummy field.
pro sports2000 -1 -p _dict.p
3. Use the following 4GL statement to create ONE record in the dummy table.
CREATE dummy.
4. Set the SQL-92 environment variables by executing the command:
sql_env
5. Serve the database (Change the Host name to your own):
proserve sports2000 -H pcyshanshi -N tcp -S 9999
6. Use the SQL Explorer Tool to create a BEFORE INSERT trigger to auto increment the customer number by executing the following SQL-92 code:
DROP TRIGGER InsertCustomerTrigger;
CREATE TRIGGER InsertCustomerTrigger
BEFORE INSERT ON pub.customer
REFERENCING NEWROW
FOR EACH ROW
IMPORT
import java.sql.*;
BEGIN
Integer iCustNum = new Integer(0);
SQLCursor sqlc = new SQLCursor ( "SELECT PUB.NextCustNum.NEXTVAL FROM PUB.DUMMY");
sqlc.open();
sqlc.fetch();
if ( sqlc.found () ) {
iCustNum = (Integer) sqlc.getValue(1, INTEGER);
}
sqlc.close();
NEWROW.setValue(1, iCustNum);
END;
COMMIT WORK;
7. Use the SQL Explorer Tool to create three test records in the Customer table by executing the following SQL-92 code:
INSERT INTO PUB.Customer (Name) VALUES ('First Customer Inserted');
INSERT INTO PUB.Customer (Name) VALUES ('Second Customer Inserted');
INSERT INTO PUB.Customer (Name) VALUES ('Third Customer Inserted');
COMMIT WORK;
8. Use the SQL Explorer Tool to execute the following code that confirms that the CustNum Fields of these newly created records have been auto incremented using the NextCustNum sequence.
SELECT
CustNum, Name
FROM
PUB.Customer
WHERE
Name = 'First Customer Inserted'
OR
Name = 'Second Customer Inserted'
OR
Name = 'Third Customer Inserted';