Keeping a journal in Oracle

The idea is actually quite simple. Whenever a record in a table (here: employee) is updated with a new value in a field (here: salary), a trigger gets fired. This trigger stored the old and new value of salary in a seperate table.

I like this trigger. It is simple. It is straightforward.

create or replace TRIGGER employee_journal
  AFTER INSERT OR UPDATE OF salary ON employee
  FOR EACH ROW
BEGIN
  INSERT INTO audit_entry 
    (entry_date, entry_user, entry_text, old_value, new_value)
  VALUES
    (SYSDATE, USER, 'Salary Update ' || :NEW.ssn, 
     :OLD.salary,
     :NEW.salary);
END;

An alternative is a trigger that calls a stored procedure:

create or replace TRIGGER employee_journal2
  AFTER INSERT OR UPDATE OF salary ON employee
  FOR EACH ROW
  call sp_employee_journal(:OLD.salary,:NEW.salary,:NEW.SSN)

with the stored procedure:

create or replace procedure  sp_employee_journal (oud IN employee.salary%type, nieuw IN employee.salary%type, ssn IN employee.ssn%type) as
begin
  INSERT INTO audit_entry 
    (entry_date, entry_user, entry_text, old_value, new_value)
  VALUES
    (SYSDATE, USER, 'Salary Update ' || ssn,oud,nieuw);
end;