Constraints and Triggers

Overview

Constraints and triggers represent two fundamental database mechanisms for maintaining data integrity and automating database behavior. Constraints define rules that data must satisfy to be valid, enforced at the database level regardless of the application making changes. Triggers are stored procedures that execute automatically in response to specific database events, enabling complex automated behaviors.

Database constraints operate as declarative rules. When an operation violates a constraint, the database rejects the entire transaction, preventing invalid data from entering the system. This differs from application-level validation, which can be bypassed by direct database access, SQL injection, or alternative clients. Constraints include primary keys, foreign keys, unique constraints, check constraints, and not-null constraints.

-- Constraint preventing invalid email formats
ALTER TABLE users ADD CONSTRAINT valid_email 
  CHECK (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

-- Foreign key constraint maintaining referential integrity
ALTER TABLE orders ADD CONSTRAINT fk_user 
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

Triggers execute procedural code automatically when specific data modification events occur. A trigger can fire before or after INSERT, UPDATE, or DELETE operations, either once per statement or once per affected row. Triggers enable audit logging, derived value calculation, complex validation logic, and cross-table synchronization that constraints cannot express.

The interaction between constraints and triggers determines database behavior. Constraints check before triggers can modify data, while after triggers execute only when constraints pass. This execution order creates dependencies that require careful design.

Key Principles

Database constraints enforce rules through five primary types, each serving distinct integrity requirements.

Primary Key Constraints uniquely identify each row in a table. The database creates an index automatically and rejects any INSERT or UPDATE that would create duplicate values or NULL values in the primary key column. Composite primary keys combine multiple columns, requiring the combination to be unique.

Foreign Key Constraints maintain referential integrity between tables. A foreign key column must contain either NULL or a value that exists in the referenced table's primary key or unique column. Foreign keys support cascading actions: ON DELETE CASCADE removes dependent rows when the referenced row deletes, while ON DELETE SET NULL sets the foreign key to NULL instead.

Unique Constraints prevent duplicate values in columns that are not primary keys. Unlike primary keys, unique constraints permit NULL values, and most databases allow multiple NULL values since NULL represents unknown, not a specific value.

Check Constraints enforce arbitrary boolean conditions. The constraint evaluates an expression for each row, rejecting operations where the expression returns false. Check constraints can reference multiple columns within the same row but cannot reference other rows or tables.

-- Check constraint ensuring price logic
ALTER TABLE products ADD CONSTRAINT valid_pricing 
  CHECK (sale_price IS NULL OR sale_price < regular_price);

-- Check constraint with multiple conditions
ALTER TABLE employees ADD CONSTRAINT valid_employee
  CHECK (
    (status = 'active' AND termination_date IS NULL) OR
    (status = 'terminated' AND termination_date IS NOT NULL)
  );

Not-Null Constraints require columns to contain values, preventing NULL. This represents the simplest constraint type but proves essential for data integrity, ensuring required fields always contain data.

Triggers operate through an event-driven execution model. Each trigger associates with a specific table and event combination. The database maintains a trigger registry and invokes matching triggers when events occur.

Trigger Timing determines when execution occurs relative to the triggering operation. BEFORE triggers execute before the database modifies data, allowing the trigger to validate or transform values. AFTER triggers execute after successful data modification, useful for logging or cascading changes. INSTEAD OF triggers replace the triggering operation entirely, commonly used with views to make them updatable.

Trigger Granularity defines execution frequency. Row-level triggers fire once per affected row, receiving OLD and NEW record variables representing the row before and after modification. Statement-level triggers fire once per SQL statement, regardless of affected row count, receiving no row-specific data.

Trigger Events specify which operations activate the trigger: INSERT, UPDATE, or DELETE. A single trigger can respond to multiple events, though separate triggers for each event often provide clearer logic.

-- Row-level BEFORE trigger for automatic timestamping
CREATE TRIGGER update_timestamp
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_modified_column();

-- Statement-level AFTER trigger for audit logging
CREATE TRIGGER audit_changes
AFTER INSERT OR UPDATE OR DELETE ON sensitive_data
FOR EACH STATEMENT
EXECUTE FUNCTION log_data_changes();

Trigger execution order becomes critical when multiple triggers respond to the same event. Most databases execute triggers alphabetically by name when timing and event match, though some provide explicit ordering mechanisms. Triggers can modify data, causing additional triggers to fire, creating trigger chains that require careful management to prevent infinite recursion.

The OLD and NEW pseudo-records provide access to row data. OLD contains the row state before modification (available in UPDATE and DELETE triggers). NEW contains the row state after modification (available in INSERT and UPDATE triggers). BEFORE triggers can modify NEW values, affecting the final stored data.

Ruby Implementation

Ruby applications interact with database constraints and triggers primarily through ActiveRecord, the ORM included in Rails. ActiveRecord provides abstraction layers for constraint management but requires raw SQL for trigger implementation.

Defining Constraints in Migrations

ActiveRecord migrations support constraint creation through dedicated methods and raw SQL execution. The migration DSL handles common constraints declaratively.

class CreateUsers < ActiveRecord::Migration[7.0]
  def change
    create_table :users do |t|
      t.string :email, null: false
      t.string :username, null: false
      t.integer :age
      
      t.timestamps
    end
    
    # Unique constraint
    add_index :users, :email, unique: true
    add_index :users, :username, unique: true
    
    # Check constraint (PostgreSQL)
    execute <<-SQL
      ALTER TABLE users ADD CONSTRAINT age_requirement 
        CHECK (age IS NULL OR age >= 13);
    SQL
  end
end

Foreign key constraints receive first-class support in modern ActiveRecord versions. The add_foreign_key method creates constraints with cascading options.

class CreateOrders < ActiveRecord::Migration[7.0]
  def change
    create_table :orders do |t|
      t.references :user, null: false, foreign_key: true
      t.decimal :total, precision: 10, scale: 2, null: false
      t.string :status, null: false, default: 'pending'
      
      t.timestamps
    end
    
    # Foreign key with cascade delete
    add_foreign_key :orders, :users, on_delete: :cascade
    
    # Check constraint for valid status
    execute <<-SQL
      ALTER TABLE orders ADD CONSTRAINT valid_status
        CHECK (status IN ('pending', 'processing', 'completed', 'cancelled'));
    SQL
  end
end

Constraint Interaction with Validations

ActiveRecord validations run in application memory before database operations. Constraints operate at the database level. This creates a two-layer validation system with distinct characteristics.

class User < ApplicationRecord
  # Application-level validations
  validates :email, presence: true, uniqueness: true, 
            format: { with: URI::MailTo::EMAIL_REGEXP }
  validates :username, presence: true, uniqueness: true
  validates :age, numericality: { greater_than_or_equal_to: 13 }, 
            allow_nil: true
end

Application validations provide user-friendly error messages and run before database round trips, improving performance. However, race conditions between validation checks and database writes can occur, especially with uniqueness validations. Database constraints guarantee integrity even when validations are bypassed through save(validate: false), console operations, or bulk SQL imports.

# Application validation can miss race condition
user1 = User.new(email: 'test@example.com')
user2 = User.new(email: 'test@example.com')

# Both pass validation checking database
user1.valid? # => true (no duplicate exists yet)
user2.valid? # => true (no duplicate exists yet)

# First save succeeds
user1.save # => true

# Second save fails at database constraint
user2.save # => false
user2.errors.full_messages # => ["Email has already been taken"]
# Constraint prevented invalid data despite validation passing

Managing Constraint Exceptions

When database constraints reject operations, ActiveRecord raises specific exception types that applications can rescue and handle.

class OrdersController < ApplicationController
  def create
    @order = Order.new(order_params)
    
    begin
      @order.save!
      redirect_to @order, notice: 'Order created successfully'
    rescue ActiveRecord::RecordInvalid => e
      # Application validation failed
      flash.now[:error] = "Validation error: #{e.message}"
      render :new
    rescue ActiveRecord::InvalidForeignKey => e
      # Foreign key constraint violation
      flash.now[:error] = "Referenced user does not exist"
      render :new
    rescue ActiveRecord::NotNullViolation => e
      # NOT NULL constraint violation
      flash.now[:error] = "Required field missing: #{e.message}"
      render :new
    end
  end
end

Implementing Triggers in Ruby Applications

Ruby applications create triggers using raw SQL in migrations, as ActiveRecord provides no DSL for trigger management. The execute method runs arbitrary SQL during migration.

class AddUpdateTimestampTrigger < ActiveRecord::Migration[7.0]
  def up
    # Create trigger function (PostgreSQL)
    execute <<-SQL
      CREATE OR REPLACE FUNCTION update_updated_at_column()
      RETURNS TRIGGER AS $$
      BEGIN
        NEW.updated_at = NOW();
        RETURN NEW;
      END;
      $$ LANGUAGE plpgsql;
    SQL
    
    # Create trigger
    execute <<-SQL
      CREATE TRIGGER update_users_timestamp
      BEFORE UPDATE ON users
      FOR EACH ROW
      EXECUTE FUNCTION update_updated_at_column();
    SQL
  end
  
  def down
    execute "DROP TRIGGER IF EXISTS update_users_timestamp ON users"
    execute "DROP FUNCTION IF EXISTS update_updated_at_column()"
  end
end

Audit logging represents a common trigger use case, automatically recording changes without application code modifications.

class AddAuditTrigger < ActiveRecord::Migration[7.0]
  def up
    # Create audit table
    create_table :user_audits do |t|
      t.integer :user_id
      t.string :action
      t.jsonb :old_values
      t.jsonb :new_values
      t.datetime :changed_at
      t.integer :changed_by
    end
    
    # Create audit trigger function
    execute <<-SQL
      CREATE OR REPLACE FUNCTION audit_user_changes()
      RETURNS TRIGGER AS $$
      BEGIN
        IF (TG_OP = 'UPDATE') THEN
          INSERT INTO user_audits (user_id, action, old_values, new_values, changed_at)
          VALUES (OLD.id, 'UPDATE', row_to_json(OLD), row_to_json(NEW), NOW());
          RETURN NEW;
        ELSIF (TG_OP = 'DELETE') THEN
          INSERT INTO user_audits (user_id, action, old_values, changed_at)
          VALUES (OLD.id, 'DELETE', row_to_json(OLD), NOW());
          RETURN OLD;
        END IF;
        RETURN NULL;
      END;
      $$ LANGUAGE plpgsql;
    SQL
    
    execute <<-SQL
      CREATE TRIGGER user_audit_trigger
      AFTER UPDATE OR DELETE ON users
      FOR EACH ROW
      EXECUTE FUNCTION audit_user_changes();
    SQL
  end
  
  def down
    execute "DROP TRIGGER IF EXISTS user_audit_trigger ON users"
    execute "DROP FUNCTION IF EXISTS audit_user_changes()"
    drop_table :user_audits
  end
end

Working with Triggers Through ActiveRecord

ActiveRecord callbacks provide an application-level alternative to database triggers, executing Ruby code around model lifecycle events.

class Order < ApplicationRecord
  belongs_to :user
  has_many :order_items
  
  # Callbacks execute in Ruby process
  before_create :generate_order_number
  after_create :send_confirmation_email
  after_update :log_status_change, if: :saved_change_to_status?
  
  private
  
  def generate_order_number
    self.order_number = "ORD-#{Time.current.strftime('%Y%m%d')}-#{SecureRandom.hex(4)}"
  end
  
  def send_confirmation_email
    OrderMailer.confirmation(self).deliver_later
  end
  
  def log_status_change
    Rails.logger.info "Order #{id} status changed from #{status_was} to #{status}"
  end
end

Callbacks differ from triggers in execution context. Callbacks run in application memory, can access Ruby objects and services, but skip when using bulk operations or SQL updates. Triggers run in the database, execute for all modifications regardless of source, but cannot access Ruby code or external services.

Practical Examples

Ensuring Data Integrity Across Tables

Consider an e-commerce system maintaining inventory counts. When orders are created or cancelled, inventory must remain consistent. Constraints and triggers work together to guarantee accuracy.

# Migration creating schema with constraints
class CreateInventorySystem < ActiveRecord::Migration[7.0]
  def change
    create_table :products do |t|
      t.string :name, null: false
      t.decimal :price, precision: 10, scale: 2, null: false
      t.integer :stock_quantity, null: false, default: 0
      
      t.timestamps
    end
    
    create_table :order_items do |t|
      t.references :order, null: false, foreign_key: { on_delete: :cascade }
      t.references :product, null: false, foreign_key: true
      t.integer :quantity, null: false
      t.decimal :price_at_purchase, precision: 10, scale: 2, null: false
      
      t.timestamps
    end
    
    # Check constraint preventing negative stock
    execute <<-SQL
      ALTER TABLE products ADD CONSTRAINT non_negative_stock
        CHECK (stock_quantity >= 0);
    SQL
    
    # Check constraint ensuring positive quantities
    execute <<-SQL
      ALTER TABLE order_items ADD CONSTRAINT positive_quantity
        CHECK (quantity > 0);
    SQL
    
    # Trigger to decrement stock when order items created
    execute <<-SQL
      CREATE OR REPLACE FUNCTION decrement_product_stock()
      RETURNS TRIGGER AS $$
      BEGIN
        UPDATE products
        SET stock_quantity = stock_quantity - NEW.quantity
        WHERE id = NEW.product_id;
        
        IF NOT FOUND THEN
          RAISE EXCEPTION 'Product % not found', NEW.product_id;
        END IF;
        
        RETURN NEW;
      END;
      $$ LANGUAGE plpgsql;
    SQL
    
    execute <<-SQL
      CREATE TRIGGER decrement_stock_on_order
      AFTER INSERT ON order_items
      FOR EACH ROW
      EXECUTE FUNCTION decrement_product_stock();
    SQL
    
    # Trigger to restore stock when order items deleted
    execute <<-SQL
      CREATE OR REPLACE FUNCTION restore_product_stock()
      RETURNS TRIGGER AS $$
      BEGIN
        UPDATE products
        SET stock_quantity = stock_quantity + OLD.quantity
        WHERE id = OLD.product_id;
        
        RETURN OLD;
      END;
      $$ LANGUAGE plpgsql;
    SQL
    
    execute <<-SQL
      CREATE TRIGGER restore_stock_on_delete
      AFTER DELETE ON order_items
      FOR EACH ROW
      EXECUTE FUNCTION restore_product_stock();
    SQL
  end
end

The check constraint on products.stock_quantity prevents negative values, ensuring the trigger logic cannot create invalid inventory states. If an order attempts to purchase more items than available, the stock update causes the constraint violation, rolling back the entire transaction.

# Application code remains simple
product = Product.create!(name: 'Widget', price: 19.99, stock_quantity: 10)
order = Order.create!(user: current_user)

# Trigger automatically decrements stock
OrderItem.create!(order: order, product: product, quantity: 3, price_at_purchase: product.price)
product.reload.stock_quantity # => 7

# Constraint prevents overselling
OrderItem.create!(order: order, product: product, quantity: 15, price_at_purchase: product.price)
# ActiveRecord::StatementInvalid: PG::CheckViolation: 
# ERROR: new row for relation "products" violates check constraint "non_negative_stock"

Implementing Soft Deletes with Trigger Automation

Soft delete patterns mark records as deleted rather than removing them, requiring updates to dependent records. Triggers automate this cascade logic.

class AddSoftDeleteSystem < ActiveRecord::Migration[7.0]
  def change
    add_column :users, :deleted_at, :datetime
    add_column :posts, :deleted_at, :datetime
    add_index :users, :deleted_at
    add_index :posts, :deleted_at
    
    # Trigger cascading soft delete from users to posts
    execute <<-SQL
      CREATE OR REPLACE FUNCTION cascade_soft_delete_posts()
      RETURNS TRIGGER AS $$
      BEGIN
        IF NEW.deleted_at IS NOT NULL AND OLD.deleted_at IS NULL THEN
          UPDATE posts
          SET deleted_at = NEW.deleted_at
          WHERE user_id = NEW.id AND deleted_at IS NULL;
        END IF;
        
        RETURN NEW;
      END;
      $$ LANGUAGE plpgsql;
    SQL
    
    execute <<-SQL
      CREATE TRIGGER soft_delete_cascade
      AFTER UPDATE ON users
      FOR EACH ROW
      WHEN (NEW.deleted_at IS NOT NULL AND OLD.deleted_at IS NULL)
      EXECUTE FUNCTION cascade_soft_delete_posts();
    SQL
  end
end

The trigger detects user soft deletion by comparing OLD.deleted_at and NEW.deleted_at, then automatically marks associated posts as deleted with the same timestamp.

class User < ApplicationRecord
  has_many :posts
  
  def soft_delete
    update(deleted_at: Time.current)
  end
  
  def restore
    update(deleted_at: nil)
  end
end

# Usage
user = User.create!(name: 'John')
user.posts.create!(title: 'First Post')
user.posts.create!(title: 'Second Post')

user.soft_delete
# Trigger automatically soft deletes all posts
user.posts.reload.all?(&:deleted_at) # => true

Enforcing Complex Business Rules

Some business rules require checking multiple columns or conditions that application code might miss. Check constraints enforce these rules atomically.

class AddSubscriptionConstraints < ActiveRecord::Migration[7.0]
  def change
    create_table :subscriptions do |t|
      t.references :user, null: false, foreign_key: true
      t.string :plan_type, null: false
      t.date :start_date, null: false
      t.date :end_date
      t.integer :max_projects
      t.boolean :active, null: false, default: true
      
      t.timestamps
    end
    
    # End date must be after start date
    execute <<-SQL
      ALTER TABLE subscriptions ADD CONSTRAINT valid_date_range
        CHECK (end_date IS NULL OR end_date > start_date);
    SQL
    
    # Active subscriptions must have valid dates
    execute <<-SQL
      ALTER TABLE subscriptions ADD CONSTRAINT active_subscription_rules
        CHECK (
          (active = false) OR 
          (active = true AND end_date IS NULL) OR
          (active = true AND end_date > CURRENT_DATE)
        );
    SQL
    
    # Plan types have specific project limits
    execute <<-SQL
      ALTER TABLE subscriptions ADD CONSTRAINT plan_type_limits
        CHECK (
          (plan_type = 'free' AND max_projects <= 3) OR
          (plan_type = 'basic' AND max_projects <= 10) OR
          (plan_type = 'premium' AND max_projects IS NULL)
        );
    SQL
  end
end

These constraints prevent invalid subscription states regardless of how data enters the system - through application forms, admin panels, background jobs, or SQL scripts.

Automatic Denormalization for Performance

Triggers maintain denormalized data automatically, avoiding application code complexity while improving read performance.

class AddDenormalizedCounts < ActiveRecord::Migration[7.0]
  def change
    add_column :users, :posts_count, :integer, null: false, default: 0
    add_column :posts, :comments_count, :integer, null: false, default: 0
    
    # Initialize existing counts
    execute <<-SQL
      UPDATE users SET posts_count = (
        SELECT COUNT(*) FROM posts WHERE posts.user_id = users.id
      );
    SQL
    
    execute <<-SQL
      UPDATE posts SET comments_count = (
        SELECT COUNT(*) FROM comments WHERE comments.post_id = posts.id
      );
    SQL
    
    # Trigger maintaining posts_count
    execute <<-SQL
      CREATE OR REPLACE FUNCTION update_posts_count()
      RETURNS TRIGGER AS $$
      BEGIN
        IF TG_OP = 'INSERT' THEN
          UPDATE users SET posts_count = posts_count + 1 WHERE id = NEW.user_id;
        ELSIF TG_OP = 'DELETE' THEN
          UPDATE users SET posts_count = posts_count - 1 WHERE id = OLD.user_id;
        ELSIF TG_OP = 'UPDATE' AND NEW.user_id != OLD.user_id THEN
          UPDATE users SET posts_count = posts_count - 1 WHERE id = OLD.user_id;
          UPDATE users SET posts_count = posts_count + 1 WHERE id = NEW.user_id;
        END IF;
        
        RETURN COALESCE(NEW, OLD);
      END;
      $$ LANGUAGE plpgsql;
    SQL
    
    execute <<-SQL
      CREATE TRIGGER maintain_posts_count
      AFTER INSERT OR UPDATE OR DELETE ON posts
      FOR EACH ROW
      EXECUTE FUNCTION update_posts_count();
    SQL
  end
end

The trigger keeps posts_count synchronized with actual post counts, even handling edge cases like post reassignment between users.

Design Considerations

Constraint Placement Decisions

The choice between application validations and database constraints involves trade-offs in user experience, performance, and data integrity guarantees.

Application validations provide immediate feedback with friendly error messages, execute before expensive database operations, and allow complex validation logic using business objects and external services. However, validations can be bypassed through console operations, bulk imports, or direct SQL, and race conditions between validation checks and database writes create potential integrity violations.

Database constraints guarantee integrity for all data modification paths, prevent race conditions through transaction isolation, and execute efficiently in the database engine. However, constraint violations produce generic error messages, require additional code to translate database errors into user-friendly messages, and cannot access application-level services or business logic.

# Validation provides user-friendly error
class User < ApplicationRecord
  validates :email, format: { 
    with: URI::MailTo::EMAIL_REGEXP,
    message: "must be a valid email address like user@example.com"
  }
end

user = User.new(email: 'invalid')
user.valid? # => false
user.errors.full_messages # => ["Email must be a valid email address like user@example.com"]

# Constraint provides integrity guarantee
# ALTER TABLE users ADD CONSTRAINT valid_email 
#   CHECK (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

user = User.new(email: 'invalid')
user.save(validate: false) # bypasses validation
# ActiveRecord::StatementInvalid: PG::CheckViolation: 
# ERROR: new row for relation "users" violates check constraint "valid_email"

The optimal approach implements both layers: application validations for user experience and constraint enforcement for data integrity. Critical integrity rules require database constraints, while user-facing validations can remain application-only.

Trigger Necessity Evaluation

Triggers introduce complexity and potential maintenance challenges. Several factors determine whether triggers are appropriate for a use case.

Use triggers when logic must execute for all modification sources, including direct SQL, background jobs, and administrative tools. Triggers excel at maintaining derived data, enforcing complex referential integrity beyond foreign keys, and implementing audit logging that cannot be circumvented.

Avoid triggers for logic that requires external service access, as triggers cannot call HTTP APIs or send emails directly. Avoid triggers for complex business logic better expressed in application code where debugging tools and testing frameworks are available. Avoid triggers for operations that might be selectively applied based on context, as triggers fire unconditionally.

# Good trigger use case: automatic timestamping
# Cannot be bypassed, no external dependencies, simple logic
CREATE TRIGGER update_timestamp
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

# Poor trigger use case: sending notification emails
# Requires external service, needs conditional logic
# Better implemented as ActiveRecord callback:
class Order < ApplicationRecord
  after_create :send_confirmation_email, if: :customer_notification_enabled?
  
  def send_confirmation_email
    OrderMailer.confirmation(self).deliver_later
  end
end

Performance Impact Analysis

Constraints and triggers affect database performance differently depending on design choices.

Foreign key constraints require index lookups to verify referenced values exist, adding overhead to INSERT and UPDATE operations. The referenced table's primary key should be indexed (happens automatically) to minimize lookup cost. Cascading deletes can impact performance significantly when deleting rows with many dependent records.

Check constraints evaluate expressions on each affected row. Simple column checks like age >= 0 execute quickly, while complex expressions involving subqueries can slow operations substantially. Avoid check constraints that perform joins or aggregate queries.

Triggers execute on every qualifying operation, multiplying performance impact by affected row count. Row-level triggers fire once per row, creating performance concerns for bulk operations. Statement-level triggers fire once per statement but cannot access individual row data. For high-throughput tables, measure trigger execution time and consider alternatives like batch processing or application-level logic.

# High-impact trigger on high-volume table
# Fires thousands of times during bulk import
CREATE TRIGGER calculate_derived_fields
BEFORE INSERT ON events
FOR EACH ROW
EXECUTE FUNCTION process_event_data();

# Lower-impact alternative: batch processing
# Process in chunks after import completes
class EventProcessor
  def self.process_batch(start_id, end_id)
    Event.where(id: start_id..end_id).find_each do |event|
      event.calculate_derived_fields
      event.save
    end
  end
end

Maintainability and Testing Challenges

Triggers create invisible behavior that complicates testing and debugging. When data changes unexpectedly, developers must search migrations for trigger definitions. Trigger logic cannot be tested through standard unit test frameworks, requiring integration tests that set up database state and verify trigger effects.

Document all triggers clearly in schema documentation and consider maintaining a registry of triggers with their purposes. Include trigger behavior in model tests even though triggers are not part of the model code.

# Test verifying trigger behavior
RSpec.describe Product do
  describe 'inventory trigger' do
    it 'decrements stock when order item created' do
      product = Product.create!(name: 'Widget', stock_quantity: 10, price: 19.99)
      order = Order.create!(user: create(:user))
      
      expect {
        OrderItem.create!(
          order: order,
          product: product,
          quantity: 3,
          price_at_purchase: product.price
        )
      }.to change { product.reload.stock_quantity }.from(10).to(7)
    end
    
    it 'prevents negative stock through constraint' do
      product = Product.create!(name: 'Widget', stock_quantity: 5, price: 19.99)
      order = Order.create!(user: create(:user))
      
      expect {
        OrderItem.create!(
          order: order,
          product: product,
          quantity: 10,
          price_at_purchase: product.price
        )
      }.to raise_error(ActiveRecord::StatementInvalid, /non_negative_stock/)
    end
  end
end

Implementation Approaches

Constraint-Heavy Strategy

This approach maximizes database constraint usage, treating the database as the authoritative source of validity rules. Applications become thin clients that submit data and handle constraint violations.

Implement comprehensive check constraints for value ranges, format requirements, and business rules. Use foreign keys extensively with appropriate cascade actions. Create unique constraints on all business identifiers, not just primary keys.

class CreateConstraintHeavySchema < ActiveRecord::Migration[7.0]
  def change
    create_table :users do |t|
      t.string :email, null: false
      t.string :username, null: false
      t.date :birthdate, null: false
      t.string :country_code, null: false
      
      t.timestamps
    end
    
    add_index :users, :email, unique: true
    add_index :users, :username, unique: true
    
    execute <<-SQL
      ALTER TABLE users 
      ADD CONSTRAINT valid_email CHECK (email ~* '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}$'),
      ADD CONSTRAINT valid_username CHECK (username ~* '^[a-z0-9_]{3,20}$'),
      ADD CONSTRAINT valid_age CHECK (birthdate <= CURRENT_DATE - INTERVAL '13 years'),
      ADD CONSTRAINT valid_country CHECK (country_code ~* '^[A-Z]{2}$');
    SQL
  end
end

This strategy provides maximum data integrity but requires careful error handling to translate constraint violations into user-friendly messages. Applications must map constraint names to meaningful error messages.

class User < ApplicationRecord
  # Minimal validations, constraints do the work
  validates :email, presence: true
  validates :username, presence: true
  validates :birthdate, presence: true
  validates :country_code, presence: true
  
  rescue_from ActiveRecord::StatementInvalid, with: :handle_constraint_violation
  
  private
  
  def handle_constraint_violation(exception)
    case exception.message
    when /valid_email/
      errors.add(:email, 'must be a valid email address')
    when /valid_username/
      errors.add(:username, 'must be 3-20 characters, alphanumeric and underscore only')
    when /valid_age/
      errors.add(:birthdate, 'user must be at least 13 years old')
    when /valid_country/
      errors.add(:country_code, 'must be a valid 2-letter country code')
    else
      raise exception
    end
  end
end

Application-Heavy Strategy

This approach minimizes database constraints, implementing validation logic in application code where testing and modification are easier. The database uses only essential constraints like primary keys and foreign keys.

class User < ApplicationRecord
  validates :email, 
    presence: true,
    uniqueness: true,
    format: { with: URI::MailTo::EMAIL_REGEXP }
  
  validates :username,
    presence: true,
    uniqueness: true,
    length: { in: 3..20 },
    format: { with: /\A[a-z0-9_]+\z/ }
  
  validates :birthdate,
    presence: true
  
  validate :minimum_age_requirement
  
  validates :country_code,
    presence: true,
    inclusion: { in: ISO3166::Country.codes }
  
  private
  
  def minimum_age_requirement
    return unless birthdate
    
    if birthdate > 13.years.ago.to_date
      errors.add(:birthdate, 'user must be at least 13 years old')
    end
  end
end

This strategy provides flexible validation with clear error messages but creates risk when data enters through non-application paths. Use this approach when the application is the sole data modification source and integrity violations represent programming errors rather than data quality issues.

Hybrid Strategy

The most common production approach combines both layers: application validations for user experience and database constraints for critical integrity rules.

Implement database constraints for:

• Primary keys and foreign keys (always)
• Unique constraints on business identifiers
• Not-null constraints on required fields
• Check constraints preventing logically impossible states
• Referential integrity requiring cascade actions

Implement application validations for:

• Format requirements with user-friendly messages
• Complex business logic involving multiple models
• Validations requiring external service calls
• Conditional validation based on state or context

# Database constraints for integrity
class CreateHybridSchema < ActiveRecord::Migration[7.0]
  def change
    create_table :orders do |t|
      t.references :user, null: false, foreign_key: { on_delete: :restrict }
      t.string :status, null: false, default: 'pending'
      t.decimal :total, precision: 10, scale: 2, null: false
      t.datetime :completed_at
      
      t.timestamps
    end
    
    execute <<-SQL
      ALTER TABLE orders
      ADD CONSTRAINT valid_status CHECK (
        status IN ('pending', 'processing', 'completed', 'cancelled')
      ),
      ADD CONSTRAINT non_negative_total CHECK (total >= 0),
      ADD CONSTRAINT completed_orders_have_timestamp CHECK (
        (status != 'completed') OR (completed_at IS NOT NULL)
      );
    SQL
  end
end

# Application validations for user experience
class Order < ApplicationRecord
  belongs_to :user
  
  validates :status, inclusion: {
    in: %w[pending processing completed cancelled],
    message: "%{value} is not a valid order status"
  }
  
  validates :total, numericality: {
    greater_than_or_equal_to: 0,
    message: "must be a positive amount"
  }
  
  validate :completed_orders_must_have_timestamp
  
  private
  
  def completed_orders_must_have_timestamp
    if status == 'completed' && completed_at.nil?
      errors.add(:completed_at, 'must be set when order is completed')
    end
  end
end

Trigger-Based Automation Strategy

Some systems rely heavily on triggers for automatic data management, reducing application code complexity by centralizing logic in the database.

Create triggers for:

• Audit logging
• Derived field calculation
• Cross-table synchronization
• Automatic timestamping
• Cascading soft deletes

This strategy works well for operations that must execute regardless of data modification source and do not require external service access. Triggers guarantee execution but complicate testing and debugging.

Common Pitfalls

Constraint Violation Error Handling

Applications frequently fail to handle constraint violations gracefully, exposing database error messages to users or crashing entirely. Constraint violations generate exceptions that differ by database adapter, requiring adapter-specific rescue blocks.

# Poor: allows database errors to propagate
def create
  @user = User.create!(user_params)
  redirect_to @user
end
# User sees: "PG::UniqueViolation: ERROR: duplicate key value violates unique constraint"

# Better: handles constraint violations
def create
  @user = User.new(user_params)
  
  if @user.save
    redirect_to @user, notice: 'User created successfully'
  else
    render :new
  end
rescue ActiveRecord::RecordNotUnique => e
  @user.errors.add(:email, 'has already been taken')
  render :new
rescue ActiveRecord::InvalidForeignKey => e
  @user.errors.add(:base, 'Referenced record does not exist')
  render :new
end

Map constraint names to fields using convention or metadata to generate appropriate error messages programmatically rather than hard-coding messages for each constraint.

Trigger Recursion Loops

Triggers that modify tables with their own triggers create recursion risk. A trigger on table A updates table B, whose trigger updates table A again, creating an infinite loop.

-- Dangerous: potential infinite recursion
CREATE TRIGGER sync_user_profile
AFTER UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_user_profile();

-- Function updates profiles table
CREATE FUNCTION update_user_profile() RETURNS TRIGGER AS $$
BEGIN
  UPDATE profiles SET name = NEW.name WHERE user_id = NEW.id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Profile trigger updates users table
CREATE TRIGGER sync_profile_user
AFTER UPDATE ON profiles
FOR EACH ROW
EXECUTE FUNCTION update_profile_user();

-- Function updates users table, triggering cycle
CREATE FUNCTION update_profile_user() RETURNS TRIGGER AS $$
BEGIN
  UPDATE users SET name = NEW.name WHERE id = NEW.user_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Prevent recursion by checking whether values actually changed before performing updates. Use session variables to detect recursive calls and exit early.

-- Safe: checks for actual changes
CREATE FUNCTION update_user_profile() RETURNS TRIGGER AS $$
BEGIN
  IF NEW.name IS DISTINCT FROM OLD.name THEN
    UPDATE profiles SET name = NEW.name WHERE user_id = NEW.id;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Foreign Key Cascade Misunderstandings

Developers often misunderstand cascade action behavior, leading to unexpected data deletion or incorrect referential integrity.

ON DELETE CASCADE removes dependent rows when the referenced row is deleted. This differs from ON DELETE SET NULL, which sets foreign key columns to NULL, and ON DELETE RESTRICT, which prevents deletion when dependent rows exist.

# Migration with cascade
add_foreign_key :posts, :users, on_delete: :cascade

# Deleting user deletes all posts
user = User.create!(name: 'John')
user.posts.create!(title: 'First Post')
user.posts.create!(title: 'Second Post')

user.destroy
# All posts automatically deleted by cascade constraint
Post.where(user: user).count # => 0

Cascading deletes execute at the database level, bypassing ActiveRecord callbacks. Models with dependent: :destroy behavior in associations will not execute their destroy callbacks when parent deletion happens through cascading constraints.

class Post < ApplicationRecord
  belongs_to :user
  has_many :comments, dependent: :destroy
  
  # This callback never fires when post deleted via cascade
  after_destroy :cleanup_associated_data
  
  def cleanup_associated_data
    # Never executes if parent user deleted with cascade
    associated_files.destroy_all
  end
end

Use dependent: :destroy in associations when callbacks must execute, or use statement-level triggers to perform cleanup operations when cascade constraints delete rows.

Check Constraint Performance Problems

Complex check constraints with subqueries or joins can severely degrade INSERT and UPDATE performance. Check constraints execute for every affected row, making expensive expressions multiply across bulk operations.

-- Problematic: subquery executes for every row
ALTER TABLE orders ADD CONSTRAINT valid_credit_limit CHECK (
  total <= (SELECT credit_limit FROM users WHERE id = user_id)
);

-- Insert batch of 1000 orders executes subquery 1000 times
-- Better: handle credit checking in application or trigger

Keep check constraints to simple expressions operating on the row's own columns. Use triggers or application code for validation requiring data from other tables.

Trigger Debugging Difficulties

When triggers modify data automatically, debugging unexpected values becomes challenging. Developers might not realize triggers are executing, leading to confusion about data state.

# Developer expects one update
order.update(status: 'completed')

# Trigger automatically sets completed_at
# Developer doesn't realize completed_at was set by trigger
order.reload.completed_at # => 2025-10-07 14:30:00

Log trigger executions or maintain trigger documentation accessible to all developers. Include comments in migration files explaining trigger purposes and side effects.

class AddCompletionTrigger < ActiveRecord::Migration[7.0]
  def up
    # TRIGGER SIDE EFFECT: Automatically sets completed_at timestamp
    # when order status changes to 'completed'
    execute <<-SQL
      CREATE TRIGGER set_completion_timestamp
      BEFORE UPDATE ON orders
      FOR EACH ROW
      WHEN (NEW.status = 'completed' AND OLD.status != 'completed')
      EXECUTE FUNCTION set_completed_at();
    SQL
  end
end

Migration Rollback Complications

Adding constraints to tables with existing data often fails when data violates the new constraint. Migrations must handle existing invalid data or validate data before adding constraints.

class AddEmailConstraint < ActiveRecord::Migration[7.0]
  def up
    # This fails if any existing emails are invalid
    execute <<-SQL
      ALTER TABLE users ADD CONSTRAINT valid_email 
        CHECK (email ~* '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}$');
    SQL
  end
  
  def down
    execute "ALTER TABLE users DROP CONSTRAINT valid_email"
  end
end

# Better: validate and fix data first
class AddEmailConstraint < ActiveRecord::Migration[7.0]
  def up
    # Fix invalid emails before adding constraint
    execute <<-SQL
      UPDATE users SET email = CONCAT('invalid_', id, '@placeholder.com')
      WHERE email IS NULL OR email !~* '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}$';
    SQL
    
    # Now constraint can be added safely
    execute <<-SQL
      ALTER TABLE users ADD CONSTRAINT valid_email 
        CHECK (email ~* '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}$');
    SQL
  end
  
  def down
    execute "ALTER TABLE users DROP CONSTRAINT valid_email"
  end
end

Constraint Name Collisions

PostgreSQL and other databases share constraint namespaces across the entire database. Migrations that create constraints with common names like valid_email might collide when applied to different tables.

# Both fail due to name collision
class AddUserEmailConstraint < ActiveRecord::Migration[7.0]
  def change
    execute "ALTER TABLE users ADD CONSTRAINT valid_email CHECK (...)"
  end
end

class AddCustomerEmailConstraint < ActiveRecord::Migration[7.0]
  def change
    execute "ALTER TABLE customers ADD CONSTRAINT valid_email CHECK (...)"
  end
end
# ERROR: constraint "valid_email" already exists

Use prefixed constraint names including the table name to avoid collisions.

execute "ALTER TABLE users ADD CONSTRAINT users_valid_email CHECK (...)"
execute "ALTER TABLE customers ADD CONSTRAINT customers_valid_email CHECK (...)"

Reference

Constraint Types

Foreign Key Actions

Trigger Timing and Events

Trigger Granularity

ActiveRecord Migration Methods

ActiveRecord Exception Types

Common PostgreSQL Check Constraint Patterns

Trigger Function Template (PostgreSQL)

CREATE OR REPLACE FUNCTION function_name()
RETURNS TRIGGER AS $$
BEGIN
  -- Access OLD for UPDATE/DELETE
  -- Access NEW for INSERT/UPDATE
  -- Modify NEW in BEFORE triggers
  
  IF TG_OP = 'INSERT' THEN
    -- Insert logic
    RETURN NEW;
  ELSIF TG_OP = 'UPDATE' THEN
    -- Update logic
    RETURN NEW;
  ELSIF TG_OP = 'DELETE' THEN
    -- Delete logic
    RETURN OLD;
  END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_name
BEFORE INSERT OR UPDATE OR DELETE ON table_name
FOR EACH ROW
EXECUTE FUNCTION function_name();

Migration Rollback Considerations