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 12:37:43 GMT
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

Mime
View raw message