Kbase P83827: SQL-92: How to make a trigger increment a field when inserting values?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/16/2008 |
|
Status: Unverified
GOAL:
SQL-92: How to use REFERENCING NEWROW in a trigger?
GOAL:
How to make a trigger to increment a field when inserting values.
GOAL:
SQL-92, how to increment a field value when a new value is inserted.
FACT(s) (Environment):
Progress 9.1x
FIX:
To try this example please follow these steps:
1. Start a copy of the sports2000 Database
2. Connect with Progress Explorer
3. From SQL Explorer, compile the Trigger code below sports2000 DB
4. Try to insert a new row in the pub.customer table as follows :
insert into pub.customer (name) values('Test1');
5. Check if the value of "custnum" has been incremented by doing SELECT on pub.customer table.
The Trigger Code:
----------------------
DROP TRIGGER increaseCustNum;
CREATE TRIGGER increaseCustNum
AFTER INSERT ON pub.customer
IMPORT
import java.sql.*;
BEGIN
Integer max = new Integer(0);
SQLCursor sqlc = new SQLCursor("SELECT pub.nextcustnum.nextval FROM sysprogress.sysdbauth");
sqlc.open();
sqlc.fetch();
if ( sqlc.found () ) {
max = (Integer) sqlc.getValue(1, INTEGER); }
sqlc.close();
int maxv = 0;
maxv = max.intValue();
String stmt = "UPDATE pub.customer SET custNum = " + maxv + " WHERE custNum = 0"; SQLIStatement update_stmt = new SQLIStatement( stmt ); update_stmt.execute();
END;
commit;