Delete trigger deletes an entire table

[Omar Crea]

Hi all,

I have some processes in a Process table, linked with
two other tables, MatPackProcess and ProcFamClass.
I want to delete a record from the Process table only
if the process has no record in the MackPackProcess table.
Instead, records in the ProcFamClass table must be deleted,
even if there is a foreign key that avoid it.

Actually, if I try to delete a record of the Process table, I obtain:

mysql> delete from Process where proc_id = 3;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key 
constraint fails (`test_mysql_trigger`.`ProcFamClass`, CONSTRAINT 
`fk_ProcFamClass_Proc1` FOREIGN KEY (`proc_id`) REFERENCES 
`Process` (`proc_id`) ON DELETE NO ACTION ON UPDATE NO ACTION)

I created a trigger to handle this situation I first tried with the
following syntax:

mysql> delimiter //
mysql> create trigger proc_tr_del before delete on Process
    -> for each row
    -> begin
    -> delete from ProcFamClass pfc where pfc.proc_id = proc_id;
    -> end;//
ERROR 1064 (42000): You have an error in your SQL syntax; check 
the manual that corresponds to your MySQL server version for 
the right syntax to use near 'pfc where pfc.proc_id = proc_id;
end' at line 4

I fixed the original trigger:

mysql> create trigger proc_tr_del before delete on Process
    -> for each row
    -> begin
    -> delete from ProcFamClass where ProcFamClass.proc_id = proc_id;
    -> end;//
Query OK, 0 rows affected (0.06 sec)

mysql> delimiter ;

Now, trying to delete a process does not give errors:

mysql> delete from Process where proc_id = 3;
Query OK, 1 row affected (0.04 sec)

but it erase all records in the ProcFamClass table!

mysql> select * from ProcFamClass;
Empty set (0.00 sec)

How can I correctly handle this situation? Thanks in advance.


Involved tables:

mysql> show create table Process \G
*************************** 1. row ***************************
       Table: Process
Create Table: CREATE TABLE `Process` (
  `proc_id` int(11) NOT NULL AUTO_INCREMENT,
  `nome` varchar(45) NOT NULL,
  PRIMARY KEY (`proc_id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8


mysql> show create table MatPackProcess \G
*************************** 1. row ***************************
       Table: MatPackProcess
Create Table: CREATE TABLE `MatPackProcess` (
  `mat_id` int(11) NOT NULL,
  `pack_id` int(11) NOT NULL,
  `proc_id` int(11) NOT NULL,
  PRIMARY KEY (`mat_id`,`pack_id`,`proc_id`),
  KEY `fk_Pack1` (`pack_id`),
  KEY `fk_Mat1` (`mat_id`),
  KEY `fk_Proc1` (`proc_id`),
  CONSTRAINT `fk_MatPackProcess_Proc1` FOREIGN KEY (`proc_id`) 
REFERENCES `Process` (`proc_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8


mysql> show create table ProcFamClass \G
*************************** 1. row ***************************
       Table: ProcFamClass
Create Table: CREATE TABLE `ProcFamClass` (
  `proc_id` int(11) NOT NULL,
  `class_id` int(11) NOT NULL,
  `fam_id` int(11) NOT NULL,
  PRIMARY KEY (`proc_id`,`class_id`,`fam_id`),
  KEY `fk_Class1` (`class_id`),
  KEY `fk_Proc1` (`proc_id`),
  KEY `fk_Fam1` (`fam_id`),
  CONSTRAINT `fk_ProcFamClass_Proc1` FOREIGN KEY (`proc_id`) 
REFERENCES `Process` (`proc_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Records:

insert into Process values(9,'Fusion');
insert into Process values(3,'Plastic');
insert into MatPackProcess values(24,15,9);
insert into MatPackProcess values(24,62,9);
insert into MatPackProcess values(24,110,9);
insert into MatPackProcess values(25,13,9);
insert into ProcFamClass values(9,1,6);
insert into ProcFamClass values(3,3,999);

MySQL version:

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.58-1  |
+-----------+