Resolving table problem in Oracle SQL is deadlocked

Question

Hey, I'm trying to create a trigger in my Oracle database that changes all the other records except those that have just been changed, and I trigger the trigger on 0. Because I am updating the records in the same table as the trigger trigger I have an error mutated table. To solve this problem, I put the code as an anonymous transaction, however this causes a dead end.

Launch Code:

CREATE OR REPLACE TRIGGER check_thumbnail AFTER INSERT OR UPDATE OF thumbnail ON photograph FOR EACH ROW BEGIN IF :new.thumbnail = 1 THEN check_thumbnail_set_others(:new.url); END IF; END; 

Procedure Code:

 CREATE OR REPLACE PROCEDURE check_thumbnail_set_others(p_url IN VARCHAR2) IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN UPDATE photograph SET thumbnail = 0 WHERE url <> p_url; COMMIT; END; 

I suppose I'm causing a dead end because the trigger fires on its own. Any ideas?

Answer 1

Using an offline transaction for this kind of thing is almost certainly a mistake. What happens if a transaction in which a new thumbnail is inserted needs to be rolled back? You have already transferred the change to other rows in the table.

If you want the data to be consistent with transactions, you will need several triggers and some way to save state. The easiest option is to create a package with a collection of type thumbnail.url%, and then create three triggers in the table. And before the trigger, the operator will clear the collection. The level trigger will insert the value: new.url into the collection. After the trigger is launched, it will then read the values ​​from the collection and call the check_thumbnail_set_others procedure (which would not be an autonomous transaction).