如何在INSERT的VALUES中包含SUBSELECT以從不同行獲取值？

我想制作一個觸發器，該觸發器將從連接的行中插入一個值。例如，我有一個有 3 行的表，如下所示：

我創建了一個觸發器，該觸發器將在第 3 行和第 4 行被洗掉后作業（在這種情況下將同時被洗掉）。我想記錄invnr并extinvnr從第 1 行基于idparent=id. 我似乎無法讓它作業。

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;

如何將其合并到上面的觸發器中？

uj5u.com熱心網友回復：

試試這種方式：

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;

• 您應該根據 ID - IDPARENT 關系選擇 INVNR 和 EXTINVNR 的值并將其存盤在變數（my_invnr和my_extinvnr）中。
• 這些變數在 INSERT 中使用到日志陳述句中。
• 由于 Select ... Into 陳述句正在讀取受影響的表 - 觸發器將失敗，表 PAYMENTS 是變異錯誤。
• 為避免這種情況（從表中分離事務），您應該宣告PRAGMA AUTONOMOUS_TRANSACTION。
• 當觸發器運行 FOR EACH (deleted) ROW 時，將有兩行插入 LOG。
  問候...

uj5u.com熱心網友回復：

這假設您使用的是 12c 或更高版本的 Oracle 資料庫。

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;

標籤：sql甲骨文选择触发器子查询

上一篇：OracleLinux是否帶有Oracle-DB

下一篇：Oracle-選擇自定義訂單