Kbase P161888: How to update the custNum field when records are inserted via SQL92 client?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
15/03/2010 |
|
Status: Unverified
GOAL:
How to update the custNum field when records are inserted via SQL92 client?
GOAL:
Sample CREATE trigger to update the custNum field?
GOAL:
How setValue works in SQL92?
GOAL:
Can a SQl92 trigger update fields in the record?
FACT(s) (Environment):
OpenEgde 10.x
All Supported Operating Systems
FIX:
The following example shows how ta add a SQL92 CREATE trigger for PUB.Customer table which updates the custNum field
with the next NextCustNum sequence value.
Run the script in SQLExplorer with any user name but sysprogress. Do not add the CREATE trigger under PUB schema name.
-- sample.sql BEGIN
-- SQL92 sample script for adding a create trigger in sports2000 database
DROP TRIGGER custInsert;
commit;
CREATE TRIGGER custInsert
BEFORE INSERT ON PUB.Customer
REFERENCING NEWROW
FOR EACH ROW
IMPORT
import java.sql.*;
BEGIN
SQLCursor st;
Integer pk;
st = new SQLCursor("select pub.nextcustnum.NEXTVAL FROM sysprogress.syscalctable");
st.open();
st.fetch();
if(st.found())
{
pk = (Integer)st.getValue(1, INTEGER);
NEWROW.setValue(1, pk);
}
st.close();
END;
-- required at end
COMMIT;
insert into PUB.Customer (name) values ('gigi');
commit;
SELECT custNum, name FROM PUB.Customer WHERE name = 'gigi';
-- sample.sql END