Steve Brett wrote: > 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 ?
Every nextval-call will get a new, unique, consecutive value, no matter if one or more session ask for the next value. Therefore ONE session can never be sure that its multiple nextval-calls will get consecutive values. Another session may have asked for one nextval-value and this will look like a 'hole' in the nextval sequence for the first session. Elke SAP Labs Berlin > > -----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 > _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
