can't you do this by setting the type of the key to serial and the default to serial (in sql manager) and then not specifying the key in the update ?
i seem to remeber this is the way i did it and it worked fine ? also a quick question ... are sequences session safe ? i.e. if you have multiple inserts in transaction blocks and each requests a nextval do they get consecutive values ? Steve > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: 25 April 2002 07:38 > To: [EMAIL PROTECTED] > Subject: autoincrementing sequences with triggers... > > > 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 > _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
