RE: Seqences & Triggers

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