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.
Dig deeper on Using Oracle PL-SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.