I do not know how to create log trigger

Question

I need to create log trigger for after update, after insert and before delete.

In the accounts_history table I have more rows than in the table of accounts and it confuses me

How to write that trigger?

I tried to do it but I did not succeed, This is how I created tables and sequences.

I'm a beginner in Oracle and plsql

I'm sorry if I did not explain my problem well.

create table accounts (
  id number,
  name varchar2(32),
  amount number,
  date date
);

create sequnce acc_seq_id
  start wtih 1
  increment by 1
  nocache
  nocycle;

create table accounts_history (
  id          number
, old_name    varchar2(32)
, new_name    varchar2(32)
, old_amount  number
, new_amount  number
, change_date date
);

My trigger for only after update

create or replace trigger after_update
after update
on accounts referencing new as new old as old
for each row

begin

iNSERT INTO account_history
(
id, 
name,
old_name,
amount,
old_amount,
date
)
values
(
 :old.id,
 :new.name,
 :old.old_name,
 :new.amount,
 :old.old_amount,
 sysdate
);
end;
/

The error:

  SQL> show error
  Errors for TRIGGER AFTER_UPDATE:

  LINE/COL ERROR
  -------- --------------------------------------------------------------
   2/1   PL/SQL: SQL Statement ignored
   9/2   PL/SQL: ORA-01747: invalid user.table.column, table.column, or
         column specification

   13/2  PLS-00049: bad bind variable 'OLD.ID'
   14/2  PLS-00049: bad bind variable 'NEW.NAME'
   15/2  PLS-00049: bad bind variable 'OLD.OLD_NAME'
   16/2  PLS-00049: bad bind variable 'NEW.AMOUNT'
   17/2  PLS-00049: bad bind variable 'OLD.OLD_AMOUNT'
   SQL> 

Answer 1

The values you insert come from the ACCOUNTS table, the columns you target come ACCOUNT_HISTORY table. You have mixed them up, which is why you get the ORA-01747 error. Try this:

create or replace trigger after_update
    after update on accounts 
    referencing new as new old as old    
   for each row
 begin
     INSERT INTO account_history
       (
    id, 
    new_name,
    old_name,
    new_amount,
    old_amount,
    change_date
)
values
(
     :old.id,
     :new.name,
     :old.name,
     :new.amount,
     :old.amount,
     sysdate
    );
End;
/