Consultor Eletrônico

Status: Verified

GOAL:

SQL-92: How to create an SQL JAVA INSERT trigger that generates a unique PRIMARY KEY field value for new rows?

GOAL:

How to create an SQL JAVA INSERT trigger?

GOAL:

How to create an SQL table?

GOAL:

How to display the rows of an SQL table?

FACT(s) (Environment):

All Supported Operating Systems
Progress 9.0x
OpenEdge 10.x

FIX:

The following SQL-92 scripts demonstrate how to:
1. Create an SQL-92 table.
2. Create a JAVA INSERT trigger to generates a unique PRIMARY KEY field value for each INSERTed new row.
3. Inserts some records.
4. Display the newly INSERTed records.
1. Create the table:
DROP TABLE pub.test;
CREATE TABLE pub.test
( cName CHAR(10) DEFAULT 's' ,
iNumber INTEGER NOT NULL,
PRIMARY KEY (iNumber)
);
COMMIT;
2. Create a JAVA INSERT trigger to generates a unique PRIMARY KEY field value for each INSERTed new row.

DROP TRIGGER testTigger;
CREATE TRIGGER testTigger
AFTER INSERT ON pub.test
REFERENCING NEWROW
FOR EACH ROW
IMPORT
import java.sql.*;
BEGIN
String strStm = "";
String Up_strStm = "";
Integer iCount = new Integer(0);
Integer iMax = new Integer(0);
int iNewId = 1;
/* Get the Count */
SQLCursor sqlc = new SQLCursor("select count(*) from pub.test");
sqlc.open();
sqlc.fetch();
if ( sqlc.found () ) {
iCount = (Integer) sqlc.getValue(1, INTEGER); }
sqlc.close();
/* Get the current maximum value of the field */
SQLCursor sqld = new SQLCursor("select MAX(iNumber) + 1 from pub.test");
sqld.open();
sqld.fetch();
if ( sqld.found () ) {
iMax = (Integer) sqld.getValue(1, INTEGER); }
sqld.close();
/* Get the New Id Number */
if (iCount.intValue() == 0) {
iNewId = 0;
} else {
iNewId = iMax.intValue();
}
/* Assign the newly generated maximum number to the newly inserted row */
Integer intNumber = (Integer) NEWROW.getValue(2, INTEGER);
String strName = (String) NEWROW.getValue(1,CHAR);
strStm = "UPDATE pub.test SET iNumber = " + iNewId + " WHERE cName = ? and iNumber = ?";
SQLIStatement update_stmt = new SQLIStatement( strStm );
update_stmt.setParam(1 , strName );
update_stmt.setParam(2 , intNumber );
update_stmt.execute();
END
COMMIT;
3. Inserts some records:
INSERT INTO pub.test (cName, iNumber) VALUES ('Tina', 1000) ;
INSERT INTO pub.test (cName, iNumber) VALUES ('Hua', 1000) ;
INSERT INTO pub.test (cName, iNumber) VALUES ('Youssif', 1000) ;
INSERT INTO pub.test (cName, iNumber) VALUES ('Shanshiry', 1000) ;
COMMIT;

4. Display the newly INSERTed records:
SELECT * FROM PUB.test;