Триггер изменения Oracle на INSERT

Я пытаюсь создать небольшую базу данных с 3 таблицами в Oracle 8i и двумя триггерами на них. Вот схема базы данных: db_schema

Я создал таблицы:

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;

И ТРИГГЕРЫ мне нужны:

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;

И другой:

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;

Как видите, в таблице OPEN_INVOICES мне нужен body_xml из таблицы INVOICES, поэтому я хочу создать body_xml с помощью select.

После этого, когда я пытаюсь вставить в счета-фактуры, я получаю эту ошибку: sqlerror


sql oracle plsql database-trigger oracle8i
person Ákos Kemenes    schedule 17.04.2018    source источник
comment
Oracle 8i - правда? Ему 20 лет!   -  person Wernfried Domscheit    schedule 17.04.2018
comment
Вы не должны создавать никаких пользовательских объектов в SYSTEM схеме.   -  person Wernfried Domscheit    schedule 17.04.2018
comment
@WernfriedDomscheit Бесполезно. Если вы разместите это, объясните, почему не использовать SYSTEM, или используйте комментарии, чтобы попросить разъяснений.   -  person Chrᴉz supports Monica    schedule 17.04.2018
comment
Это просто тестовая база данных. На самом деле я, конечно, не буду использовать SYSTEM, но сейчас это не важно.   -  person Ákos Kemenes    schedule 17.04.2018
comment
Верно, но это часть вашего MCVE для использования SYSTEM (которая является зарезервированной внутренней учетной записью Oracle с повышенными привилегиями, которая не предназначена для определяемых пользователем объектов).   -  person William Robertson    schedule 17.04.2018
comment
НИКОГДА, никогда не используйте пользователя SYS или SYSTEM для создания собственных таблиц. Только не   -  person a_horse_with_no_name    schedule 17.04.2018

Ответы (3)


arrow_upward
3
arrow_downward

У вас есть триггер, который срабатывает ПОСЛЕ INSERT ON INVOICES. Это триггеры вставляют в INVOICE_STATUSES.

У вас также есть триггер, который срабатывает ПОСЛЕ INSERT ON INVOICE_STATUSES, однако в этом триггере вы пытаетесь выбрать из таблицы INVOICES - там вы получаете ошибку.

Считайте свой оператор и все DML внутри любого триггера одной командой. Вы не можете выбрать таблицу, пока в нее вставляете какие-либо данные.

Вы должны поместить всю свою логику в хранимую процедуру и выполнить ее.

person Wernfried Domscheit    schedule 17.04.2018

arrow_upward
0
arrow_downward

Если вам нужно сделать это с помощью триггеров, то, вероятно, лучшим способом будет также сохранить invoice_body_xml в INVOICE_STATUSES и заполнить его в триггере 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;
/
person David Faber    schedule 17.04.2018

arrow_upward
0
arrow_downward

Я предлагаю вместо того, чтобы пытаться складывать, вращать и искажать связку триггеров вместе, чтобы выполнить требуемые INSERT, SELECT и т. Д., Я предлагаю вам написать процедуру для создания ваших счетов-фактур, подобную следующей:

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;

Вы по-прежнему можете использовать триггеры для установки таких полей, как INSERTTS и MODIFYTS.

При этом я обнаружил, что необходимо установить несколько полей в OPEN_INVOICES, но нигде (насколько я смог найти) они не инициализировались. Это может быть то, что вы хотите изучить.

Удачи.

person Bob Jarvis - Reinstate Monica    schedule 17.04.2018