I would like to ask a question about triggers on mutating table and I would appreciate if you could kindly give me a hand. As I understand, if a trigger is fired based on a DML statement on a given table, then this trigger cannot query(SELECT) or modify the same table (INSERT, UPDATE, MERGE). And according to oracle online documentation:

ORA-04091: table string.string is mutating, trigger/function may not see itCause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.

Action: Rewrite the trigger (or function) so it does not read that table.

Apparently the methods suggested in this article, allow to deal with this problem. Well, I was interested by the topic so I decided to create my own test case and as an exercise apply the methods indicated in the document, in order to better understand and learn the topic. Here are the information about my test case:

CREATE OR REPLACE TRIGGER tr_articlesBEFORE INSERT ON myarticlesFOR EACH ROWDECLARE l_count PLS_INTEGER := 0;BEGIN -- So here we check to see that -- how many articles which was built -- by the constructor :NEW.consid -- are already in the stock (myarticles table) SELECT COUNT(*) INTO l_count FROM myarticles WHERE consid = :NEW.consid;

IF l_count >= 3 THEN RAISE exceptions_pkg.MAX_CONSTRCUTOR_LIMIT_REACHED; END IF;END;/

Now the problem is that oracle accepted this trigger and I didn't get any oracle error message complaining that the trigger was acting upon a mutating table. Whereas this is actually how it works, for each INSERT statement for each row, the trigger is fired and it tries to do a SELECT (SELECT COUNT(*) INTO l_count) on the very same table.

Thank you very much for your very interesting article. Among the methods explained in your article I found the second one, that is the Global Temporary Table very interesting. I didn't know that such thing even exists in oracle. So here is what I did:I created a global temporary table and instead of putting my trigger on the initial (articles) table, I defined my trigger based on this new table. According to what I read in oracle online documentation, no primary key/foreign key constraint can be defined on such tables, is that right?

So I created the global temporary table as an image (from a DDL point of view) of the original table. Instead of inserting rows directly in my articles table I insert rows into this temporary table and then I can query the principal table and insert the new row in the case where certain application specific conditions are satisfied. Here is what I did:

CREATE OR REPLACE TRIGGER tr_articlesAFTER INSERT ON myarticles_tmp_globaltabFOR EACH ROWDECLARE l_count PLS_INTEGER := 0;BEGIN -- So here we check to see that -- how many articles with built -- by the constructor :NEW.consid -- are already in the stock SELECT COUNT(*) INTO l_count FROM myarticles WHERE consid = :NEW.consid;

SQL> BEGIN 2 INSERT INTO myarticles_tmp_globaltab(artid, artlab, consid) VALUES ('artid-00006', 'Mouse', 'const-00003'); 3 EXCEPTION 4 WHEN exceptions_pkg.MAX_CONST_LIMIT_EXCEPTION 5 THEN 6 DBMS_OUTPUT.PUT_LINE('There are already 3 articles of this constructor in the stock'); 7 END; 8 /SQL> COMMIT; -- I do this commit in order to delete old rows in the global temporary table

There are already 3 articles of this constructor in the stock

PL/SQL procedure successfully completed.

SQL>

And it works pretty well !!

So it seems, that once we chose this method in order to deal with the mutating table problem, it is up to the developer to make sure that the only way for accessing the articles table for any DML is by passing through the global temporary table.

There is also, something that I found, initially in the test case I provided in the OP I used a BEFORE INSERT trigger and as I explained nothing happened, I got no error message from oracle upon my INSERT statements complaining about the mutating table problem. However, once I read your awesome article, I saw that you use AFTER INSERT/UPDATE triggers. I changed my code accordingly, that is, I used an AFTER INSERT trigger (instead of a BEFORE INSERT) and this time I could see the oracle famous ORA-04091 error message as I expected to see when I tried to insert a new row into the triggering table. So does this mean that with a BEFORE INSERT trigger we never have a table mutating problem and it is only with AFTER INSERT/UPDATE that we may encounter this problem?

... The definition of a temporary table is visible to all sessions, but the data in a temporary table is visible only to the session that inserts the data into the table...If you rollback a transaction, the data you entered is lost, although the table definition persists....A transaction-specific temporary table allows only one transaction at a time. If there are several autonomous transactions in a single transaction scope, each autonomous transaction can use the table only as soon as the previous one commits.

Based on the above oracle quotation (in particular that part that I put on bold), is it correct to conclude that there will be no problem in a multi-user environment if we use the global temporary table method to deal with mutating table problem? I mean it is safe because, even several users, therefore several sessions, access the same temporary global table, each session has access to its own data of the temporary global table and doesn't see other's data albeit, they are physically in the same temporary table (tablespace)?

You seem to have completely missed the point of the article. I am in no way suggesting you should insert into the temporary table, rather than the main table. The temporary table is just used as a temporary store for the primary key columns, so you can go back and check the data in the statement level triggers, since statement level triggers do not cause table mutating errors.

I don't even know where to start with your solution because it is so completely wrong...

Well, now that I read the article, once more, I saw that I missed the fact that there was also the statement level trigger. Because you present that for the first method (using collection). So I thought that the second method is a new method that has nothing to do with the first one and therefore the statement level trigger defined in the first method is no longer relevant to the second method. So here is what I understand from your article

1) WE create a row level trigger for the (mutating) table, so whenever the user tries to insert a new row into tab1, this one is fired

2) This row level trigger updates the global temporary table tab1_mods at least by the values of the primary key columns

3) We create also a statement level trigger on the same (mutating) table, as statement trigger don't cause mutating table problem. So again this trigger will be fired but however after the first row level trigger which we have just defined (statement level trigger is run after row level as I understand, is that right?)

4) This statement trigger, will search the values inside the global temporary table in order for application processing (in the context of your example this is the procedure tab1_statement_change) and does the necessary DML statements on the table tab1_audit.

5) Our procedure can also do a COMMIT (or DELETE FROM as you did) to empty the global temporary table.

Logically, you want to do some processing in a row level trigger. In your case this processing accesses the triggering table, causing a mutating table error.

The solution to this is to switch your row-level trigger to store the primary key of rows you are trying to insert, so that the statement level trigger can do any work that cannot be done in the row-level trigger

The important thing is you understand the concept, not the specific job you are trying to do. If you understand the concept, the solution will be simple.