MySQL trigger in phpMyAdmin [closed]

dont understand, why this not work :C

DELIMITER $$

CREATE TRIGGER 'Reset Pass' BEFORE UPDATE ON authme
FOR EACH ROW
BEGIN
  IF OLD.`password` <> NEW.`password` THEN
    UPDATE authme SET `idVK` = '' WHERE `username` = NEW.`username`;
  END IF;
END$$

DELIMITER ;

Help pls

Answer

To start with: the trigger name should not be surrounded with single quotes; you need backticks – or better yet, no quotes.

The deeper problem is that a trigger cannot action the table it fires upon. Your trigger code compiles, but when it runs it gives error:

Error: ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG: Can’t update table ‘authme’ in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

I understand that you want to empty column idVK when the password changes. For this, you just need to set its value in pseudo-table NEW:

DELIMITER $$

CREATE TRIGGER Reset_Pass BEFORE UPDATE ON authme
FOR EACH ROW
BEGIN
    IF OLD.`password` <> NEW.`password` THEN
        SET NEW.`idVK` = '';
    END IF;
END$$

DELIMITER ;

Demo on DB Fiddle – once the above trigger is created:

insert into authme(username, password, idVK) values ('foo', 'bar', 'baz');
update authme set password = 'newbar' where username = 'foo';
select * from authme;

| username | password | idVK |
| -------- | -------- | ---- |
| foo      | newbar   |      |