[FIXED] How to create trigger that will insert into table old and new value on update/insert

Issue

I have looked a lot through the internet the past 3 hours but I cannot manage to make the trigger work. Here is my setup:

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;

There is a user USER1 that has granted UPDATE on view_faktura only. Making UPDATE statement on the view will change the underlying faktura table data.

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

What I want to do is log into another new table any UPDATEs that are done on the view (or underlying table):

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
                       );

Now, I try the trigger:

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"
  );

Can anyone help me with this?

Edit: Changed some quotation marks to " instead of '. "field changed?" I do not know how to refer to this.

Error I get (without any of the fields I do not know how to refer to in double quotations) is:

Error(2,98): PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge

Solution

Define your log table so that field_changed has a data type and a valid identifier:

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
);

Then you can define the trigger, using the :NEW and :OLD records to get the values and an END; statement to terminate the block:

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;
/

or, as an 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;
/

Then, after the update, the log table contains:

ID	FIELD_CHANGED	OLD_VALUE	NEW_VALUE	FAKTURANR	DATE_OF_CHANGE
1	EXTNR	111	21	2	2022-06-21 09:21:16

db<>fiddle here

---

Or for multiple columns, you can either create a second copy of the trigger and change the column names or use:

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<>fiddle here

Answered By - MT0

Answer Checked By - Robin (PHPFixing Admin)