A question about triggers on mutating tables - ORA-04091

[dariyoosh]

Hello Tim,

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:

http://docs.oracle.com/cd/E11882_01/ser ... sthref1926

ORA-04091: table string.string is mutating, trigger/function may not see it
Cause: 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.

While I was googling I found an interesting article on asktom

http://asktom.oracle.com/pls/asktom/ASK ... 9097816936

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:

Code: Select all

SQL> SELECT * FROM v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE   11.2.0.1.0   Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL>

OS: Fedora Core 17 (X86_64)


My testcase includes a pair of tables: constructors and articles in a stock

Code: Select all

SET SQLBLANKLINES ON;

DROP TABLE myarticles;
DROP TABLE myconstructors;

-- Table representing article constructors with the following
-- column description:
--
-- consid   Unique constructor id
-- conslab  constructor label
--
CREATE TABLE myconstructors
(
    consid      VARCHAR2(30)    NOT NULL,
    conslab     VARCHAR2(30)    NOT NULL
);
ALTER TABLE myconstructors ADD CONSTRAINT PK_MYCONSTRUCTORS
    PRIMARY KEY(consid);
   
-- Table representing articles with the following
-- column description:
--
-- artid    Unique reference of an article
-- artlab   Label of the article
-- consid   constructor id (foreign key to myconstructors)
--
CREATE TABLE myarticles
(
    artid   VARCHAR2(30)    NOT NULL,
    artlab  VARCHAR2(30)    NOT NULL,
    consid  VARCHAR2(30)    NOT NULL
);
ALTER TABLE myarticles ADD CONSTRAINT PK_MYARTICLES
    PRIMARY KEY(artid);
ALTER TABLE myarticles ADD CONSTRAINT FK1_MYARTICLES
    FOREIGN KEY(consid) REFERENCES myconstructors(consid)
        ON DELETE CASCADE;
       
-- Values for the table myconstructors
INSERT ALL
    INTO myconstructors(consid, conslab) VALUES('const-00001', 'Cisco')
    INTO myconstructors(consid, conslab) VALUES('const-00002', 'IBM')
    INTO myconstructors(consid, conslab) VALUES('const-00003', 'HP')
    INTO myconstructors(consid, conslab) VALUES('const-00004', 'BULL')
SELECT * FROM DUAL;

-- Values for the table myarticles
INSERT ALL
    INTO myarticles(artid, artlab, consid)
        VALUES('artid-00001', 'Keyboard', 'const-00003')
    INTO myarticles(artid, artlab, consid)
        VALUES('artid-00002', 'Mouse', 'const-00003')
    INTO myarticles(artid, artlab, consid)
        VALUES('artid-00003', 'Monitor', 'const-00003')
    INTO myarticles(artid, artlab, consid)
        VALUES('artid-00004', 'router', 'const-00001')
    INTO myarticles(artid, artlab, consid)
        VALUES('artid-00005', 'switch', 'const-00001')
SELECT * FROM DUAL;

SELECT * FROM myconstructors;
SELECT * FROM myarticles;


CONSID                                        CONSLAB
------------------------------             ------------------------------
const-00001                                   Cisco
const-00002                                   IBM
const-00003                                   HP
const-00004                                   BULL


ARTID                      ARTLAB                      CONSID
------------------       -------------------         -------------------------
artid-00001                 Keyboard                   const-00003
artid-00002                 Mouse                      const-00003
artid-00003                 Monitor                    const-00003
artid-00004                 router                     const-00001
artid-00005                 switch                     const-00001


Now imagine that we want deliberately to create a wrong trigger on the articles (mutating) table, in order to get error and then use the methods indicated in the asktom article to deal with it.

Let's say, we want to make sure that there is at most three articles from each constructor in the stock. For example the
following insert must raise an exception

Code: Select all

INSERT INTO myarticles(artid, artlab, consid) VALUES('artid-00006', 'switch', 'const-00003');


Because, there already three articles built by the constructor 'const-00003'(HP) in the articles table.

Here is how I defined the trigger

Code: Select all

CREATE OR REPLACE PACKAGE exceptions_pkg
AS
    MAX_CONSTRCUTOR_LIMIT_REACHED EXCEPTION;
END exceptions_pkg;
/

CREATE OR REPLACE TRIGGER tr_articles
BEFORE INSERT ON myarticles
FOR EACH ROW
DECLARE
    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.

Why I don't get any error message? Isn't that a mutating table?

Thanks in advance,

Kind regards,
Dariyoosh

[Tim...]

Hi.

A mutating table exception is a runtime error. Oracle does not prevent code from accessing the table at compile time. It will just fail at run time.

I have an article on this here:

http://www.oracle-base.com/articles/9i/ ... ptions.php

Cheers

Tim...

[dariyoosh]

Dear Tim,


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:

Code: Select all

CREATE GLOBAL TEMPORARY TABLE myarticles_tmp_globaltab
(
    artid   VARCHAR2(30)    NOT NULL,
    artlab  VARCHAR2(30)    NOT NULL,
    consid  VARCHAR2(30)    NOT NULL
) ON COMMIT DELETE ROWS;


CREATE OR REPLACE TRIGGER tr_articles
AFTER INSERT ON myarticles_tmp_globaltab
FOR EACH ROW
DECLARE
    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;
   
    IF l_count >= 3
    THEN
        RAISE exceptions_pkg.MAX_CONST_LIMIT_EXCEPTION;
    ELSE
        INSERT INTO myarticles(artid, artlab, consid)
            VALUES(:NEW.artid, :NEW.artlab, :NEW.consid);
    END IF;
END;
/


And therefore for inserting a new article in my article stock table I run something similar to this

Code: Select all

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?

Also, I checked oracle online documentation at the following link,

http://docs.oracle.com/cd/E11882_01/ser ... m#i1006400

Here is what oracle says about global temporary tables:

... 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)?


Thank you very much for your time,

Regards,
Dariyoosh

[Tim...]

Hi.

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...

Cheers

Tim...

[dariyoosh]

Oh, I was happy to have done a good job

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.


Is that correct?


Regards,
Dariyoosh

[Tim...]

Hi.

I think you need to take a step back.

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.

Cheers

Tim...

[dariyoosh]

OK, I will read again carefully the article in order to be sure to understand the concept.

Thanks a lot for your help and your time.

Merry Christmas!


Regards,
Dariyoosh

[Tim...]