db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernt M. Johnsen" <Bernt.John...@Sun.COM>
Subject Re: Derby Equivalent of MySql queries
Date Wed, 21 Nov 2007 13:16:06 GMT
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" <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.

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

Mime
View raw message