MySQL Forums
Forum List  »  Triggers

Trigger on ndb table does not always work as expected
Posted by: Philip Luppens
Date: September 15, 2006 05:31AM

Hi there,

I seem to have hit a problem when I use a trigger to update some data in another table. The triggers are defined to update the number of child objects in a one-to-many relationship, so if a child row gets inserted, the column value in a parent row that keeps track of the count of the child rows gets increased by 1. When I delete a child row, the parent's count decreases by 1. I hope you understand what I mean.

It does work when I delete a child row by another column (eg. WHERE name='foo'). But, when I delete a child row by its primary key (BIGINT(20) auto_increment), the count doesn't change.
This is quite funny, since it does work on an InnoDB table, but not on an NDBCluster type.

I guess I'm doing something wrong, but I can't seem to find out why. Is this a known limitation ? Is there something I forgot ?

Version: 5.0.24 (cluster setup, 2 data nodes, 2 api nodes, 1 management server).

Here's the code from the trigger:
----------------------------------

DELIMITER //

create trigger `Catalog_ItemImage_DELETE` BEFORE DELETE on `Catalog_ItemImage`
for each row BEGIN
IF OLD.item IS NOT NULL THEN
UPDATE Catalog_Item SET numberOfImagesCounter = numberOfImagesCounter - 1 WHERE Catalog_Item.id = OLD.item;
END IF;
END;
//

DELIMITER ;
----------------------------------

The UPDATE trigger (which handles the count when a child row changes parent) does work with both primary keys and other columns, so I'm a bit at loss here about what's going on. Any insights would be greatly appreciated.

Cheers,

Phil



Edited 1 time(s). Last edit at 09/15/2006 05:33AM by Philip Luppens.

Options: ReplyQuote


Subject
Views
Written By
Posted
Trigger on ndb table does not always work as expected
2091
September 15, 2006 05:31AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.