trigger - select count into - ORA-04091

Question

I want to INSERT or UPDATE on table (CMS_CONTENT_ENROLLMENT) and then do a count over the same table and update the result into another table. Sadly I'm getting a ORA-04091: table name is mutating, trigger/function may not see it

CREATE OR REPLACE TRIGGER CMS_CONTENT_ENROLLMENT_CNT_TR
    AFTER INSERT OR UPDATE
    ON CMS_CONTENT_ENROLLMENT
    FOR EACH ROW
DECLARE
BEGIN
    UPDATE CMS_CONTENT CNT
    SET CNT.REGISTRATIONCOUNT =
    (
        SELECT COUNT (ENROLLMENTID)
        FROM CMS_CONTENT_ENROLLMENT
        WHERE DELETED = 0 AND CONTENTID = CNT.CONTENTID
    )
    WHERE CNT.CONTENTID = :NEW.CONTENTID;
END;
/

why the need to maintain this count inside CMS_CONTENT, since you can simply query CMS_CONTENT_ENROLLMENT anytime you need? You've normalized these two anyway, but then turn around and want to denormalize pieces (simultaneously) into cms_content. KISS and query as needed, don't try to maintain this counter imo. — tbone

Vincent Malgrat Vincent Malgrat · Accepted Answer · 2013-02-13T14:44:01

Words of warning: I don't think it's a good idea to embed complex application logic code in triggers because the code is spread among many objects and it makes it hard to test and maintain. Usually the ORA-04091 is an indication that you are trying to perform something too complex for triggers. This can be circumvented but will result in complex logic often flawed in some ways, especially in a multi-user environment like a database. For this reason if you want a summary table I would advice a regular view, a materialized view or procedural logic.

If you want to go with triggers anyway, you could make it work with incremental logic, like this (untested, assumes that you never update contendid, that deleted is NOT NULL, that you never actually delete from the table and that the row exists in the summary table):

CREATE OR REPLACE TRIGGER CMS_CONTENT_ENROLLMENT_CNT_TR
   AFTER INSERT OR UPDATE ON CMS_CONTENT_ENROLLMENT
   FOR EACH ROW
DECLARE
BEGIN
   IF :NEW.deleted = 0 AND ((inserting) OR :OLD.deleted != 0) THEN
      UPDATE CMS_CONTENT CNT
         SET CNT.REGISTRATIONCOUNT = CNT.REGISTRATIONCOUNT + 1
       WHERE cnt.contentid = :NEW.contentid;
   ELSIF :NEW.deleted != 0 AND :OLD.deleted = 0 THEN
      UPDATE CMS_CONTENT CNT
         SET CNT.REGISTRATIONCOUNT = CNT.REGISTRATIONCOUNT - 1
       WHERE cnt.contentid = :NEW.contentid;
   END IF;
END;
/

trigger - select count into - ORA-04091

2 Answers