The size of the trigger cannot exceed 32K.

If the logic for your trigger requires much more than 60 lines of PL/SQL source text, then put most of the source text in a stored subprogram and invoke the subprogram from the trigger. For information about subprograms invoked by triggers, see "Subprograms Invoked by Triggers".

In addition to the restrictions that apply to all PL/SQL units (see "LONG and LONG RAW Variables"), triggers have these restrictions:

• A trigger cannot declare a variable of the LONG or LONG RAW data type.

• A SQL statement in a trigger can reference a LONG or LONG RAW column only if the column data can be converted to the data type CHAR or VARCHAR2.

• A trigger cannot use the correlation name NEW or PARENT with a LONG or LONG RAW column.

A mutating table is a table that is being modified by a DML statement (possibly by the effects of a DELETE CASCADE constraint). (A view being modified by an INSTEAD OF trigger is not considered to be mutating.)

The mutating-table restriction prevents the trigger from querying or modifying the table that the triggering statement is modifying. When a row-level trigger encounters a mutating table, ORA-04091 occurs, the effects of the trigger and triggering statement are rolled back, and control returns to the user or application that issued the triggering statement, as Example 10-26 shows.

If you must use a trigger to update a mutating table, you can avoid the mutating-table error in either of these ways:

• Use a compound DML trigger (see "Using Compound DML Triggers to Avoid Mutating-Table Error").

• Use a temporary table.

  For example, instead of using one AFTER each row trigger that updates the mutating table, use two triggers—an AFTER each row trigger that updates the temporary table and an AFTER statement trigger that updates the mutating table with the values from the temporary table.

-- Create log table
 
DROP TABLE log;
CREATE TABLE log (
  emp_id  NUMBER(6),
  l_name  VARCHAR2(25),
  f_name  VARCHAR2(20)
);
 
-- Create trigger that updates log and then reads employees
 
CREATE OR REPLACE TRIGGER log_deletions
  AFTER DELETE ON employees
  FOR EACH ROW
DECLARE
  n INTEGER;
BEGIN
  INSERT INTO log VALUES (
    :OLD.employee_id,
    :OLD.last_name,
    :OLD.first_name
  );
 
  SELECT COUNT(*) INTO n FROM employees;
  DBMS_OUTPUT.PUT_LINE('There are now ' || n || ' employees.');
END;
/
 
-- Issue triggering statement:
 
DELETE FROM employees WHERE employee_id = 197;

DELETE FROM employees WHERE employee_id = 197
            *
ERROR at line 1:
ORA-04091: table HR.EMPLOYEES is mutating, trigger/function might not see it
ORA-06512: at "HR.LOG_DELETIONS", line 10
ORA-04088: error during execution of trigger 'HR.LOG_DELETIONS'

SELECT count(*) FROM log;

  COUNT(*)
----------
         0
 
1 row selected.

SELECT employee_id, last_name FROM employees WHERE employee_id = 197;

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        197 Feeney
 
1 row selected.

DROP TABLE p;
CREATE TABLE p (p1 NUMBER CONSTRAINT pk_p_p1 PRIMARY KEY);
INSERT INTO p VALUES (1);
INSERT INTO p VALUES (2);
INSERT INTO p VALUES (3);
 
DROP TABLE f;
CREATE TABLE f (f1 NUMBER CONSTRAINT fk_f_f1 REFERENCES p);
INSERT INTO f VALUES (1);
INSERT INTO f VALUES (2);
INSERT INTO f VALUES (3);
 
CREATE TRIGGER pt
  AFTER UPDATE ON p
  FOR EACH ROW
BEGIN
  UPDATE f SET f1 = :NEW.p1 WHERE f1 = :OLD.p1;
END;
/
 

SELECT * FROM p ORDER BY p1;
 

        P1
----------
         1
         2
         3

SELECT * FROM f ORDER BY f1;
 

        F1
----------
         1
         2
         3

UPDATE p SET p1 = p1+1;
 

SELECT * FROM p ORDER BY p1;
 

        P1
----------
         2
         3
         4

SELECT * FROM f ORDER BY f1;
 

        F1
----------
         4
         4
         4