I have 2 triggers in my db but each one call each other (I can execute this script but when the triggers get activate I get : #1442 - Can't update table 'users' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.)
My 2 triggers:
CREATE TRIGGER `trigger_update_users_update_fiche_client`
AFTER UPDATE ON `users`
FOR EACH ROW
UPDATE `client` SET `NOM_CLIENT` = NEW.firstname, `PRENOM_CLIENT` = NEW.name, `TELEPHONE_CLIENT` = NEW.phone, `EMAIL_CLIENT` = NEW.email
WHERE `client`.`ID_USER` = OLD.ID;
CREATE TRIGGER `trigger_update_client_update_fiche_users`
AFTER UPDATE ON `client`
FOR EACH ROW
UPDATE `users` SET `firstname` = NEW.NOM_CLIENT, `name` = NEW.PRENOM_CLIENT, `phone` = NEW.TELEPHONE_CLIENT, `email` = NEW.EMAIL_CLIENT
WHERE `users`.`id` = OLD.ID_USER
To resolve it I have tried to do this :
CREATE TRIGGER `trigger_update_users_update_fiche_client`
AFTER UPDATE ON `users`
FOR EACH ROW
IF (OLD.firstname <> NEW.firstname OR OLD.name <> NEW.name OR OLD.phone <> NEW.phone OR OLD.email <> NEW.email)
THEN
UPDATE `client` SET `NOM_CLIENT` = NEW.firstname, `PRENOM_CLIENT` = NEW.name, `TELEPHONE_CLIENT` = NEW.phone, `EMAIL_CLIENT` = NEW.email
WHERE `client`.`ID_USER` = OLD.ID;
But I get this error :
CREATE TRIGGER `trigger_update_users_update_fiche_client`
AFTER UPDATE ON `users`
FOR EACH ROW
IF (OLD.firstname <> NEW.firstname OR OLD.name <> NEW.name OR OLD.phone <> NEW.phone OR OLD.email <> NEW.email)
THEN
UPDATE `client` SET `NOM_CLIENT` = NEW.firstname, `PRENOM_CLIENT` = NEW.name, `TELEPHONE_CLIENT` = NEW.phone, `EMAIL_CLIENT` = NEW.email
WHERE `client`.`ID_USER` = OLD.ID
MySQL replied: Documentation
#1064 - Syntax error near '' on line 7
And with a END IF i get this error :
FOR EACH ROW
IF (OLD.firstname <> NEW.firstname OR OLD.name <> NEW.name OR OLD.phone <> NEW.phone OR OLD.email <> NEW.email)
THEN
UPDATE `client` SET `NOM_CLIENT` = NEW.firstname, `PRENOM_CLIENT` = NEW.name, `TELEPHONE_CLIENT` = NEW.phone, `EMAIL_CLIENT` = NEW.email
WHERE `client`.`ID_USER` = OLD.ID
END IF
MySQL replied: Documentation
#1064 - Syntax error near 'END IF' on line 8
Aucun commentaire:
Enregistrer un commentaire