Update foreign key of different row of the same table on UPDATE

Question

I'm using MySql, for my Database and I have a table "Person". Amongst other thing, it contains a relation to itself "Person_MarriedTo" stored as a foreigh_key;

This relation can be null since it's not everybody that's married. What I want to do is that this field can be updated automatically.

if I add A married to B, then B married to A if B deivorce from A, then A is also divorced from B

I thought triggers would be the way to go, but I can't get my code to work the way I want it to. Any insights would be much appreciated.

Here is my code for the trigger:

    USE `mydb`;
    DELIMITER $$

    CREATE TRIGGER `Person_BUPD` BEFORE UPDATE ON Person FOR EACH ROW
    BEGIN
        IF OLD.Person_MariedTo != NULL THEN
            UPDATE Person SET Person_MariedTo = NULL WHERE UID_Person =         OLD.Person_MariedTo;
        END IF;
        UPDATE Person SET Person_MariedTo = OLD.UID_Person WHERE UID_Person =         NEW.Person_MariedTo;
    END$$

Answer 1

What you are trying to do is not possible using a trigger.

Within a stored function or trigger, it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger. You need to do this some other way.

Source

See here: MySQL - Trigger for updating same table after insert