首页 文章

Oracle - 更新后触发

提问于
浏览 次
0

我有触发器：

CREATE OR REPLACE TRIGGER TR.TR_OUTPUT_EHT_UPDATE_OPE
  AFTER DELETE
  ON TR.TR_JN_OUTPUT_EHT
  FOR EACH ROW
DECLARE
   BEGIN
  UPDATE TR.TR_BP tbp
    SET tbp.DONE= 2, tbp.DATA_WYK = SYSDATE
    WHERE EXISTS (SELECT ARCH.NR
    FROM TR.TR_KB_OUTPUT_ARCH ARCH
        WHERE ARCH.NR = tbp.NRKK)
    AND NOT EXISTS (SELECT OUTPUT.NR_SERYJNY
        FROM TR.TR_JN_OUTPUT OUTPUT
        WHERE OUTPUT.NR = tbp.NRKK);
END;
/

之后我想获得刚刚更新的行的“NRKK”并更新另一个由“NRKK”连接的表：

UPDATE P.TR_OPE OPE
SET OPE.DATA = SYSDATE, 
OPE.STATUS = OPE.STATUS + 1 
WHERE OPE.NRKK = "NRKK"

我试着这样做：

SELECT tbp.NRKK INTO v_nrkk FROM TR.TR_BP tbp WHERE tbp.DATA = (SELECT 
MAX(tbp.DATA) FROM TR.TR_BP tbp1);

但我有错误：ORA 04091，ORA 06512和ORA 04088 .

EDITED:

我创建了另一个触发器

CREATE OR REPLACE TRIGGER TR.TR_OP_E
  AFTER UPDATE ON TR.TR_BP
FOR EACH ROW
DECLARE
    v_nrkk TR.TR_BP.NRKK % TYPE;
BEGIN
  SELECT tbp.NRKK INTO v_nrkk FROM TR.TR_BPtbp WHERE tbp.DATA = (SELECT 
MAX(tbp.DATA) FROM TR.TR_BP tbp1);

  IF v_nrkk is NOT NULL THEN
  UPDATE P.TR_OPE OPE
    SET OPE.DATA = SYSDATE, OPE.STATUS = 1
    WHERE OPE.ID_BR = '00100300'
    AND OPE.NRKK = v_nrkk;
  END IF;
  END;
/

但我还有错误......

oracle triggers

1 回答

  • 1

    正如@Renato Afonso所提到的，你可以避免使用 COMPOUND TRIGGER 来改变表错误 . 见下面的演示;

    --Tables

    CREATE TABLE TABLE_1 (data NUMBER);
/
CREATE TABLE TABLE_2 (DATA1 NUMBER);
/
    • 记在表1中
    SQL> select * from TABLE_1;

      DATA
----------
         1
    • 记录在表2中
    SQL> select * from TABLE_2;

     DATA1
----------
         5

    触发：

    CREATE OR REPLACE TRIGGER TR_OP_E FOR
   INSERT OR UPDATE OR DELETE
   ON TABLE_1
   COMPOUND TRIGGER
   v_nrkk   TABLE_1.DATA%TYPE;
   AFTER STATEMENT
   IS
   BEGIN
      --Selecting from the same table on which trigger is created
      SELECT tbp.data
        INTO v_nrkk
        FROM Table_1 tbp
       WHERE tbp.DATA = (SELECT MAX (tbp.DATA)
                           FROM Table_1 tbp1);

      IF v_nrkk IS NOT NULL
      THEN
         UPDATE TABLE_2 OPE
            SET OPE.DATA1 = v_nrkk;
      END IF;
   END
   AFTER STATEMENT;

END;
/

    执行：

    SQL> UPDATE TABLE_1 SET DATA = 2;

1 row updated.

SQL> COMMIT;

Commit complete.

SQL> select * from TABLE_1;

      DATA
----------
         2

SQL>  select * from TABLE_2;

     DATA1
----------
         2
    回复于

相关问题