RE: Seqences & Triggers

[Scott, Niall]

Title: Message

Hi Elke

 

I am using 7.4.3.

 

What I wish to do is (with the tables below) use

insert into test1 (aValue) ('TEST3')

 

and get a record

 

teid 1

aValue 'TEST3'

 

but I get

teid ?

aValue 'TEST3'

 

I certainly don't want to update the teid in all records but I couldn't find out the correct code for the trigger to do this

When create new record set teid to test1seq.Nextval

 

Cheers

Niall

-----Original Message-----
From: Zabach, Elke [mailto:[EMAIL PROTECTED]
Sent: 14 August 2003 16:34
To: Scott, Niall; 'Sapdb. General'
Subject: 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
 

Mhm, with my 7.4.3 this works perfectly.

1. which version do you use?

2. please check the errorcode after the update. We have to assume

    that this update returns some error --> does not change any value

3. 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?

 

Elke

SAP Labs Berlin 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 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. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~