Thanks Bernt. That helped
Aneez "Bernt M. Johnsen" <[EMAIL PROTECTED]> 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.
