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?