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" <Bernt.Johnsen@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.