DEV Community

Athreya aka Maneshwar
Athreya aka Maneshwar

Posted on

Triggers in SQLite: Automating Logic Inside Your Database

#webdev #programming #database #architecture

Hello, I'm Maneshwar. I'm building git-lrc, an AI code reviewer that runs on every commit. It is free, unlimited, and source-available on Github. Star Us to help devs discover the project. Do give it a try and share your feedback for improving the product.

So far, we’ve looked at how SQLite lets you query smarter (subqueries), structure cleaner (views), and control IDs (autoincrement).

Now we move into something more powerful—something that lets your database react automatically.

That’s where triggers come in.

A trigger is code that runs automatically when something happens in your database.

You don’t call it.
You don’t manually execute it.
It just runs behind the scenes.

The Three Parts of a Trigger

Every trigger is built on three things:

1. Event (When it runs)

This is what activates the trigger:

  • INSERT
  • UPDATE
  • DELETE

2. Condition (Should it run?)

An optional check using a WHEN clause.

If the condition is false → trigger does nothing.

3. Action (What it does)

The actual SQL logic:

  • INSERT
  • UPDATE
  • DELETE
  • SELECT

Real-World Use Case: Preventing Invalid Data

Imagine you're managing a banking system.

You don’t want a user’s account balance to go negative accidentally.

Instead of relying only on application code, you can enforce this rule directly in the database using a trigger.

CREATE TRIGGER prevent_negative_balance
BEFORE UPDATE OF balance ON accounts
WHEN NEW.balance < 0
BEGIN
    SELECT RAISE(ABORT, 'Balance cannot be negative');
END;
Enter fullscreen mode Exit fullscreen mode

What’s happening here?

  • The trigger fires before the balance is updated.
  • It checks if the new balance is less than zero.
  • If true, it aborts the operation with an error message.

This ensures your data remains consistent no matter where the update originates.

Keeping Track: Audit Logs with Triggers

Another powerful use of triggers is maintaining an audit trail.

For example, tracking changes made to sensitive data like salaries or account details.

CREATE TRIGGER log_salary_update
AFTER UPDATE OF salary ON employees
BEGIN
    INSERT INTO salary_audit(employee_id, old_salary, new_salary, change_date)
    VALUES (OLD.id, OLD.salary, NEW.salary, datetime('now'));
END;
Enter fullscreen mode Exit fullscreen mode

Now every time a salary is updated:

  • The old and new values are recorded
  • You get a timestamp of the change
  • No manual logging required!

When Triggers Go Wrong

While triggers are powerful, they can also introduce complexity if not used carefully.

Here are a few pitfalls to watch out for:

  • Hidden logic: Since triggers run automatically, it can be hard to debug unexpected behavior.
  • Performance impact: Multiple triggers on large datasets can slow down operations.
  • Recursive triggers: A trigger that modifies a table may unintentionally fire another trigger, creating loops.

Best practice: Keep triggers simple, focused, and well-documented.

Triggers are like invisible guardians of your database, they watch, react, and enforce rules without being explicitly called.

When used wisely, they reduce redundancy, improve consistency, and make your system more robust.

But like any powerful tool, they demand discipline.

Overuse or misuse can lead to confusion and performance issues.

The key is balance: use triggers where they shine, and keep your logic transparent and maintainable.

git-lrc
*AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production.

git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free.*

Any feedback or contributors are welcome! It's online, source-available, and ready for anyone to use.

⭐ Star it on GitHub:

GitHub logo HexmosTech / git-lrc

Free, Unlimited AI Code Reviews That Run on Commit



AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production.

git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free.

See It In Action

See git-lrc catch serious security issues such as leaked credentials, expensive cloud operations, and sensitive material in log statements

git-lrc-intro-60s.mp4

Why

  • 🤖 AI agents silently break things. Code removed. Logic changed. Edge cases gone. You won't notice until production.
  • 🔍 Catch it before it ships. AI-powered inline comments show you exactly what changed and what looks wrong.
  • 🔁 Build a…
View on GitHub

Top comments (0)