Oracle mutating trigger on INSERT

Question

I'm trying to create a small database with 3 tables, in Oracle 8i, and two triggers on them. Here is the database schema:

I've created the tables:

CREATE TABLE SYSTEM.Invoices(
   invoice_id   NUMBER              NOT NULL,
   invoice_body_xml CLOB     NOT NULL,
   insertTS  DATE              NOT NULL,
   modifyTS DATE,   
   PRIMARY KEY (invoice_id))
    TABLESPACE SYSTEM;


CREATE TABLE SYSTEM.Invoice_Statuses(
   invoice_id           NUMBER              NOT NULL,
   status               NVARCHAR2(15)       NOT NULL,
   status_details       CLOB,
   transaction_id       NVARCHAR2(50),
   transaction_index    NUMBER,
   request_id           NVARCHAR2(50),
   insertTS             DATE                NOT NULL,
   CONSTRAINT from_statuses_to_invoices
        FOREIGN KEY(invoice_id)
        REFERENCES SYSTEM.INVOICES(invoice_id))
    TABLESPACE SYSTEM;

CREATE TABLE SYSTEM.Open_Invoices(
    invoice_id          NUMBER              NOT NULL,
    invoice_body_xml    CLOB                NOT NULL,
    status              NVARCHAR2(15)       NOT NULL,
    transaction_id      NVARCHAR2(50),
    transaction_index   NUMBER,
    insertTS            DATE                NOT NULL,
   CONSTRAINT from_open_to_invoices
        FOREIGN KEY(invoice_id)
        REFERENCES SYSTEM.INVOICES(invoice_id))
    TABLESPACE SYSTEM;

And the TRIGGERS I need:

CREATE OR REPLACE TRIGGER after_invoice_insert
AFTER INSERT
   ON SYSTEM.INVOICES
   FOR EACH ROW

BEGIN
   INSERT INTO SYSTEM.INVOICE_STATUSES
   (INVOICE_ID,
   STATUS,
   INSERTTS)
   VALUES
   ( 
    :NEW.invoice_id,
    n'NEW',
    SYSDATE);
END;

And the other one:

CREATE OR REPLACE TRIGGER after_invoice_statuses_insert
AFTER INSERT
   ON SYSTEM.INVOICE_STATUSES
   FOR EACH ROW
DECLARE
    body_xml CLOB;

BEGIN
   SELECT SYSTEM.INVOICES.invoice_body_xml INTO body_xml FROM SYSTEM.INVOICES WHERE SYSTEM.INVOICES.invoice_id = :NEW.invoice_id;
   INSERT INTO SYSTEM.OPEN_INVOICES
   (INVOICE_ID,
   INVOICE_BODY_XML,
   STATUS,
   TRANSACTION_ID,
   TRANSACTION_INDEX,
   INSERTTS)
   VALUES
   ( 
    :NEW.invoice_id,
    body_xml,
    :NEW.status,
    :NEW.transaction_id,
    :NEW.transaction_index,
    SYSDATE);
END;

As you can see, at the OPEN_INVOICES table, I need the body_xml, from the INVOICES table, thats why I want to create the body_xml with the select.

After this, when I try to insert into invoices I get this error:

Answer 1

You have a trigger which fires AFTER INSERT ON INVOICES. This triggers inserts into INVOICE_STATUSES.

You also have a trigger which fires AFTER INSERT ON INVOICE_STATUSES, however in this trigger you try to select from table INVOICES - there you get the error.

Consider your statement and all DML's inside any trigger as one command. You cannot select a table while you insert any data in it.

You should put all your logic into a stored procedure and execute that one.

Answer 2

If you need to accomplish this through triggers then probably the best way would be to store invoice_body_xml in INVOICE_STATUSES as well, and populate it in the trigger on INVOICES:

CREATE OR REPLACE TRIGGER after_invoice_insert
 AFTER INSERT
    ON invoices
   FOR EACH ROW
BEGIN
   INSERT INTO invoice_statuses
     ( invoice_id, status, insertts, invoice_body_xml )
   VALUES
     ( :new.invoice_id, n'NEW', SYSDATE, :new.invoice_body_xml );
END;
/

CREATE OR REPLACE TRIGGER after_invoice_statuses_insert
 AFTER INSERT
    ON invoice_statuses
   FOR EACH ROW
BEGIN
    INSERT INTO open_invoices
      ( invoice_id, invoice_body_xml, status, transaction_id, transaction_index, insertts )
   VALUES
     ( :new.invoice_id, :new.invoice_body_xml, :new.status, :new.transaction_id, :new.transaction_index, SYSDATE );
END;
/

Answer 3

I suggest that rather than attempting to fold, spindle, and mutilate a bunch of triggers together to do the required INSERT's, SELECT's, etc, I suggest that you write a procedure to create your invoices similar to the following:

CREATE OR REPLACE PROCEDURE CREATE_INVOICE
  (pinInvoice_id       IN NUMBER,
   pinInvoice_body_xml IN CLOB)
IS
BEGIN
  INSERT INTO INVOICES
    (INVOICE_ID,
     INVOICE_BODY,
     INSERTTS)
  VALUES
    (pinInvoice_id,
     pinInvoice_body_xml,
     SYSDATE);

  INSERT INTO INVOICE_STATUSES
    (INVOICE_ID,
     STATUS,
     INSERTTS)
  VALUES
    (pinInvoice_id,
     n'NEW',
     SYSDATE);

  INSERT INTO OPEN_INVOICES
    (INVOICE_ID,
     INVOICE_BODY_XML,
     STATUS,
     TRANSACTION_ID,
     TRANSACTION_INDEX,
     INSERTTS)
  VALUES
   (pinInvoice_id,
    pinInvoice_body_xml,
    n'NEW',
    ???,  -- don't know where this comes from
    ???,  -- don't know where this comes from
    SYSDATE);
END CREATE_INVOICE;

You may still want to use triggers to set fields such as INSERTTS and MODIFYTS.

While doing this I found that a couple of fields in OPEN_INVOICES needed to be set, but no where (as far as I could find) were they being initialized. This may be something you want to look into.

Best of luck.