Database Triggers in PostgreSQL and Rails: How They Work and When to Use Them

Database triggers are logic that PostgreSQL runs automatically when a row is updated, inserted, or deleted.  Unlike Rails, triggers run at the database level. It is applicable in every case, no matter which app, script, or service writes to the table.

Triggers are suitable for audit trails, derived data, and rules that apply to every write to a database. If you're running a Ruby on Rails application on top of PostgreSQL, it is important to understand when and how to use database triggers. This article breaks down what database triggers are, how they compare to Rails, and the types.

What Are Database Triggers?

A database trigger is logic that runs automatically when a specific database event happens.

Once configured, the application does not need to call the trigger directly. PostgreSQL detects the relevant operation and executes the associated function.

Triggers respond to three core events:

• INSERT: a new row is added
• UPDATE: an existing row changes
• DELETE: a row is removed

They can also run at two different points in the lifecycle:

• BEFORE triggers: useful for validating or modifying data before it's written
• AFTER triggers: run once the original operation has already completed

Here is a simple flow:

Application

↓

SQL statement

↓

PostgreSQL

↓

Trigger executes

↓

Database operation continues

Triggers are especially useful when a database rule must work even if data is changed through direct SQL, a script, background process, or another application.

This is one of the most practical distinctions to understand, and it directly affects performance:

• A row-level trigger runs once for every affected row. An UPDATE touching 1,000 rows means 1,000 trigger executions.
• A statement-level trigger runs once for the entire SQL statement, regardless of how many rows it touches.

Since PostgreSQL 10, statement-level triggers can use transition tables to access every row affected by a statement in one pass:

For example:

CREATE TRIGGER users_permission_audit

AFTER UPDATE OF permissions ON users

FOR EACH ROW

EXECUTE FUNCTION audit_permission_change();

This trigger watches the permissions column in the users table.

When that column is updated, PostgreSQL runs the audit_permission_change() function.

The trigger defines when the action occurs. The function defines what PostgreSQL should do.

Types of Database Triggers

Understanding the main types of database triggers helps developers choose the right approach.

BEFORE Triggers

A BEFORE trigger runs before PostgreSQL completes an operation.

It can be useful for:

• Validating incoming values
• Changing data before storage
• Normalizing data
• Blocking invalid changes
• Applying rules before a row is written

For example:

CREATE TRIGGER normalize_email

BEFORE INSERT OR UPDATE ON customers

FOR EACH ROW

EXECUTE FUNCTION normalize_customer_email();

The function can change the new value before the row reaches the table.

AFTER Triggers

An AFTER trigger executes after the requested database action.

Common uses include:

• Creating audit entries
• Updating history tables
• Maintaining totals
• Updating related data
• Recording completed changes

For example:

CREATE TRIGGER users_permission_audit

AFTER UPDATE OF permissions ON users

FOR EACH ROW

EXECUTE FUNCTION audit_permission_change();

This works well when the trigger needs access to the completed change.

Row-Level Triggers

A row-level trigger uses:

FOR EACH ROW

It runs once for every affected row. If an SQL statement updates 1,000 rows, the trigger may execute 1,000 times. That makes row-level trigger performance important when working with large PostgreSQL databases.

Statement-Level Triggers

A statement-level trigger uses:

FOR EACH STATEMENT

It runs once for the entire SQL operation.

This can reduce repeated work during large updates, especially when the trigger does not need to process each row separately.

Transition Tables in PostgreSQL

PostgreSQL also supports transition tables for suitable statement-level triggers. They allow the function to work with sets of affected rows.

For example:

CREATE TRIGGER users_bulk_audit

AFTER UPDATE ON users

REFERENCING NEW TABLE AS new_rows OLD TABLE AS old_rows

FOR EACH STATEMENT

EXECUTE FUNCTION audit_bulk_update();

Instead of calling a function for every row, PostgreSQL can give the function access to the affected rows as a group. This can be useful for larger operations where row-by-row execution creates unnecessary overhead.

Trigger Functions in PostgreSQL

A trigger by itself doesn't do anything, it needs a trigger function attached to it. Here's a real example: auditing permission changes on a users table.

Suppose every change to a user's permissions must be recorded.

The function could look like this:

CREATE FUNCTION audit_permission_change()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO permission_audits (
        user_id,
        old_permissions,
        new_permissions,
        changed_at
    )
    VALUES (
        NEW.id,
        OLD.permissions,
        NEW.permissions,
        NOW()
    );

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Now attach it to the table:

CREATE TRIGGER users_permission_audit

AFTER UPDATE OF permissions ON users

FOR EACH ROW

WHEN (OLD.permissions IS DISTINCT FROM NEW.permissions)

EXECUTE FUNCTION audit_permission_change();

The UPDATE OF permissions condition limits when the trigger is considered.

The WHEN condition checks whether the value actually changed.

This prevents unnecessary audit records.

Common Uses of SQL Triggers

SQL triggers work best when an action must happen consistently at the database level.

Auditing

Triggers can automatically record changes to sensitive information.

For example, a permission audit could capture:

• User ID
• Previous permissions
• New permissions
• Time of change

This provides a database-level history that does not depend on Rails calling extra application code.

Data Integrity

Triggers can enforce rules that must apply to every writer. This can be helpful in enterprise databases where data may be updated through several systems. However, developers should use standard database constraints when possible. A CHECK, UNIQUE, or foreign key constraint is often easier to understand than custom trigger logic.

Derived Data

A trigger can keep stored calculations or aggregate values updated. For example, changing an order could update a related total. This can reduce repeated calculations, but developers must consider locking and concurrent updates.

History Tables

Triggers can save old row values before or after updates. This helps answer questions such as:

• What changed?
• When did it change?
• What value existed previously?
• How has a record changed over time?

Database Triggers vs. Rails Callbacks

Rails already provides Active Record callbacks.

For example:

class User < ApplicationRecord
  before_update :update_something

  private

  def update_something
    # application logic
  end
end

The callback works because Rails manages the update.

The flow looks like:

Rails callback

↓

Active Record

↓

SQL

↓

PostgreSQL

A database trigger works differently:

SQL

↓

PostgreSQL

↓

Trigger

This distinction is critical when designing a Ruby on Rails database.

Rails Callbacks Can Be Skipped

This operation can run callbacks:

user.update!(name: "John")

UPDATE users
SET name = 'Wick'
WHERE id = 1;

User.insert_all(...)
User.upsert_all(...)
User.where(...).update_all(...)
User.where(...).delete_all

This means a normal Rails application can already contain code paths that skip callbacks.

A database trigger does not depend on which Rails method caused the SQL.

When Should You Use Rails Instead

Triggers are not the best place for every type of logic. Application code is usually better for:

• Sending emails
• Calling APIs
• Authorization
• User notifications
• Complex workflows
• Background jobs
• Business processes involving several services

Triggers can make behavior less visible to Rails developers. Someone reading the model may not realize that updating one record also causes another database operation. For this reason, trigger logic should remain small and easy to understand.

Creating and Managing Triggers Through Rails Migrations

Active Record doesn't have a full database-agnostic API for triggers, so in practice, you write the SQL directly inside a migration:

class AddUserTimestampTrigger < ActiveRecord::Migration[8.0]
  def up
    execute <<~SQL
      CREATE FUNCTION update_user_timestamp()
      RETURNS TRIGGER AS $$
      BEGIN
        NEW.updated_at = NOW();
        RETURN NEW;
      END;
      $$ LANGUAGE plpgsql;

      CREATE TRIGGER users_updated_at
      BEFORE UPDATE ON users
      FOR EACH ROW
      EXECUTE FUNCTION update_user_timestamp();
    SQL
  end

  def down
    execute <<~SQL
      DROP TRIGGER IF EXISTS users_updated_at ON users;
      DROP FUNCTION IF EXISTS update_user_timestamp();
    SQL
  end
end

This keeps the trigger version-controlled alongside your Rails app, no more triggers that got created manually in production and quietly forgotten.

The schema.rb Trap

Here's a gotcha that catches even experienced Ruby on Rails development teams off guard: if your app uses the default schema_format = :ruby, schema.rb cannot represent PostgreSQL functions or triggers at all. It only understands tables, columns, indexes, and foreign keys.

That means rails db:schema:load which is what db:setup, db:test:prepare, and most CI pipelines actually run will silently build a database without your triggers. Nothing errors out. Your test suite just runs against a database that behaves differently from production.

There are two ways to fix this deliberately:

1. Switch config.active_record.schema_format to :sql, so structure.sql captures the trigger along with everything else.
2. Stay on schema.rb and use the fx gem, which teaches the schema dumper how to represent functions and triggers.

Either way, this is a deliberate decision, not something to discover after a production incident.

Triggers and Rails In-Memory Data

A BEFORE trigger can change data before PostgreSQL stores it. Rails may still hold the original value in memory.

For example:

user.update!(status: "active")

If the trigger modifies the final stored value, Rails may need to reload the record:

user.reload

This is important when application code immediately relies on values that database triggers can modify.

Database Triggers and Transactions

Triggers normally run inside the same transaction as the database operation that activates them.

For example:

BEGIN

   ↓

UPDATE users

   ↓

Trigger executes

   ↓

Audit record created

   ↓

COMMIT

If the transaction rolls back, the work performed by the trigger also rolls back. A trigger can also cause the original transaction to fail if its function raises an error. This makes triggers useful when related database actions must succeed or fail together.

Database Triggers and Concurrency

Because triggers run inside the same transaction, they're subject to normal PostgreSQL locking behavior. Consider two transactions updating different orders that belong to the same customer:

Transaction A: UPDATE order → Trigger → UPDATE customer totals

Transaction B: UPDATE order → Trigger → UPDATE customer totals

Both transactions now compete for the same customer row. The trigger has introduced shared state that both transactions need to update — and the more work a trigger does, the longer it holds locks on contested rows.

Practical rule of thumb: keep trigger logic small and predictable an audit insert or a counter update is fine. Expensive workflows, external API calls, or long-running processes belong in application code or a background job, not a trigger.

Database Trigger Performance

Performance problems often appear when trigger work is multiplied across large operations.

Watch for:

• Row-level triggers on bulk updates
• Multiple extra writes
• Expensive SQL inside functions
• Shared records updated by many transactions
• Recursive trigger chains
• Triggers firing when values have not actually changed

Use column restrictions, WHEN conditions, statement-level triggers, and transition tables where appropriate.

Database Triggers in Azure Database for PostgreSQL

Azure databases for PostgreSQL can also use PostgreSQL trigger functionality because the service runs PostgreSQL.

However, migrations to a managed database service should include more than tables and records.

Review:

• PostgreSQL version compatibility
• Database functions
• Trigger definitions
• Extensions
• Permissions
• Backup behavior
• Performance
• Upgrade planning

Teams moving trigger-heavy systems should include database-side logic in their migration plan.

When Should You Use Database Triggers

The real question isn't can this be a trigger it's should this rule live in the database at all.

Triggers make sense when:

• The rule must hold regardless of which application or service writes the data
• You need database-wide audit or history tracking
• You're maintaining derived or denormalized values
• You're enforcing invariants across multiple writers (Rails, background jobs, scripts, other services)

Triggers are the wrong tool when:

• The logic involves several domain concepts or a multi-step workflow
• Authorization decisions or external API calls are involved
• The business rule is really an application concern, not a data integrity concern

A simple decision framework:

Does this rule need to hold no matter which application writes the data?

   → Yes: consider enforcing it in the database (trigger)

   → No: prefer Rails/application code

The bottom line: Rails callbacks protect behavior within the Rails application. PostgreSQL triggers protect behavior at the database level. That distinction matters most when the same data can be touched by multiple services, background jobs, scripts, or direct SQL clients. This is increasingly the norm for enterprise databases running on shared PostgreSQL infrastructure, including managed platforms like Azure Database for PostgreSQL.

Final Thoughts

Database Triggers give PostgreSQL the ability to enforce important behavior directly at the database level. They are useful when a rule must apply to every writer, including Rails, scripts, background processes, services, and direct SQL.  Rails callbacks remain the better choice for application-level workflows such as emails, APIs, user notifications, and complex business processes. The goal is not to replace Rails callbacks with PostgreSQL triggers.

It is to place each rule where it belongs. If a requirement must survive every application path and every database writer, PostgreSQL may be the right layer. If the requirement describes how the application should behave, Rails is usually the clearer choice.

Frequently Asked Questions

What is a database trigger?

A database trigger is logic that runs automatically when a defined database event occurs. PostgreSQL triggers can respond to inserts, updates, deletes, and other supported operations. They are commonly used for audit logs, history records, data rules, and automatic database updates.

What are the main types of database triggers?

The main types of database triggers include BEFORE, AFTER, and INSTEAD OF triggers. They can also be row-level or statement-level. The correct choice depends on when the logic must execute and whether it needs to process individual rows or an entire SQL statement.

What are SQL triggers used for?

SQL triggers are used to perform automatic actions when database events occur. Common uses include auditing, history tracking, data validation, derived values, and rules that need to apply across different applications or direct database access.

What does “triggers on SQL” mean?

The triggers on SQL generally refers to automatic database logic connected to SQL operations. Trigger syntax differs across PostgreSQL, SQL Server, MySQL, Oracle, and other database systems, so developers should follow the documentation for the database they use.

Are PostgreSQL triggers better than Rails callbacks?

No option is always better. PostgreSQL triggers work well for database-wide rules, while Rails callbacks work well for application behavior. The correct choice depends on whether the logic must still run when Rails callbacks are bypassed.

Do database triggers run inside transactions?

Yes. PostgreSQL triggers normally execute as part of the transaction that caused them. If the surrounding transaction fails or rolls back, database changes made by the trigger also roll back.

Can database triggers affect performance?

Yes. Triggers can increase transaction time when they execute frequently, run expensive queries, update shared records, or perform too many additional writes. Bulk operations should always be tested before trigger-heavy logic reaches production.