Using AI to Update Audit Triggers After Schema Changes

A previous article explored how AI can take the drudgery out of repetitive database development tasks. Rather than writing SQL from scratch, the AI chatbot in Database Workbench can generate the required audit triggers from a simple prompt. The focus wasn't on replacing developers - it was on removing repetitive work so developers can spend more time solving business problems.

Generating an audit trigger is only the beginning. As a database evolves, tables change, new columns are added, and existing audit triggers need to evolve with them. This is another area where AI can save developers valuable time.

The Problem with Audit Triggers

Many databases use audit triggers to record changes made to important tables. They capture inserts, updates and deletes, storing who changed what and when.

The problem starts when the table changes.

• A new column gets added.
• Another column is renamed.
• A nullable column becomes mandatory.

The application evolves, but the audit trigger quietly falls behind.

Someone eventually notices that the new column isn't being audited, or worse, the trigger is silently set to being inactive and no longer compiles. Fixing it usually means manually comparing the table definition with the trigger source, identifying what's changed, and editing often lengthy trigger code. It's repetitive work that's easy to get wrong.

A Practical Workflow

Suppose you've added three new columns to a CUSTOMER table.

Traditionally you would:

1. Inspect the table definition.
2. Inspect the audit triggers.
3. Locate every INSERT and UPDATE section.
4. Add the new columns in multiple places.
5. Verify that everything still compiles.

It's a straightforward task, but also a repetitive one. The changes are usually mechanical, yet they still require careful comparison to ensure every new column is handled correctly.

With the AI chatbot, the process becomes much simpler. Instead of manually comparing the table definition with the audit trigger, the chatbot analyses both objects, identifies any missing or obsolete column references, and produces an updated version of the trigger.

The obvious question is: how does the chatbot know what has changed?

AI with Database Context

The answer lies in the way the AI chatbot is integrated into Database Workbench. Rather than relying solely on a text prompt, the chatbot can work with database objects supplied directly by the application.

When updating an audit trigger, Database Workbench provides the chatbot with both the current table definition and the existing audit trigger source. Instead of manually copying SQL between applications and describing the schema changes, the chatbot receives the required context automatically.

It compares the table definition with the trigger source, determines which columns have been added, removed or modified, and updates only the relevant parts of the trigger. The existing audit logic, formatting and structure are preserved, while the trigger is brought back into sync with the current table definition.

Because the chatbot starts with the actual database metadata and source code, it can focus on analysing the differences rather than trying to infer them from a written description.

AI as a Code Reviewer

One of the strengths of this approach is that AI isn't inventing a solution from scratch.

Instead, it's acting like an experienced developer performing a careful code review.

It compares two versions of the same design:

• What does the table contain?
• What does the trigger expect?
• Which columns are missing?
• Which references are now obsolete?

This comparison task is exactly the sort of repetitive analysis that large language models perform well, making it an ideal fit for maintaining database objects as schemas evolve.

Here's an example. We're connected to the SQLite Sakila demo database in Database Workbench and we fire up the database-aware AI Assistant chat and enter the following prompt:

-- Add missing columns to actor_audit table
ALTER TABLE actor_audit
ADD COLUMN e_mail VarChar(100);
ALTER TABLE actor_audit
ADD COLUMN title VarChar(20);

-- Drop existing audit triggers for actor
DROP TRIGGER IF EXISTS audit_actor_ai;
DROP TRIGGER IF EXISTS audit_actor_bd;
DROP TRIGGER IF EXISTS audit_actor_bu;

-- Create updated audit triggers for actor
CREATE TRIGGER audit_actor_ai AFTER INSERT ON actor
BEGIN
    INSERT INTO actor_audit (
        actor_id,
        first_name,
        last_name,
        e_mail,
        title,
        last_update,
        audit_trigger_action,
        audit_timestamp
    )
    VALUES (
        NEW.actor_id,
        NEW.first_name,
        NEW.last_name,
        NEW.e_mail,
        NEW.title,
        NEW.last_update,
        'INSERT',
        DATETIME('now')
    );
END;

CREATE TRIGGER audit_actor_bd BEFORE DELETE ON actor
BEGIN
    INSERT INTO actor_audit (
        actor_id,
        first_name,
        last_name,
        e_mail,
        title,
        last_update,
        audit_trigger_action,
        audit_timestamp
    )
    VALUES (
        OLD.actor_id,
        OLD.first_name,
        OLD.last_name,
        OLD.e_mail,
        OLD.title,
        OLD.last_update,
        'DELETE',
        DATETIME('now')
    );
END;

CREATE TRIGGER audit_actor_bu BEFORE UPDATE ON actor
BEGIN
    -- Insert old values
    INSERT INTO actor_audit (
        actor_id,
        first_name,
        last_name,
        e_mail,
        title,
        last_update,
        audit_trigger_action,
        audit_timestamp
    )
    VALUES (
        OLD.actor_id,
        OLD.first_name,
        OLD.last_name,
        OLD.e_mail,
        OLD.title,
        OLD.last_update,
        'UPDATE (OLD)',
        DATETIME('now')
    );
    -- Insert new values
    INSERT INTO actor_audit (
        actor_id,
        first_name,
        last_name,
        e_mail,
        title,
        last_update,
        audit_trigger_action,
        audit_timestamp
    )
    VALUES (
        NEW.actor_id,
        NEW.first_name,
        NEW.last_name,
        NEW.e_mail,
        NEW.title,
        NEW.last_update,
        'UPDATE (NEW)',
        DATETIME('now')
    );
END;

That looks like exactly the thing we want. We can now respond to the AI that it should do the other tables as well.

In this example, the AI checks the other tables and triggers, and finds they're all up to date. No modifications required.

That's four tables checked, in nearly the same time as it takes to check one, or ten or more.

The Bigger Picture

The previous article demonstrated how AI can generate audit triggers. This next step shows that AI can also help maintain them throughout the lifetime of a database.

Rather than simply generating new code, the AI chatbot can compare database metadata with existing source code, detect schema changes, and update only the objects that need to be changed.

By integrating AI directly into Database Workbench and providing it with the relevant database context, repetitive maintenance tasks become faster, more accurate, and significantly less tedious.

The AI isn't replacing the database developer.

It's eliminating another repetitive task that developers have always known was necessary, but never particularly enjoyed.

As database projects grow, that's where AI delivers its biggest value - not writing clever code, but keeping existing code accurate, consistent and up to date.