database - 如何在表上创建触发器来识别特定记录是否被删除/更新并存储实体更新信息？

Question

我需要将信息保存在另一个临时表中，例如，TableTemp 具有正在修改的记录，并且还有一个列定义哪个实体更新了它。

Answer 1

你看起来像是刚刚发现，并提出了非常广泛的问题。然而，这里有一个可能的解决方案，假设如下:

• a_sqnc 是您将在 TableTemp 中使用的序列，用于跟踪 NO_ORD 列中的操作顺序(即使还有带有修改时间的 D_UPD 列)。

---

create sequence a_sqnc
minvalue 1
maxvalue 99999999
start with 1
increment by 1
nocache;

• TableTemp 将有一个 TABLE_NAME 列，以便跟踪来自不同表的更改。它还有一个 PK_VALUE 和 ROW_VALUE，我们在其中存储更改的数据。这是带有有用索引的表创建:

---

create table TableTemp (
  table_name VARCHAR2(50) not null,
  action     VARCHAR2(240) not null,
  no_ord     NUMBER(12) not null,
  nature     VARCHAR2(3) not null,
  pk_value   VARCHAR2(4000),
  row_value  VARCHAR2(4000),
  ori        VARCHAR2(250),
  c_user     VARCHAR2(20),
  d_upd      DATE
);

create index AP_D_UPD on TableTemp (D_UPD);
create index AP_NO_ORD on TableTemp (NO_ORD);
create index AP_TABLE_NAME on TableTemp (TABLE_NAME);

• 假设您有一个简单的表 BANK，其中包含两列 PK_val(主键)和 val:

---

create table BANK (
  pk_val VARCHAR2(50) not null,
  val    VARCHAR2(240) not null
);

alter table BANK
  add constraint BK_PK primary key (pk_val)
  using index ;

• 使用 DBMS_APPLICATION_INFO.READ_MODULE(w_sess_mod, w_sess_act) 了解哪个模块和哪个操作在运行:我在 TableTemp< 的 ORI 列中将两者连接起来;

• user Oracle session 变量将允许您跟踪谁在 c_user 列中进行了更改；

• 下面是如何创建触发器 AUD_BNK 来跟踪表 BANK 中的变化；它将分为 3 个操作:DELETE、UPDATE、INSERT(如果需要，您可以删除 INSERT 大小写) .

---

CREATE OR REPLACE TRIGGER "AUD_BNK" 
AFTER DELETE OR INSERT OR UPDATE
   ON BANQUE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW

DECLARE
    w_a        VARCHAR2(10);
    W_ERRM     VARCHAR2(1000);
    W_CODE     VARCHAR2(1000);
    w_n        VARCHAR2(200) := 'BANK';
    w_id       NUMBER :=  a_sqnc.nextval;
    w_act      v$session.action%type;
    w_mod      v$session.module%type;
    w_ori      TableTemp.ORI%TYPE;

  BEGIN
  DBMS_APPLICATION_INFO.READ_MODULE(w_mod, w_act);
  w_ori := 'Module : '||w_mod ||' ; Action : '||w_act;
  ----------------------------------
  -- test which action is for change
  ----------------------------------
  IF UPDATING
  THEN
    w_a := 'UPDATE';
  ELSIF DELETING
  THEN
    w_a := 'DELETE';
  ELSIF INSERTING
  THEN
    w_a := 'INSERT';
  END IF;
  ----------------------------------
  -- Insert into TableTemp 
  ----------------------------------
If w_a in ('UPDATE', 'DELETE') then
  Insert into TableTemp 
       Select w_n, w_a, w_id, 'OLD', :OLD.pk_val, :OLD.val
            , w_ori, user, sysdate
         From Dual;
End if;

-- if you update, there is a new value and an old value
If w_a in ('UPDATE', 'INSERT') then
  Insert into TableTemp 
       Select w_n, w_a, w_id, 'NEW', :NEW.pk_val, :NEW.val
            , w_ori, user, sysdate
         From Dual;
End if;

Exception
When others then
  Begin
    W_ERRM := SQLERRM;
    W_CODE := SQLCODE;
    -- try inserting in case of error anyway
    Insert into TableTemp 
         Select w_n, w_a, -1, 'ERR', 'Grrr: '||W_CODE, W_ERRM
              , w_ori, user, sysdate
     From Dual;
  End;
End;
/

小心!

如果表发生变化，这种跟踪表上每个变化的方式将严重损害性能。但这对于几乎没有变化的参数表来说非常有用。