Can't update other table in trigger

Question

We have 2 tables that that store usernames and passwords, and two websites where users login (bad idea I know, but I don't have control over that).

So when a member updates their password on one table I would like the trigger to update the password on the other table as well. I am having issues though as it is giving me an error.

The query:

update authenticate set password = md5('superman') where member_id = 108649

The error:

ERROR 1442 (HY000) at line 3: Can't update table 'authenticate' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Here are the two triggers:

-- When update runs on the authenticate table
CREATE TRIGGER `authenticate_after_update` AFTER UPDATE ON `authenticate` FOR EACH ROW BEGIN
    IF new.password != old.password THEN 
        update auth set passwd = new.password where member_id = new.member_id;
    end if;
END

-- When update runs on the auth table
CREATE TRIGGER `auth_after_update` AFTER UPDATE ON `auth` FOR EACH ROW BEGIN
    if new.passwd != old.passwd then
        update authenticate set password = new.passwd where member_id = new.member_id;
    end if;
END

score 0 · Answer 1 · edited May 23 '17 at 11:49

I was able to get it with help from this thread: How to avoid circular Trigger dependencies in MySQL

Here was the result I came up with:

-- When update runs on the authenticate table
CREATE TRIGGER `authenticate_after_update` AFTER UPDATE ON `authenticate` FOR EACH ROW BEGIN
    if @updating is null then
        set @updating = 1;
        update auth set passwd = new.password where member_id = new.member_id;
        set @updating = null;
    end if;
END

-- When update runs on the auth table
CREATE TRIGGER `auth_after_update` AFTER UPDATE ON `auth` FOR EACH ROW BEGIN
    if @updating is null then
        set @updating = 1;
        update authenticate set password = new.passwd where member_id = new.member_id;
        set @updating = null;
    end if;
END

Can't update other table in trigger

1 Answers1