Trigger for audit logging in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the purpose of a trigger in audit logging?

A trigger automatically records changes made to a table, such as inserts, updates, or deletes, to keep an audit trail of data modifications.

beginner

Which SQL event types can a trigger listen to for audit logging?

Triggers can listen to INSERT, UPDATE, and DELETE events to capture changes for audit logging.

intermediate

What is a common structure of an audit log table?

An audit log table usually contains columns like action_type (INSERT/UPDATE/DELETE), changed_by (user), changed_at (timestamp), and old/new values of the data.

intermediate

How does a BEFORE trigger differ from an AFTER trigger in audit logging?

A BEFORE trigger runs before the data change happens, allowing validation or modification; an AFTER trigger runs after the change, ideal for recording the final state in audit logs.

beginner

Why is audit logging important in databases?

Audit logging helps track who changed what and when, supporting security, compliance, and troubleshooting by providing a history of data changes.

Which SQL trigger event is NOT typically used for audit logging?

A SELECT

B UPDATE

C DELETE

D INSERT

SELECT statements do not modify data, so triggers are not fired on SELECT for audit logging.

What information is essential to store in an audit log table?

A User who made the change

B Timestamp of the change

C Type of action performed

D All of the above

All these details help create a complete audit trail.

When should an AFTER trigger be used for audit logging?

A To validate data before change

B To record the final state after data change

C To prevent data changes

D To modify data before insertion

AFTER triggers run after the change, so they capture the final data state.

Which SQL statement creates a trigger for audit logging?

A CREATE TRIGGER

B CREATE PROCEDURE

C CREATE FUNCTION

D CREATE VIEW

CREATE TRIGGER defines a trigger that runs on data changes.

What is a risk of not having audit logging in a database?

A Easier data recovery

B Improved database performance

C Data changes cannot be tracked

D Automatic backups

Without audit logging, you cannot track who changed data or when.

Explain how a trigger can be used to implement audit logging in a database.

Think about what happens when data changes and how to capture that automatically.

Describe the key columns you would include in an audit log table and why.

Consider what information helps track who did what and when.