You might have a look at http://www.sapdb.org/7.4/htmhelp/15/1a2bb4b44d11d2a97100a0c9449261/content.htm (CURRVAL, NEXTVAL ...)
http://www.sapdb.org/7.4/htmhelp/48/0d801bb4f211d2a97100a0c9449261/content.htm (use of SYSKEY) and http://www.sapdb.org/7.4/htmhelp/d0/7638a1d21c11d2a97400a0c9449261/content.htm (use of DEFAULT SERIAL) Cheers Alexander Schr�der SAP DB, SAP Labs Berlin > -----Original Message----- > From: Michael Slinn [mailto:[EMAIL PROTECTED] > Sent: Friday, August 15, 2003 4:55 PM > To: SAPDB General List > Subject: RE: Seqences & Triggers > > > This is good information. It should go into the documentation! > > Mike > > > > Scott, Niall wrote: > > > >> Hi > >> > >> I need to create a autoincrement field but I can't use a fixed data > >> type for reasons I won't go into. I need the field to be type > >> integer. I thought that I would create a sequence then call it in a > >> trigger thus > >> > >> Create table test1( > >> teid integer, > >> aValue Char(10) > >> ) > >> -- > >> create sequence test1seq increment by 1 start with 1 > >> -- > >> CREATE TRIGGER test1_update FOR test1 AFTER INSERT EXECUTE ( > >> UPDATE dbasys.test1 set teid=test1seq.Nextval; > >> ) > >> -- > >> > >> I tried various trigger code but the above was the only one that > >> didn't give errors > >> > >> I then used > >> Insert into test1 (aValue) VALUES('Test 1') > >> but all I get is a null value in teid. > >> > >> I have come to SAP DB from Interbase where the trigger > code would be > >> CREATE TRIGGER test1_update FOR TEST1 > >> ACTIVE BEFORE INSERT POSITION 0 > >> AS > >> BEGIN > >> new.teid=GEN_ID(test1seq,1); > >> END > >> > >> > >> But I can't figure out how to do the same in SAPDB > >> > >> Cheers > > > > Do you really want to update ALL rows of the table if one single row > > is added? Or did you just forgot to add some where-clause > > to your update? > > > > Your trigger update should look like this: > > CREATE TRIGGER test1_update FOR test1 AFTER INSERT EXECUTE ( > > UPDATE dbasys.test1 set teid=test1seq.Nextval > > where syskey = :syskey; > > ) > > > > Your table has no primary key defined, but the implicitly defined > > syskey exists, which can be named in where-clauses and select-lists. > > > > But, why do you use a trigger? > > If your trigger looks really that small (and that is not only a > > shortened example) then your insert can be changed from > > Insert into test1 (aValue) VALUES('Test 1') > > to > > Insert into test1 VALUES(test1seq.Nextval, 'Test 1') > > and you will not need a trigger any more > > > > or to make things even easier (but pay attention to the > > not-updatability of serial-columns), you can define > > teid integer default serial > > and the use > > Insert into test1 (aValue) VALUES('Test 1') > > without triggers. > > > > > > Elke > > SAP Labs Berlin > > > > _______________________________________________ > 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
