MySQL Forums
Forum List  »  Triggers

issues with triggers
Posted by: Juanito Juanito
Date: May 26, 2014 11:09AM

Hi,
I have the following (recursive) table, and I want to apply a triger on it.
Please note that area_pai is the portuguese translation of area_father, and nivel the translation of level.

CREATE TABLE `area` (

`area_id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`area_pai` int(10) unsigned DEFAULT '0',

`nome` varchar(45) NOT NULL,

`nivel` mediumint(8) unsigned DEFAULT '0',

PRIMARY KEY (`area_id`),

UNIQUE KEY `unique` (`area_pai`,`nome`),

KEY `area-area_fk_idx` (`area_pai`),

CONSTRAINT `area-area_fk` FOREIGN KEY (`area_pai`) REFERENCES `area` (`area_id`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8


The after insert trigger finds the level of the parent area and increases the value by 1; this new value is seat as the level of the inserted row.

USE `parque_novo_indice`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` TRIGGER `area_AUPD` AFTER UPDATE ON `area` FOR EACH ROW

begin
declare nivelvar mediumint unsigned;
select nivel into @nivelvar from area where area.area_id = New.area_pai;


set @nivelvar = @nivelvar + 1;

update area
set nivel=@nivelvar;

end

The problem, is that when I run the statement:

UPDATE `parque_novo_indice`.`area` SET `area_pai`='1' WHERE `area_id`='1';

I get the erro message:

ERROR 1442: 1442: Can't update table 'area' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

SQL Statement:

UPDATE `parque_novo_indice`.`area` SET `area_pai`='1' WHERE `area_id`='1'


This is not the first time I have this type of issues on the area table since I also tried to create others triggers to control the hierarchy of the recursive table area.
Any help would be much appreciated!!
Juanito

Options: ReplyQuote


Subject
Views
Written By
Posted
issues with triggers
2762
May 26, 2014 11:09AM
1148
May 26, 2014 11:16AM
1140
May 28, 2014 10:27AM
1226
May 27, 2014 03:47PM


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.