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