Thanks Bernt.

That helped

Aneez

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


It was a typo. Should of course be N.TAG_ID

>>>>>>>>>>>> Aneez Backer wrote (2007-11-21 05:01:30):
> 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" 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.

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





Never miss a thing. Make Yahoo your homepage.