Is a log trigger the database transaction log?

No. It is application-defined trigger behavior that records selected changes in a chosen structure.

Does a history table always store both old and new values?

No. That depends on its design.

Does a recorded update time prove when a business change actually took effect?

No. Those can be different notions of time.

AI Glossary / Data engineering and architecture / Advanced

Log trigger

Definition

A log trigger is a database trigger used to record changes to a table in a separate log or history table. It can capture information associated with inserts, updates and deletions, according to its design. A history-oriented implementation may preserve successive versions of a row and their recorded time intervals. The term describes a design pattern rather than one standardized command that behaves identically in every database.

What it means

An ordinary current-state table may tell you a customer's present delivery preference without showing earlier preferences. A log trigger adds a mechanism for retaining selected change information automatically when covered database operations occur. The resulting history can help explain how a record reached its current state. However, the history answers only the questions its design supports. Recording a new value is different from recording both old and new values, and recording a database account is not necessarily the same as identifying the person who requested the change through an application.

How it works

The database invokes the configured trigger for specified events. The trigger uses the information available about the affected data and writes the chosen details to another table. PostgreSQL's documentation demonstrates an audit trigger that records insert, update and delete events with a timestamp, user and operation type. A version-history design can instead maintain successive row versions, closing an earlier recorded interval when a replacement becomes effective in that history. Designers must specify event coverage, identity, time meaning and rollback behavior. A table-change history should not be confused with the database engine's transaction log or a complete record of every attempted action.

Three practical examples

A product catalog records changes to a descriptive category so analysts can investigate why a later report differs from an earlier one.
A customer-service application retains selected preference changes to explain what the database recorded at a particular stage of a case.
An AI data-preparation workflow examines recorded label revisions while checking whether the history actually covers all relevant edits.

Think of it this way

Imagine keeping a change notebook beside a shared register. Each covered alteration causes a new note or version to be recorded. The notebook can explain the register's evolution, but only if the relevant changes are included and the notes retain enough context. It does not automatically prove who initiated an action or whether the recorded information matched the real world.

Worked example

Consider an original version-history illustration for item P7. Its category is recorded as Basic from 09:00 until 11:00, and Plus from 11:00 onward. Define intervals to include their start and exclude their end. A query for 10:30 selects Basic. A query for exactly 11:00 selects Plus, because the earlier interval excludes that boundary. There are two historical versions but only one current category under this model. If the application later discovers that the real-world change took effect at 10:45, the recorded database-change time alone does not establish that business-effective time. Additional fields and rules would be needed to represent the distinction. These times are stipulated examples, not claims about the timestamp behavior of a particular database product.

Illustrative case study

In a fictional catalog project, a team introduces an AI assistant that explains changes in product classifications. The assistant receives a history table populated by triggers. During review, staff find that an old bulk-import route was outside the original capture design, leaving a gap. They document the period for which the history is complete, test the import route and revise the assistant's wording so it does not present missing history as proof that no change occurred. They also test a rolled-back update to understand what remains recorded. The scenario shows why automatic capture must be verified against actual write paths before its records support confident explanations.

Common misconceptions and mistakes

A log trigger does not automatically create a complete, tamper-proof audit trail. Its coverage and protections must be established. Another mistake is claiming that timestamps always provide an unambiguous sequence for every change; the chosen clock and recording rules matter. A history of stored values also does not necessarily establish when those values were true outside the database. Finally, row-level examples should not be copied into another database without checking its trigger model and behavior for operations affecting many rows. This entry describes the pattern rather than supplying deployment-ready code.

Business use

The pattern is useful when explaining changes matters alongside knowing the current value. Begin with the questions people need answered: what changed, which record was affected, what previous information must be retained and what the recorded time means. Test representative inserts, updates, deletions, bulk changes and unsuccessful transactions. For AI-generated explanations, give the model the history's coverage limits so it can distinguish an absent record from evidence of no event. Consider ongoing storage and maintenance as part of the design, since a history table grows with captured activity rather than only with the number of current entities.

Check your understanding

Which category applied at exactly 11:00 in the toy recorded history?

Answer: Plus.

Why was Basic excluded then?

Answer: Its interval ended at 11:00 and excluded that endpoint.

How many versions were retained?

Answer: Two.

Why could the fictional assistant not treat a missing event as proof of no change?

Answer: The capture history had a known coverage gap.

Related terms

Tuple-versioning
Database trigger
audit trail
change data capture
temporal database
slowly changing dimension

Learn next

Design a small history table for one fictional item. State whether it records events or versions, define each timestamp and specify what happens at a version boundary. Then list the write paths that must be tested and one question the retained data cannot answer. Explain how an AI summary should communicate that limitation.

Sources

PostgreSQL documentation, Trigger Functions, auditing example: https://www.postgresql.org/docs/current/plpgsql-trigger.html The interval calculation, notebook analogy and catalogue case are original educational illustrations. The documentation supports trigger-based change recording; the entry describes a general log/history-trigger pattern and does not claim universal syntax, complete historical coverage or automatic audit integrity.

Previous: Log-sum-exp trick Next: Logarithm