CREATE OR REPLACE TRIGGER trg1 AFTER INSERT ON regaddress FOR EACH ROW DECLARE BEGIN UPDATE regaddress SET destname = name WHERE detailscode :NEW.detailscode; commit; END;As seen on the net I tried adding PRAGMA AUTONOMOUS_TRANSACTION to the code. The error went away but the table didn't get updated. Also I want this kind of update to happen as I cannot modify it through the front end because the same form is running in different departments, so the only option is to write a trigger.
One way to handle this situation is to use a package PL/SQL table to store ROWIDs of updated records in a row trigger, and reprocess the updated records in a statement trigger.
Search for Note:156388.1 at metalink.oracle.com for more info.
This was first published in August 2006