Dear Sir/Madam,
I am using Oracle Database 12C.
I am getting "ORA-04091: table HR.TABLE_NAME is mutating, trigger/function may not see it'. The sample workspace to replicate the issue is shown below:
1. Create transactions table as below:
CREATE TABLE transactions (
transaction\_id NUMBER(10) PRIMARY KEY,
claim\_id NUMBER(10),
paid\_status NUMBER(1)
);
2. Insert Data into transactions table
INSERT INTO transactions values(1,100,1);
INSERT INTO transactions values(2,100,0);
INSERT INTO transactions values(3,100,1);
INSERT INTO transactions values(4,100,1);
INSERT INTO transactions values(5,100,1);
3. Commit the changes
COMMIT;
4. Query the inserted data from transactions table
SELECT * FROM transactions;
5. Create the check_if_all_paid
CREATE OR REPLACE PROCEDURE check_if_all_paid (
p\_claim\_id\_i IN NUMBER
) AS
--PRAGMA autonomous\_transaction;
lv\_unpaid\_count NUMBER(10);
BEGIN
SELECT COUNT(claim\_id) into lv\_unpaid\_count FROM transactions
WHERE claim\_id = p\_claim\_id\_i AND (paid\_status != 1);
END check_if_all_paid;
6. Create the Trigger which will be fired as soon as any update happens in the transaction table
CREATE OR REPLACE TRIGGER trg_test AFTER
UPDATE OF paid\_status ON transactions
FOR EACH ROW
BEGIN
check\_if\_all\_paid(:old.claim\_id);
END;
7. Try to update the transaction table using the below UPDATE statement
UPDATE transactions SET paid_status = 1 WHERE transaction_id = 2;
Thus I am getting the ORA-04091: table HR.TRANSACTIONS is mutating, trigger/function may not see it error as shown below.
Solution: As a solution for this I tried to put PRAGMA autonomous_transaction inside check_if_all_paid procedure, which solve the problem, but my further requirement which is to get the count of PAID_STATUS inside procedure gets affected giving me wrong count.
Hence I cannot put PRAGMA autonomous_transaction.
I tried many other approach but still failed to solved the requirement.
Thanks & Regards,
Susanto Paul