I have a CUSTOMER table with a primary key consisting of the single column by ID. There is also a PARENTID column which is a foreign key to the CUSTOMER which is the parent of the customer allowing us to implement customer hierarchies. This table and data related to this table is queried by external systems via a web service.
There is now a new requirement to allow updating a CUSTOMER records ID column. For precisely this purpose, SQL provides a FOREIGN KEY constraint with ON UPDATE CASCADE, but this is not implemented in Oracle 11G (and I guess the same applies to Oracle 12). I've tried to implement an AFTER UPDATE trigger for that purpose:
CREATE TRIGGER ufr_CUSTOMER_PARENTID AFTER UPDATE OF ID ON CUSTOMER
FOR EACH ROW
BEGIN
UPDATE CUSTOMER SET PARENTID = :NEW.ID WHERE PARENTID = :OLD.ID;
END;
Unfortunately I get SQL Error: ORA-04091: table CUSTOMER is mutating errors,
Of course, I can always handle this with explicit SQL update statements, but using database mechanisms to handle this is much preferable.
Pete
NB: Please don't bother replying if all you want to say is that my schema is wrong as I can't change that because existing, external interfaces must not be affected. Thanks