Hi Bernt

for the revised queries that you sent:

CREATE TRIGGER TrignameA
AFTER DELETE ON TABLEA
REFERENCING OLD AS O
FOR EACH ROW
UPDATE TABLEB SET TAGCOUNT=(TAGCOUNT-1) WHERE ID=O.TAG_ID;


CREATE TRIGGER TrignameB
AFTER INSERT ON TABLEA
REFERENCING NEW AS N
FOR EACH ROW
UPDATE TABLEB SET TAGCOUNT=(TAGCOUNT+1) WHERE ID=N_TAG_ID;


In the first one, you have written "O.TAG_ID"
and for the second one its "N_TAG_ID"

Which is correct?? the dot operator or the underscore ??


thanks
aneez



"Bernt M. Johnsen" <Bernt.Johnsen@Sun.COM> wrote:
Hi,

BEGIN/END is part of SQL PSM and not supported by Derby. In addition
you can't write NEW.TAG_ID or OLD.TAG_ID but have to use a
"refernecing clause" (See
http://db.apache.org/derby/docs/dev/ref/rrefsqlj89752.html). This as
according to the SQL spec.

In your case, the triggers can be rewritten to be SQL standard
compliant as:

CREATE TRIGGER TrignameA
AFTER DELETE ON TABLEA
REFERENCING OLD AS O
FOR EACH ROW
UPDATE TABLEB SET TAGCOUNT=(TAGCOUNT-1) WHERE ID=O.TAG_ID;

and

CREATE TRIGGER TrignameB
AFTER INSERT ON TABLEA
REFERENCING NEW AS N
FOR EACH ROW
UPDATE TABLEB SET TAGCOUNT=(TAGCOUNT+1) WHERE ID=N_TAG_ID;



>>>>>>>>>>>> Aneez Backer wrote (2007-11-21 02:53:58):
> Hi
>
> Am a newbie to using derby and databases in general.
>
> I need the following MySql Queries to be Derby compatible. Can anyone let me know their Derby equivalent?
>
> --------------------------------------------------
> DELIMITER |
> CREATE TRIGGER TrignameA
> AFTER DELETE ON TABLEA
> FOR EACH ROW BEGIN
> UPDATE TABLEB SET TAGCOUNT=(TAGCOUNT-1) WHERE ID=OLD.TAG_ID;
> END|
>
> CREATE TRIGGER TrignameB
> AFTER INSERT ON TABLEA
> FOR EACH ROW BEGIN
> UPDATE TABLEB SET TAGCOUNT=(TAGCOUNT+1) WHERE ID=NEW.TAG_ID;
> END|
> DELIMITER ;
> --------------------------------------------------
>
> My project involves switching a database application on MySql to Derby. I have been successful in making the slight modifications needed in the SQL queries, but have not been able to find the correct derby equivalents of the above MySql queries.
>
> Thanks
> Aneez
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> ---------------------------------
> Be a better pen pal. Text or chat with friends inside Yahoo! Mail. See how.

--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Derby/Java DB
Sun Microsystems, Trondheim, Norway





Get easy, one-click access to your favorites. Make Yahoo! your homepage.