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
pgpLpJmEx4GcM.pgp
Description: PGP signature
