Trigger on reflexive table
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?