I have a table 'MYTABLE' with a primary key column 'ID' and a another column 'REPLACE_WITH' which contains a reference to another row of the table.
I want to add a table constraint which checks that the REPLACE_WITH value cannot be set to reference a row which is already referenced by another row. In the example the REPLACE_WITH column cannot be set for row with ID=1.
I have tried to create a trigger (before insert or update) which check it, but I get the error 'TABLE MUTATING'. If I use a separate transaction (PRAGMA autonomous_transaction) then the check does not work when multiple rows modifications done in the same transaction.
Someone has the solution?