MySQL Forums
Forum List  »  Triggers

Delete trigger deletes an entire table
Posted by: Omar Crea
Date: March 13, 2012 07:33PM

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  |
+-----------+

Options: ReplyQuote


Subject
Views
Written By
Posted
Delete trigger deletes an entire table
2536
March 13, 2012 07:33PM


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.