Seqences & Triggers

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
 
Niall R Scott
IS Support
Cameron Controls
Aberdeen
[email protected] <mailto:[email protected]> 
 
 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
This e-mail is confidential, may contain proprietary information
of the Cooper Cameron Corporation and its operating Divisions
and may be confidential or privileged.

This e-mail should be read, copied, disseminated and/or used only
by the addressee. If you have received this message in error please
delete it, together with any attachments, from your system.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~