Building an Automated Audit Logging System with Triggers0%

Trigger Caveats, Cascading Risks & Performance

Building an Automated Audit Logging System with Triggers

Advanced14 min read•Updated: 2026-09-12

Building an Automated Audit Logging System with Triggers

Regulatory frameworks (such as HIPAA, SOC2, and PCI-DSS) require software systems to maintain an immutable, tamper-evident Audit Trail of all changes made to sensitive data.

Relying on application code to record audit entries is dangerous: developers might forget to add audit calls, or direct modifications executed via the MySQL CLI would bypass auditing completely.

Database Triggers guarantee that 100% of modifications are logged at the storage layer.

Step 1: Designing the Centralized Audit Table

1CREATE TABLE audit_log (
  audit_id BIGINT AUTO_INCREMENT PRIMARY KEY,
  table_name VARCHAR(50) NOT NULL,
  operation_type ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
  record_id INT NOT NULL,
  changed_by VARCHAR(100) NOT NULL,
  old_data JSON NULL,
  new_data JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
10) ENGINE = InnoDB;

By storing row snapshots in JSON, a single audit table can track changes across any table regardless of differing schema columns!

Step 2: Implementing the INSERT Audit Trigger

1DELIMITER //
2 
3CREATE TRIGGER trg_audit_products_insert
4AFTER INSERT ON products
5FOR EACH ROW
6BEGIN
7    INSERT INTO audit_log (
8        table_name, 
9        operation_type, 
10        record_id, 
11        changed_by, 
12        old_data, 
13        new_data
14    )
15    VALUES (
16        'products',
17        'INSERT',
18        NEW.product_id,
19        USER(), -- Captures 'username'@'hostname' of connecting client
20        NULL,
21        JSON_OBJECT(
22            'sku', NEW.sku,
23            'name', NEW.name,
24            'retail_price', NEW.retail_price
25        )
26    );
27END //
28 
29DELIMITER ;

Step 3: Implementing the UPDATE Audit Trigger

1DELIMITER //
2 
3CREATE TRIGGER trg_audit_products_update
4AFTER UPDATE ON products
5FOR EACH ROW
6BEGIN
7    -- Log update only if price or name actually changed
8    IF OLD.retail_price != NEW.retail_price OR OLD.name != NEW.name THEN
9        INSERT INTO audit_log (
10            table_name, 
11            operation_type, 
12            record_id, 
13            changed_by, 
14            old_data, 
15            new_data
16        )
17        VALUES (
18            'products',
19            'UPDATE',
20            NEW.product_id,
21            USER(),
22            JSON_OBJECT('name', OLD.name, 'retail_price', OLD.retail_price),
23            JSON_OBJECT('name', NEW.name, 'retail_price', NEW.retail_price)
24        );
25    END IF;
26END //
27 
28DELIMITER ;

Step 4: Implementing the DELETE Audit Trigger

1DELIMITER //
2 
3CREATE TRIGGER trg_audit_products_delete
4AFTER DELETE ON products
5FOR EACH ROW
6BEGIN
7    INSERT INTO audit_log (
8        table_name, 
9        operation_type, 
10        record_id, 
11        changed_by, 
12        old_data, 
13        new_data
14    )
15    VALUES (
16        'products',
17        'DELETE',
18        OLD.product_id,
19        USER(),
20        JSON_OBJECT('sku', OLD.sku, 'name', OLD.name, 'retail_price', OLD.retail_price),
21        NULL
22    );
23END //
24 
25DELIMITER ;

Querying the Audit History

Now, auditors can track the entire forensic history of any item:

1SELECT 
2    audit_id,
3    operation_type,
4    changed_by,
5    created_at,
6    JSON_UNQUOTE(JSON_EXTRACT(old_data, '$.retail_price')) AS old_price,
7    JSON_UNQUOTE(JSON_EXTRACT(new_data, '$.retail_price')) AS new_price
8FROM audit_log
9WHERE record_id = 501 AND table_name = 'products'
10ORDER BY created_at DESC;

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Why is database-level trigger auditing superior to application-level auditing?

Question 2

Which MySQL function captures the identity and host of the database user who performed the modification?

Question 3

Why is JSON an excellent format for storing row states in audit tables?

Question 4

Which trigger timing is appropriate for recording audit log entries after a successful modification?

Question 5

If the main table update is rolled back, what happens to the audit row inserted by the AFTER UPDATE trigger?

Next Lesson

Trigger Caveats, Cascading Risks & Performance

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Utilizing OLD and NEW Pseudo-Records	Trigger Caveats, Cascading Risks & Performance

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Trigger Caveats, Cascading Risks & Performance

Building an Automated Audit Logging System with Triggers

Advanced14 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Building an Automated Audit Logging System with Triggers

Regulatory frameworks (such as HIPAA, SOC2, and PCI-DSS) require software systems to maintain an immutable, tamper-evident Audit Trail of all changes made to sensitive data.

Relying on application code to record audit entries is dangerous: developers might forget to add audit calls, or direct modifications executed via the MySQL CLI would bypass auditing completely.

Database Triggers guarantee that 100% of modifications are logged at the storage layer.

Step 1: Designing the Centralized Audit Table

1CREATE TABLE audit_log (
  audit_id BIGINT AUTO_INCREMENT PRIMARY KEY,
  table_name VARCHAR(50) NOT NULL,
  operation_type ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
  record_id INT NOT NULL,
  changed_by VARCHAR(100) NOT NULL,
  old_data JSON NULL,
  new_data JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
10) ENGINE = InnoDB;

By storing row snapshots in JSON, a single audit table can track changes across any table regardless of differing schema columns!

Step 2: Implementing the INSERT Audit Trigger

1DELIMITER //
2 
3CREATE TRIGGER trg_audit_products_insert
4AFTER INSERT ON products
5FOR EACH ROW
6BEGIN
7    INSERT INTO audit_log (
8        table_name, 
9        operation_type, 
10        record_id, 
11        changed_by, 
12        old_data, 
13        new_data
14    )
15    VALUES (
16        'products',
17        'INSERT',
18        NEW.product_id,
19        USER(), -- Captures 'username'@'hostname' of connecting client
20        NULL,
21        JSON_OBJECT(
22            'sku', NEW.sku,
23            'name', NEW.name,
24            'retail_price', NEW.retail_price
25        )
26    );
27END //
28 
29DELIMITER ;

Step 3: Implementing the UPDATE Audit Trigger

1DELIMITER //
2 
3CREATE TRIGGER trg_audit_products_update
4AFTER UPDATE ON products
5FOR EACH ROW
6BEGIN
7    -- Log update only if price or name actually changed
8    IF OLD.retail_price != NEW.retail_price OR OLD.name != NEW.name THEN
9        INSERT INTO audit_log (
10            table_name, 
11            operation_type, 
12            record_id, 
13            changed_by, 
14            old_data, 
15            new_data
16        )
17        VALUES (
18            'products',
19            'UPDATE',
20            NEW.product_id,
21            USER(),
22            JSON_OBJECT('name', OLD.name, 'retail_price', OLD.retail_price),
23            JSON_OBJECT('name', NEW.name, 'retail_price', NEW.retail_price)
24        );
25    END IF;
26END //
27 
28DELIMITER ;

Step 4: Implementing the DELETE Audit Trigger

1DELIMITER //
2 
3CREATE TRIGGER trg_audit_products_delete
4AFTER DELETE ON products
5FOR EACH ROW
6BEGIN
7    INSERT INTO audit_log (
8        table_name, 
9        operation_type, 
10        record_id, 
11        changed_by, 
12        old_data, 
13        new_data
14    )
15    VALUES (
16        'products',
17        'DELETE',
18        OLD.product_id,
19        USER(),
20        JSON_OBJECT('sku', OLD.sku, 'name', OLD.name, 'retail_price', OLD.retail_price),
21        NULL
22    );
23END //
24 
25DELIMITER ;

Querying the Audit History

Now, auditors can track the entire forensic history of any item:

1SELECT 
2    audit_id,
3    operation_type,
4    changed_by,
5    created_at,
6    JSON_UNQUOTE(JSON_EXTRACT(old_data, '$.retail_price')) AS old_price,
7    JSON_UNQUOTE(JSON_EXTRACT(new_data, '$.retail_price')) AS new_price
8FROM audit_log
9WHERE record_id = 501 AND table_name = 'products'
10ORDER BY created_at DESC;

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Why is database-level trigger auditing superior to application-level auditing?

Question 2

Which MySQL function captures the identity and host of the database user who performed the modification?

Question 3

Why is JSON an excellent format for storing row states in audit tables?

Question 4

Which trigger timing is appropriate for recording audit log entries after a successful modification?

Question 5

If the main table update is rolled back, what happens to the audit row inserted by the AFTER UPDATE trigger?

Next Lesson

Trigger Caveats, Cascading Risks & Performance

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Utilizing OLD and NEW Pseudo-Records	Trigger Caveats, Cascading Risks & Performance

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next