You may think this is redundant, as many changes are logged in the databases journals, but the logs are meant for database recovery and aren’t easily accessible by user programs.
The Transaction History table is easily referenced and can be incorporated into end user reports.
Some of this is Banner specific, but even those can give you ideas for similar scripts to be used with other third-party Oracle applications.
These tips, tricks, and scripts were developed under various versions of Oracle and Banner, and mainly under Linux and AIX Unix, and you may need to modify them for your particular version or operating system.
In our example above, changes made to the Work Order table are recorded a Transaction History table.
Typically when creating audit trails, you’ll use AFTER triggers.
Triggers can be defined to run DML (Data Manipulation Language) actions such as INSERT, UPDATE, and DELETE.
Triggers help the database designer ensure certain actions, such as maintaining an audit file, are completed regardless of which program or user makes changes to the data.
The INSERTED table contains all the new values; whereas, the DELETED table contains old values.
Here is how the tables are used: A trigger is defined for a specific table and one or more events.
In MS SQL, this is T-SQL; whereas in Oracle it is PL/SQL.
Here are some common uses for triggers: You can use triggers to track changes made to tables.
Triggers are defined to run when these actions are executed on a specific table.