如何创建将插入更新/插入时的旧值和新值的触发器

Question

在过去的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;

有一个用户用户1仅在上授予 on view_faktura。在视图上进行更新语句将更改基础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：在期望以下一个时遇到符号“文件终止” ：（如果loop mod mod mod mod null pragma提出返回返回返回retalle with＆lt with＆lt with＆lt； LT

Answer 1

定义日志表以使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 and ：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;
/

或，作为automous_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	fakturanr	date_of_change
1	extnr	111	21	2	2022-06-21 09:21:16

db＆lt;＆gt; gt; fiddle 此处

---

或对于多列，您可以创建触发器的第二份副本并更改列名或使用：

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＆lt;＆gt;＆gt; fiddle 在这里