Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: local policy)
Date: Mon, 02 Jun 2008 06:34:26 -0700
From: Rick Hillegas <Richard.Hillegas@Sun.COM>
Subject: Re: Problems porting trigger from DB2
In-reply-to: <17584753.post@talk.nabble.com>
Sender: Richard.Hillegas@Sun.COM
To: Derby Discussion <derby-user@db.apache.org>
Message-id: <4843F6E2.3050608@sun.com>
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 7BIT
References: <17584753.post@talk.nabble.com>
User-Agent: Thunderbird 2.0.0.14 (Macintosh/20080421)

Hi Carlos,

The fired SET statement does not look like a legal Derby statement to 
me. Maybe you could tackle your problem this way:

1) Declare the Categories table to have an autoid column

2) Then migrate your legacy data into Derby

3) Then use the ALTER TABLE ALTER COLUMN statement to reset the next 
value of the autoid column.

Hope this helps,
-Rick

granbajo wrote:
> Hello,
>
>   I am porting an application from DB2 where I use some triggers to generate
> unique ids (max previous id + 1).  I do not want to use an autoid column in
> Derby because I already have some data that I need to migrate.
>
>   This trigger works in DB2, up to version 8:
>
> CREATE TRIGGER Tests.AutoCategoryID 
>    NO CASCADE BEFORE INSERT ON Tests.Categories
>    REFERENCING NEW AS n
>    FOR EACH ROW MODE DB2SQL
>    SET (n.categoryid) = (SELECT value(MAX(categoryid),0) + 1 FROM
> Test.Categories);
>
>   However, when I run it in Derby, I get the following error:
>
> ERROR 42X01: Syntax error: Encountered "SET" at line 5, column 4.
>
>   Any ideas?
>