Newbie blues: Derby Equivalent of MySql queries

[Aneez Backer <an...@yahoo.com>]

Hi Bernt

This is regarding the Derby equivalent of MySQL Trigger statements

The statements you sent worked all right with Derby 10.3.1.4
But, When I try to run them on Derby 10.2.1.6, it gives the following error

ERROR 42X01: Syntax error: Encountered "UPDATE" at line 5, column 1.

Isn't update a part of the 10.2.1.6 SQL syntax ??

The MySql Trrigers and their derby equivalent for Derby 10.3.1.4 are as follows
-------------------------------------
 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 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;
 
CREATE TRIGGER TrignameB
AFTER INSERT ON TABLEA
FOR EACH ROW BEGIN
UPDATE TABLEB SET TAGCOUNT=(TAGCOUNT+1) WHERE ID=NEW.TAG_ID;
END|
DELIMITER ;

-------------------------------------


Today, I was informed that I was supposed to work on derby 10.2.1.6 and not the latest :(   . 

Thanks
Aneez


"Bernt M. Johnsen" <Be...@Sun.COM> wrote: Hi,

>>>>>>>>>>>> Aneez Backer wrote (2007-11-21 20:05:01):
> Thanks Bernt.
> 
> That helped

Just a small question: Is there some special (e.g. performance)
requirements that force you to maintain the data in TABLEB? Since

SELECT TAGCOUNT FROM TABLEB WHERE ID=xxx;

should give the same result as

SELECT COUNT(TAG_ID) FROM TABLEA WHERE TAG_ID=xxx;

you could perhaps do without the triggers and TABLEB?
-- 
Bernt Marius Johnsen, Database Technology Group, 
Staff Engineer, Derby/Java DB
Sun Microsystems, Trondheim, Norway



            














       
---------------------------------
Be a better sports nut! Let your teams follow you with Yahoo Mobile. Try it now.

Re: Newbie blues: Derby Equivalent of MySql queries

[Oystein Grovlen - Sun Norway <Oy...@Sun.COM>]

Aneez Backer wrote:
> Hi Bernt
> 
> This is regarding the Derby equivalent of MySQL Trigger statements
> 
> The statements you sent worked all right with Derby 10.3.1.4
> But, When I try to run them on Derby 10.2.1.6, it gives the following error
> 
> ERROR 42X01: Syntax error: Encountered "UPDATE" at line 5, column 1.
> 
> Isn't update a part of the 10.2.1.6 SQL syntax ??

In 10.2, it was required to include "MODE DB2SQL" in "FOR EACH" clauses. 
     This requirement was removed for 10.3. (Ref. 
http://issues.apache.org/jira/browse/DERBY-1953.) Hence, for 10.2 your 
first statement need to look as follows:

CREATE TRIGGER TrignameA
AFTER DELETE ON TABLEA
REFERENCING OLD AS O
FOR EACH ROW MODE DB2SQL
UPDATE TABLEB SET TAGCOUNT=(TAGCOUNT-1) WHERE ID=O.TAG_ID;

-- 
Øystein