时间戳

首页 > 解决方案 > 如何从 oracle 触发器更改为 oracle 复合触发器？

sql - 如何从 oracle 触发器更改为 oracle 复合触发器？

问题描述

我必须从给定的 Oracle 触发器更改：

CREATE OR REPLACE TRIGGER MY_TRIGGER
    AFTER UPDATE OF STATUS ON T_TABLE_A
    FOR EACH ROW
BEGIN
    UPDATE T_TABLE_B T
    SET T.STATUS = :NEW.STATUS
    WHERE T.REF_ID = :NEW.ID;
END;
/

到 Oracle 复合触发器。效果必须相同。我现在的做法是：

CREATE OR REPLACE TRIGGER MY_NEW_TRIGGER
        for insert or update on T_TABLE_A
        compound trigger
        before statement -- STUB
        is
        begin
          null;
        end before statement;
            before each row
            is
     begin
            end before each row;

     after each row -- STUB
            is
     begin
         --IDEA: collect ids of changed records (T_TABLE_A) here >> in a global variable? array?
            end after each row;

    after statement -- STUB
     is
     begin
              --IDEA: Bulk Update of T_TABLE_B (goal is: update T_TABLE_B.STATUS column; must be the same as T_TABLE_A.STATUS)
            end after statement;
      end;
/

但作为一名 Java 开发人员，我很难找到变量、数组和简单 DB 脚本的正确语法，所以任何方法都是有帮助的。

方法从哪里开始标记为“IDEA”。

标签： sqldatabaseoracletriggers

解决方案

正如我所看到的，您几乎完成了一半的工作，我将代码修改为IDEA部分。

我没有包括before statement部分和触发子句，您可以根据需要进行调整。我只是考虑更新我的代码中的状态列。

CREATE OR REPLACE TRIGGER my_trigger
   FOR UPDATE OF status ON t_table_a
   COMPOUND TRIGGER

   -- record type to hold each record updated in t_table_a 
   -- columns we are intersted in are id and status
   TYPE table_a_rec IS RECORD(
       id     t_table_a.id%TYPE
      ,status t_table_a.status%TYPE);

   --table type based on record to access each record using this
   TYPE table_a_row_data_t IS TABLE OF table_a_rec INDEX BY PLS_INTEGER;

   -- global variable for the compound trigger
   g_row_level_data table_a_row_data_t;

   AFTER EACH ROW IS
   BEGIN
      -- IDEA: collect ids of changed records (T_TABLE_A) here >> in a global variable? array?
      g_row_level_data(g_row_level_data.count + 1).id := :new.id;
      g_row_level_data(g_row_level_data.count).status := :new.status;
   END AFTER EACH ROW;

   AFTER STATEMENT IS
   BEGIN
      --IDEA: Bulk Update of T_TABLE_B (goal is: update T_TABLE_B.STATUS column; must be the same as T_TABLE_A.STATUS)
      FORALL i IN 1 .. g_row_level_data.count
         UPDATE t_table_b t
            SET t.status = g_row_level_data(i).status
          WHERE t.ref_id = g_row_level_data(i).id;
   END AFTER STATEMENT;
END my_trigger;
/

推荐阅读