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" <[EMAIL PROTECTED]> 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.
