lundi 7 juin 2021

How can i do an IF condition in a trigger in a sql phpMyAdmin script?

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