Trigger exec when record are delete for a foreing key cascade
I am using mySQL with InnoDB into a new web application.
I design my tables with referential integrity, using foreing keys and, at some moments, the CASCADE as action to update/delete.
The relationship are working ok into update and delete but, the triggers in the child table are not being executed when it are deleted as a action from the father deletion.
The triggers are correctly executed if I execute a UPDATE or a DELETE directly into child table.
At university, I am the only using mySQL intead PostgreSQL so, I think that is the only place to get help.
I am trying finding a solution before give up from mySQL so, any help will be welcome!
-- Table "TPEDI" DDL
CREATE TABLE `TPEDI` (
`RECN` int(10) NOT NULL auto_increment,
`PEDI` int(10) NOT NULL default '0',
`PESS` int(10) NOT NULL default '0',
`DPED` date NOT NULL default '0000-00-00',
`VPED` float(10,2) NOT NULL default '0.00',
PRIMARY KEY (`RECN`)
) ENGINE=InnoDB AUTO_INCREMENT=149 DEFAULT CHARSET=utf8;
-- Table "TPEDIITEM" DDL
CREATE TABLE `TPEDIITEM` (
`RECN` int(10) NOT NULL auto_increment,
`PEDI` int(10) NOT NULL default '0',
`PROD` int(10) NOT NULL default '0',
`QVEN` int(10) NOT NULL default '0',
`VUNI` float(10,2) NOT NULL default '0.00',
PRIMARY KEY (`RECN`),
KEY `FK_PEDI_ITEM` (`PEDI`),
CONSTRAINT `FK_TPEDIITEM_TPEDI` FOREIGN KEY (`PEDI`) REFERENCES `TPEDI` (`RECN`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=314 DEFAULT CHARSET=utf8;
DELIMITER ;;
CREATE TRIGGER `TPEDIITEM_AI` AFTER INSERT ON `TPEDIITEM` FOR EACH ROW BEGIN
UPDATE TPROD SET QEST = QEST - NEW.QVEN WHERE TPROD.RECN = NEW.PROD;
END;;
DELIMITER ;
DELIMITER ;;
CREATE TRIGGER `TPEDIITEM_AU` AFTER UPDATE ON `TPEDIITEM` FOR EACH ROW BEGIN
UPDATE TPROD SET TPROD.QEST = TPROD.QEST + OLD.QVEN WHERE TPROD.RECN = OLD.PROD;
UPDATE TPROD SET TPROD.QEST = TPROD.QEST - NEW.QVEN WHERE TPROD.RECN = NEW.PROD;
END;;
DELIMITER ;
DELIMITER ;;
CREATE TRIGGER `TPEDIITEM_AD` AFTER DELETE ON `TPEDIITEM` FOR EACH ROW BEGIN
UPDATE TPROD SET TPROD.QEST = TPROD.QEST + OLD.QVEN WHERE TPROD.RECN = OLD.PROD;
END;;
DELIMITER ;