db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael Oliver <mikeolive...@gmail.com>
Subject DB2 Trigger equivalent in Derby
Date Mon, 21 Nov 2005 18:13:14 GMT
Hi,

I have a DB2 Trigger as follows:

CREATE TRIGGER URI_TRG NO CASCADE BEFORE INSERT ON URI
referencing NEW AS newrow FOR EACH ROW MODE DB2SQL
SET newrow.URI_ID = COALESCE((SELECT MAX(URI_ID) FROM URI) + 1, 1);

Which works fine in DB2 but throws an error
ERROR 42X01: Syntax error: Encountered "SET" at line 1, column 107.

When I try to create the trigger in Derby.

I need URI_ID to be a unique number ascending as new rows are added to the
table.

I have read in the archives about how a BEFORE INSERT cannot modify the new
row. Aside from what else would you want a BEFORE trigger to do except
modify the data being inserted, but that doesn't matter eventually it will
get sorted out.

What I need is a workaround. Because the application can run on multiple
databases I would just as soon not have to add code or do multiple round
trips.

I thought about putting a random number generator on the JDBC adapter for
these kinds of fields instead of 0 which would not be unique in a multi user
world, but then change the trigger from a BEFORE insert to an AFTER INSERT
and do an update to set the ID.

Thanks in advance for any ideas.

Ollie

Mime
View raw message