RE: Seqences & Triggers

[Zabach, Elke]

Title: Message

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