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 (asf.osuosl.org: domain of mikeoliveraz@gmail.com
 designates 64.233.162.197 as permitted sender)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
        s=beta; d=gmail.com;
        h=received:message-id:date:from:to:subject:mime-version:content-type;
        b=CVOnPAJBRHKzwmk+QwoUnkMVaILsopV3Bc4/iOc49LmQtWG5gS9shag162ak17RBkpruoX6swEu0BFIe8J+QGaQOMLqe1H+rApyK7odpI88wywAgvMXfui2lrbsYskFI8j44S/6KvS+7MPYs+c7FF6WRUDQ5JaU6FxXfKFrrEHI=
Message-ID: <84c8f8d70511211013w330b1e38tef0bfe1583265f14@mail.gmail.com>
Date: Mon, 21 Nov 2005 10:13:14 -0800
From: Michael Oliver <mikeoliveraz@gmail.com>
To: derby-user@db.apache.org
Subject: DB2 Trigger equivalent in Derby
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_34227_25715766.1132596794032"

------=_Part_34227_25715766.1132596794032
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

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 =3D 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 use=
r
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

------=_Part_34227_25715766.1132596794032
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi,<br>
<br>
I have a DB2 Trigger as follows:<br>
<br>
CREATE TRIGGER URI_TRG NO CASCADE BEFORE INSERT ON URI <br>
referencing NEW AS newrow FOR EACH ROW MODE DB2SQL <br>
SET newrow.URI_ID =3D COALESCE((SELECT MAX(URI_ID) FROM URI) + 1, 1);<br>
<br>
Which works fine in DB2 but throws an error <br>
ERROR 42X01: Syntax error: Encountered &quot;SET&quot; at line 1, column 10=
7.<br>
<br>
When I try to create the trigger in Derby.<br>
<br>
I need URI_ID to be a unique number ascending as new rows are added to the =
table.<br>
<br>
I have read in the archives about how a BEFORE INSERT cannot modify the
new row.&nbsp; 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.<br>
<br>
What I need is a workaround.&nbsp; Because the application can run on
multiple databases I would just as soon not have to add code or do
multiple round trips.<br>
<br>
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.&nbsp; <br>
<br>
Thanks in advance for any ideas.<br>
<br>
Ollie<br>


------=_Part_34227_25715766.1132596794032--