Just a followup to my posting a few days back on getting sequences to update as part of a trigger...I have a satisfactory solution to my problem.
Several people have wondered why this would be useful... For clients like Microsoft Access, VBA(and pretty well any other ODBC compliant tool that can create a client side cursor as part of a forms based entry screen) you get the interface to perform the insert for you, but unless the key field is autoincrementing you have to write client side code to support the insert. With this trigger and the sapdb ODBC driver, you get the equivalent of an autoincrementing key field, and you don't have to write switch statements for each different backend in your interface code just to do something simple like insert a record. Elke very kindly pointed out ( as did Stefan from this list ) that a key field test with a NULL would never return true because all SAPDB triggers fire AFTER the INSERT . By altering the CREATE to have a DEFAULT clause of 0 for new KEY fields , you can transform it to a valid sequence.nextval as part of the trigger using an update statement. Thanks for all the help ... I appreciate it. CREATE TABLE TEST.ADDRESSES ( ADDRESSID INTEGER DEFAULT 0 PRIMARY KEY , ADDRESSTYPE INTEGER) // CREATE SEQUENCE TEST.ADDRESSIDSEQ START WITH 1 INCREMENT BY 1 NOMAXVALUE CACHE 10 // CREATE TRIGGER triggeraddress FOR TEST.ADDRESSES AFTER INSERT EXECUTE ( BEGIN UPDATE TEST.ADDRESSES SET ADDRESSID = TEST.ADDRESSIDSEQ.NEXTVAL WHERE ADDRESSID = 0; END; ) JStauft _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
