Mutating and Constraining Tables:

A mutating table is a table which is currently being changed through an UPDATE, DELETE Or INSERT statement, or a table which may requires to be updated. Alternatively, the trigger body or the triggering action must not hold any operations on the similar table on that the trigger is written. They are not considered to be mutating in the case of Statement Triggers.

A constraining table is a table in which a triggering statement may require to read either straightly for a SQL statement or indirectly for a declarative statement.

Example

CREATE OR REPLACE TRIGGER INS_PROD AFTER INSERT ON PRODUCT FOR EACH ROW

DECLARE

TOT_REC NUMBER;

BEGIN

SELECT COUNT(*) INTO TOT_REC FROM PRODUCT;

DBMS_OUTPUT.PUT_LINE('THE TOTAL RECORDS ARE '||TOT_REC);

END;

Trigger gets created.

When a record is inserted,

INSERT INTO PRODUCT (pcode,pname) VALUES(189,'books');

INSERT INTO PRODUCT(pcode,pname) VALUES(189,'books')

*

ERROR at line 1:

ORA-04091: table HEMA.PRODUCT is mutating, trigger/function might not see it

ORA-06512: at "HEMA.INS_PROD", line 4

ORA-04088: error during execution of trigger 'HEMA.INS_PROD'

In order to avoid this problem, if the total numbers of records are to be show by using the trigger along with a package variable. The subsequent example is illustrative of this:

Before writing a trigger, write a package with specification alone:

CREATE OR REPLACE PACKAGE TEST AS N NUMBER:=0;

END;

After that write a Before Trigger as shown below:

CREATE OR REPLACE TRIGGER INS_PROD1 BEFORE INSERT ON PRODUCT FOR EACH ROW BEGIN

SELECT COUNT(*) INTO TEST.N FROM PRODUCT; END;

In addition write an After Trigger that has the following statements:

CREATE OR REPLACE TRIGGER INS_PROD AFTER INSERT ON PRODUCT FOR EACH ROW DECLARE

TOT_REC NUMBER; BEGIN TOT_REC:=TEST.N+1;

DBMS_OUTPUT.PUT_LINE('THE TOTAL RECORDS ARE '||TOT_REC);

END;

Now, if the insert statement is issued and it displays

INSERT INTO PRODUCT(PCODE,PNAME) VALUES(189,'BOOKS'); THE TOTAL RECORDS ARE 12

1 row created.