I want to make a trigger that will insert a value from a connected row. For example I have a table with 3 rows as below:

I create a trigger that will work once row 3 and 4 are deleted (in this case will be deleted at the same time). And I want to record invnr and extinvnr from row 1 based on idparent=id. I cannot seem to make it work though.

CREATE OR REPLACE TRIGGER LOG_DELETEDPAYMENTS
BEFORE DELETE ON payments
FOR EACH ROW
BEGIN
IF :old.invnr IS NULL THEN
INSERT INTO TABLE_LOG_DELETEDPAYMENTS (table_name, invnr, extinvnr, invdate, transactionid, info, createdby, deleted_by, date_of_delete)
values ('payments', :old.invnr, :old.extinvnr, :old.invdate, :old:transactionid, :old.info, :old.createdby, sys_context('userenv','OS_USER'), SYSDATE);
END IF;
END;

How can I incorporate this into the trigger above?

CodePudding user response：

This assumes your are on release 12c or greater of Oracle database.

CREATE OR REPLACE PACKAGE LOG_DELETEDPAYMENTS_PKG
AS
  -- need a locally defined type for use in trigger
  TYPE t_payments_tbl IS TABLE OF payments%ROWTYPE INDEX BY PLS_INTEGER;
END LOG_DELETEDPAYMENTS_PKG;

CREATE OR REPLACE PACKAGE BODY LOG_DELETEDPAYMENTS_PKG
AS
BEGIN
  -- could also put the trigger code here and pass the type as a parameter to a procedure
  NULL;
END LOG_DELETEDPAYMENTS_PKG;


CREATE OR REPLACE TRIGGER LOG_DELETEDPAYMENTS_CT
FOR DELETE ON payments
COMPOUND TRIGGER

  l_tab LOG_DELETEDPAYMENTS_PKG.t_payments_tbl;
  l_count PLS_INTEGER:= 0;

  BEFORE EACH ROW IS
  BEGIN
    -- capture the deletes in local type
    l_count := l_count   1;
    l_tab(l_count).invnr := :old.invnr;
    l_tab(l_count).extinvnr := :old.extinvnr;
    l_tab(l_count).invdate := :old.invdate;
    l_tab(l_count).transactionid := :old.transactionid;
    l_tab(l_count).info := :old.info;
    l_tab(l_count).createdby := :old.createdby;
    l_tab(l_count).idparent := :old.idparent;
    l_tab(l_count).id := :old.id;
                         
  END BEFORE EACH ROW;

  AFTER STATEMENT IS
  BEGIN
    
    FOR i IN l_tab.first .. l_tab.COUNT LOOP
    
      IF(l_tab(i).invnr IS NULL) THEN
    
        -- if the invoice number is NULL, then get info from parent
        SELECT p.invnr
              ,p.extinvnr
        INTO   l_tab(i).invnr
              ,l_tab(i).extinvnr
        FROM   TABLE(l_tab) p
        WHERE  p.id = l_tab(i).idparent;

      END IF;
      
    END LOOP;
    
    -- log all deletes
    FORALL i IN 1 .. l_tab.COUNT
      INSERT INTO LOG_DELETEDPAYMENTS 
        (table_name, invnr, extinvnr, invdate, transactionid, info, createdby, deleted_by, date_of_delete)
      VALUES 
        ('payments', l_tab(i).invnr, l_tab(i).extinvnr, l_tab(i).invdate, l_tab(i).transactionid, l_tab(i).info, l_tab(i).createdby, sys_context('userenv','OS_USER'), SYSDATE);
    
    l_tab.delete;
  END AFTER STATEMENT;

END LOG_DELETEDPAYMENTS_CT;

CodePudding user response：

Try it this way:

create or replace TRIGGER LOG_DELETEDPAYMENTS
BEFORE DELETE ON payments
FOR EACH ROW
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    Declare
        my_invnr    PAYMENTS.INVNR%TYPE;
        my_extinvnr PAYMENTS.EXTINVNR%TYPE;
    Begin
        IF :old.INVNR IS NULL THEN
            Select    INVNR, EXTINVNR
            Into      my_invnr, my_extinvnr
            From      PAYMENTS
            Where     ID = :old.IDPARENT;
            --
            INSERT INTO TABLE_LOG_DELETEDPAYMENTS (table_name, invnr, extinvnr, invdate, transactionid, info, createdby, deleted_by, date_of_delete)
            values ('payments', my_invnr, my_extinvnr, :old.invdate, :old:transactionid, :old.info, :old.createdby, sys_context('userenv','OS_USER'), SYSDATE);
        END IF;
    End;
END;

• You should select the values of INVNR and EXTINVNR based on ID - IDPARENT relationship and store it in the variables (my_invnr and my_extinvnr).
• Those variables are used in INSERT into the log statement.
• Because of the Select ... Into statement that is reading the affected table - trigger would fail with table PAYMENTS is mutating error.
• To avoid that (to separate transaction from the table) you should Declare the PRAGMA AUTONOMOUS_TRANSACTION.
• There will be two rows inserted into LOG as the trigger runs FOR EACH (deleted) ROW.
  Regards...