如何創建將在更新/插入時插入表舊值和新值的觸發器

在過去的 3 個小時里，我在互聯網上看了很多，但我無法使觸發器作業。這是我的設定：

CREATE TABLE faktura ( 
                       extnr        VARCHAR2(32), 
                       fakturanr    NUMBER,
                       fakturadate  DATE, 
                       partner_name VARCHAR2(32)
                     )

INSERT INTO faktura(extnr, fakturanr, fakturadate, partner_name)
VALUES('1234/12', 1, to_date('01.01.2022', 'dd.mm.yyyy'), 'Global Sellers LTD');

INSERT INTO faktura(extnr, fakturanr, fakturadate, partner_name)
VALUES('111', 2, to_date('02.01.2022', 'dd.mm.yyyy'), 'Thomas Limited');

CREATE OR REPLACE VIEW view_faktura AS 
SELECT extnr, fakturanr, fakturadate 
  FROM faktura;

SELECT * 
  FROM faktura; 
  
SELECT * 
  FROM view_faktura;

有一個用戶 USER1 只授予了UPDATE權限view_faktura。在視圖上做UPDATE陳述句會改變底層faktura表資料。

UPDATE sol.view_faktura 
   SET extnr = '21' 
 WHERE fakturanr = '2'

我想要做的是登錄另一個新表，在視圖（或基礎表）上完成的任何更新：

CREATE TABLE log_table( 
                        id             NUMBER GENERATED BY DEFAULT AS IDENTITY, 
                        field changed?, 
                        old_value      VARCHAR2(50), 
                        new_value      VARCHAR2(50), 
                        fakturanr      NUMBER, 
                        date_of_change DATE
                       );

現在，我嘗試觸發：

create or replace trigger ChangeOnFaktura
  after update of "some column" on faktura "(or view_faktura)"
  for each row
declare
begin
  insert into log_table (
    old_value, new_value, date_of_change, "field changed?", fakturanr
  ) values (
    oldvalue, newvalue, sysdate, "field changed?", "fakturanr from the changed record"
  );

誰能幫我這個？

編輯：將一些引號更改為“而不是'。”欄位更改了？”我不知道如何參考這個。

我得到的錯誤（沒有任何我不知道如何在雙引號中參考的欄位）是：

錯誤（2,98）：PLS-00103：在預期以下情況之一時遇到符號“檔案結尾”：（開始案例宣告結束例外退出 goto if loop mod null pragma raise return select update while with <<繼續關閉當前洗掉獲取鎖插入打開回滾保存點設定sql執行提交forall合并管道清除

uj5u.com熱心網友回復：

定義您的日志表，使其field_changed具有資料型別和有效識別符號：

CREATE TABLE log_table( 
  id             NUMBER GENERATED BY DEFAULT AS IDENTITY, 
  field_changed  VARCHAR2(30), 
  old_value      VARCHAR2(32), 
  new_value      VARCHAR2(32), 
  fakturanr      NUMBER,
  date_of_change DATE
);

然后您可以定義觸發器，使用:NEW和:OLD記錄來獲取值和END;終止塊的陳述句：

create or replace trigger ChangeOnFaktura
  after update of extnr on faktura
  for each row
DECLARE
BEGIN
  insert into log_table (
    old_value,
    new_value,
    date_of_change,
    field_changed,
    fakturanr
  ) values (
    :OLD.extnr,
    :NEW.extnr,
    SYSDATE,
    'EXTNR',
    :NEW.fakturanr
  );
END;
/

或者，作為AUTONOMOUS_TRANSACTION：

create or replace trigger ChangeOnFaktura
  after update of extnr on faktura
  for each row
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  insert into log_table (
    old_value,
    new_value,
    date_of_change,
    field_changed,
    fakturanr
  ) values (
    :OLD.extnr,
    :NEW.extnr,
    SYSDATE,
    'EXTNR',
    :NEW.fakturanr
  );
  COMMIT;
END;
/

然后，更新后，日志表包含：

ID	FIELD_CHANGED	OLD_VALUE	NEW_VALUE	法克圖蘭	DATE_OF_CHANGE
1	EXTNR	111	21	2	2022-06-21 09:21:16

db<>在這里擺弄

或者對于多個列，您可以創建觸發器的第二個副本并更改列名或使用：

create or replace trigger ChangeOnFaktura
  after update on faktura
  for each row
BEGIN
  IF :OLD.extnr <> :NEW.extnr
  OR (:OLD.extnr IS NULL AND :NEW.extnr IS NOT NULL)
  OR (:OLD.extnr IS NOT NULL AND :NEW.extnr IS NULL)
  THEN
    insert into log_table (
      old_value,
      new_value,
      date_of_change,
      field_changed,
      fakturanr
    ) values (
      :OLD.extnr,
      :NEW.extnr,
      SYSDATE,
      'EXTNR',
      :NEW.fakturanr
    );
  END IF;
  
  IF :OLD.partner_name <> :NEW.partner_name
  OR (:OLD.partner_name IS NULL AND :NEW.partner_name IS NOT NULL)
  OR (:OLD.partner_name IS NOT NULL AND :NEW.partner_name IS NULL)
  THEN
    insert into log_table (
      old_value,
      new_value,
      date_of_change,
      field_changed,
      fakturanr
    ) values (
      :OLD.partner_name,
      :NEW.partner_name,
      SYSDATE,
      'PARTNER_NAME',
      :NEW.fakturanr
    );
  END IF;
END;
/

db<>在這里擺弄

標籤：sql 甲骨文 触发器 sql更新

上一篇：在Oracle中選擇整個表時，小數點后僅顯示4位

下一篇：JSON_ARRAYAGG回傳值帶“\”