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;