Learning Oracle PL/SQL

7.4. Keeping a Trail of Database Changes

Another of the security requirements for the library application states:

All changes in the actual catalog records should be auditable; the database will store "traceability" data—which librarian made what change, and when.

This is not an unusual requirement; keeping an audit trail is important to the security of many business operations. In Oracle, there are lots of ways of automatically recording this kind of information, and we'll look closely at one method that uses PL/SQL. I'll also mention several other built-in tracking mechanisms such as auditing features and Oracle's LogMiner utility.

7.4.1. Logging Data History Using Table-Level Triggers

A table-level trigger is a PL/SQL block that executes or fires automatically when data in the table changes. A trigger is not a program that you call from another program or from the command line; instead, it is a way of attaching programmer-defined logic to particular events. As such, a trigger is a great way to keep track of changes, because triggers will always fire^[6]; you can set up a trigger and more or less forget about it.

^[6] Triggers fire unless specifically disabled by the trigger owner or administrator via the ALTER TRIGGER statement.

The programmer can set up the trigger to run either immediately before the data changes or immediately after. Triggers set up to work the first way are known as BEFORE triggers; triggers set up to work the second way are known as AFTER triggers. ...