Skip to Main Content
Learning Oracle PL/SQL
book

Learning Oracle PL/SQL

by Bill Pribyl, Steven Feuerstein
November 2001
Beginner content levelBeginner
424 pages
11h 11m
English
O'Reilly Media, Inc.
Content preview from 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. ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Learning Oracle PL/SQL

Learning Oracle PL/SQL

Darryl Hurley
Oracle PL/SQL Programming, Third Edition

Oracle PL/SQL Programming, Third Edition

Steven Feuerstein, Bill Pribyl

Publisher Resources

ISBN: 0596001800Supplemental ContentCatalog PageErrata