MySQL Forums
Forum List  »  Triggers

Trigger on reflexive table
Posted by: Jean-Marie DUCHESNE
Date: December 07, 2013 01:59AM

Hello,

I start on mySQL database and I want to use a trigger to populate a foreign key in a reflexive table.
The code of my table is :

CREATE TABLE IF NOT EXISTS `gestion`.`action` (
`act_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
`act_action_origine` INT(10) UNSIGNED NULL ,
`act_creation` INT UNSIGNED NOT NULL DEFAULT 1 ,
`act_modification` INT UNSIGNED NULL ,
`act_statut` ENUM('Valide','Supprimée') CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT 'Valide' ,
`act_type` ENUM('Interne','Externe') CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT 'Interne' ,
`act_document_source` INT UNSIGNED NULL ,
`act_detail` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL DEFAULT 'Essai' ,
`act_responsable` INT UNSIGNED NULL ,
`act_delai` DATETIME NULL DEFAULT NOW() ,
`act_relance_mail` TINYINT(1) NULL DEFAULT 0 ,
`act_priorite` ENUM('Basse','Moyenne','Haute') CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT 'Basse' ,
`act_avancement` ENUM('Plannifiée','Réalisée','Vérifiée','Soldée') CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT 'Plannifiée' ,
`act_cloture` DATETIME NULL DEFAULT NULL ,
`act_document_cloture` INT UNSIGNED NULL DEFAULT NULL ,
`act_efficacite` TEXT CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL ,
PRIMARY KEY (`act_id`) ,
INDEX `fk_action_action_idx` (`act_action_origine` ASC) ,
INDEX `fk_action_creation_idx` (`act_creation` ASC) ,
INDEX `fk_action_modificaton_idx` (`act_modification` ASC) ,
CONSTRAINT `fk_action_action`
FOREIGN KEY (`act_action_origine` )
REFERENCES `gestion`.`action` (`act_id` )
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `fk_action_creation`
FOREIGN KEY (`act_creation` )
REFERENCES `gestion`.`creation` (`cre_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_action_modification`
FOREIGN KEY (`act_modification` )
REFERENCES `gestion`.`modification` (`mod_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB

and the triggger is :

USE `gestion`;
DELIMITER $$

CREATE TRIGGER `action_BINS` BEFORE INSERT ON action FOR EACH ROW
-- Edit trigger body code below this line. Do not edit lines above this one
BEGIN
IF (NEW.act_action_origine IS NULL) THEN
SET NEW.act_action_origine = LAST_INSERT_ID() ;
END IF;
END

When I want to insert a new, Ihave an error with this following message :

ERROR 1452: Cannot add or update a child row: a foreign key constraint fails (`gestion`.`action`, CONSTRAINT `fk_action_action` FOREIGN KEY (`act_action_origine`) REFERENCES `action` (`act_id`) ON DELETE CASCADE ON UPDATE NO ACTION)

SQL Statement:

INSERT INTO `gestion`.`action` (`act_detail`) VALUES ('Try n°1')


Do you know why I have this error and how solve it?

Options: ReplyQuote


Subject
Views
Written By
Posted
Trigger on reflexive table
2560
December 07, 2013 01:59AM
1036
December 21, 2013 04:29PM


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.