Effective strategy for leaving an audit trail/change history for DB applications?

One strategy you could use is MVCC, Multi-Value Concurrency Control. In this scheme, you never do updates to any of your tables, you just do inserts, maintaining version numbers for each record. This has the advantage of providing an exact snapshot from any point in time, and it also completely sidesteps the update lock problems that plague many databases.

But it makes for a huge database, and selects all require an extra clause to select the current version of a record.

If you are using Hibernate, take a look at JBoss Envers. From the project homepage:

The Envers project aims to enable easy versioning of persistent JPA classes. All that you have to do is annotate your persistent class or some of its properties, that you want to version, with @Versioned. For each versioned entity, a table will be created, which will hold the history of changes made to the entity. You can then retrieve and query historical data without much effort.

This is somewhat similar to Eric's approach, but probably much less effort. Don't know, what language/technology you use to access the database, though.

In the past I have used triggers to construct db update/insert/delete logging.

You could insert a record each time one of the above actions is done on a specific table into a logging table that keeps track of the action, what db user did it, timestamp, table it was performed on, and previous value.

There is probably a better answer though as this would require you to cache the value before the actual delete or update was performed I think. But you could use this to do rollbacks.