$ cat /posts/supabase-triggers-and-functions-automate-database-logic.md

Supabase Triggers and Functions: Automate Database Logic

drwxr-xr-x2026-01-255 min0 views

PostgreSQL triggers and functions enable database-level automation executing custom logic automatically when data changes, eliminating application-side code for common operations like timestamps, data validation, audit logging, cascade operations, and business rules enforcement. Unlike application logic that can be bypassed with direct database access or API manipulation, database triggers guarantee execution for all data modifications maintaining consistency and integrity. This comprehensive guide covers understanding triggers and functions concepts, writing PL/pgSQL functions for custom logic, creating triggers for INSERT, UPDATE, DELETE events, implementing automatic timestamps and slug generation, building audit trails tracking changes, enforcing complex validation rules, cascading operations across related tables, troubleshooting trigger issues, and performance considerations. Database automation becomes valuable when operations must execute regardless of access method—whether through application code, admin panel, SQL editor, or external integrations—ensuring data integrity and business rules are always enforced. Before proceeding, understand database basics and migrations.

Understanding Triggers and Functions

Concept	Description	Use Case
Function	Reusable SQL/PL/pgSQL code block	Custom calculations, validation
Trigger	Automatic function execution on events	Auto-timestamps, audit logs
BEFORE Trigger	Executes before data modification	Validation, data transformation
AFTER Trigger	Executes after data modification	Audit logging, notifications
FOR EACH ROW	Trigger fires for each affected row	Row-level operations
FOR EACH STATEMENT	Trigger fires once per SQL statement	Bulk operations

Creating Your First Function

sqlbasic_functions.sql

-- Simple function: Calculate post read time
create or replace function calculate_read_time(content text)
returns integer
language plpgsql
as $$
declare
  word_count integer;
  words_per_minute integer := 200;
begin
  -- Count words (approximate)
  word_count := array_length(regexp_split_to_array(content, '\s+'), 1);
  
  -- Calculate reading time in minutes
  return ceil(word_count::float / words_per_minute);
end;
$$;

-- Use the function
select calculate_read_time('This is sample content for a blog post...');
-- Returns: 1 (minute)

-- Function with multiple parameters
create or replace function generate_slug(title text)
returns text
language plpgsql
as $$
begin
  return lower(
    regexp_replace(
      regexp_replace(trim(title), '[^a-zA-Z0-9\s]', '', 'g'),
      '\s+', '-', 'g'
    )
  );
end;
$$;

select generate_slug('Hello World: A Guide!');
-- Returns: 'hello-world-a-guide'

Automatic Timestamps Trigger

sqlauto_timestamps.sql

-- Function to update updated_at timestamp
create or replace function update_updated_at_column()
returns trigger
language plpgsql
as $$
begin
  new.updated_at = now();
  return new;
end;
$$;

-- Create trigger for posts table
create trigger update_posts_updated_at
  before update on posts
  for each row
  execute function update_updated_at_column();

-- Now whenever a post is updated:
update posts set title = 'New Title' where id = '123';
-- updated_at automatically set to current timestamp

-- Apply to multiple tables
create trigger update_comments_updated_at
  before update on comments
  for each row
  execute function update_updated_at_column();

create trigger update_profiles_updated_at
  before update on profiles
  for each row
  execute function update_updated_at_column();

-- Reusable function works for any table with updated_at column

Automatic Slug Generation

sqlauto_slug.sql

-- Function to generate slug from title
create or replace function generate_slug_from_title()
returns trigger
language plpgsql
as $$
begin
  -- Only generate slug if not provided
  if new.slug is null or new.slug = '' then
    new.slug := lower(
      regexp_replace(
        regexp_replace(trim(new.title), '[^a-zA-Z0-9\s]', '', 'g'),
        '\s+', '-', 'g'
      )
    );
  end if;
  
  return new;
end;
$$;

-- Create trigger
create trigger posts_generate_slug
  before insert or update on posts
  for each row
  execute function generate_slug_from_title();

-- Usage:
insert into posts (title, content, user_id)
values ('My First Blog Post!', 'Content here...', 'user-123');
-- slug automatically set to 'my-first-blog-post'

-- Handle duplicate slugs
create or replace function generate_unique_slug()
returns trigger
language plpgsql
as $$
declare
  base_slug text;
  counter integer := 1;
  final_slug text;
begin
  -- Generate base slug
  base_slug := lower(
    regexp_replace(
      regexp_replace(trim(new.title), '[^a-zA-Z0-9\s]', '', 'g'),
      '\s+', '-', 'g'
    )
  );
  
  final_slug := base_slug;
  
  -- Check for duplicates and append counter
  while exists (select 1 from posts where slug = final_slug and id != coalesce(new.id, '00000000-0000-0000-0000-000000000000')) loop
    final_slug := base_slug || '-' || counter;
    counter := counter + 1;
  end loop;
  
  new.slug := final_slug;
  return new;
end;
$$;

-- Replace previous trigger
drop trigger if exists posts_generate_slug on posts;
create trigger posts_generate_unique_slug
  before insert or update on posts
  for each row
  execute function generate_unique_slug();

Audit Trail Implementation

sqlaudit_trail.sql

-- Create audit log table
create table audit_logs (
  id uuid default gen_random_uuid() primary key,
  table_name text not null,
  record_id uuid not null,
  action text not null, -- 'INSERT', 'UPDATE', 'DELETE'
  old_data jsonb,
  new_data jsonb,
  user_id uuid references auth.users(id),
  created_at timestamp with time zone default now()
);

create index audit_logs_table_name_idx on audit_logs(table_name);
create index audit_logs_record_id_idx on audit_logs(record_id);
create index audit_logs_user_id_idx on audit_logs(user_id);

-- Audit function
create or replace function audit_trigger_func()
returns trigger
language plpgsql
security definer
as $$
declare
  audit_row audit_logs%rowtype;
begin
  audit_row.table_name := TG_TABLE_NAME;
  audit_row.action := TG_OP;
  audit_row.user_id := auth.uid();
  
  if (TG_OP = 'INSERT') then
    audit_row.record_id := new.id;
    audit_row.new_data := to_jsonb(new);
  elsif (TG_OP = 'UPDATE') then
    audit_row.record_id := new.id;
    audit_row.old_data := to_jsonb(old);
    audit_row.new_data := to_jsonb(new);
  elsif (TG_OP = 'DELETE') then
    audit_row.record_id := old.id;
    audit_row.old_data := to_jsonb(old);
  end if;
  
  insert into audit_logs values (audit_row.*);
  
  if (TG_OP = 'DELETE') then
    return old;
  else
    return new;
  end if;
end;
$$;

-- Apply audit trigger to posts
create trigger posts_audit_trigger
  after insert or update or delete on posts
  for each row
  execute function audit_trigger_func();

-- Query audit history
select
  action,
  old_data->>'title' as old_title,
  new_data->>'title' as new_title,
  created_at,
  (select email from auth.users where id = audit_logs.user_id) as changed_by
from audit_logs
where table_name = 'posts' and record_id = 'some-post-id'
order by created_at desc;

Data Validation Triggers

sqlvalidation_triggers.sql

-- Validate post content before insert/update
create or replace function validate_post()
returns trigger
language plpgsql
as $$
begin
  -- Title must be at least 5 characters
  if length(trim(new.title)) < 5 then
    raise exception 'Title must be at least 5 characters';
  end if;
  
  -- Content must be at least 50 characters
  if length(trim(new.content)) < 50 then
    raise exception 'Content must be at least 50 characters';
  end if;
  
  -- Title cannot exceed 200 characters
  if length(new.title) > 200 then
    raise exception 'Title cannot exceed 200 characters';
  end if;
  
  -- Trim whitespace
  new.title := trim(new.title);
  new.content := trim(new.content);
  
  return new;
end;
$$;

create trigger validate_post_before_save
  before insert or update on posts
  for each row
  execute function validate_post();

-- Email validation example
create or replace function validate_profile()
returns trigger
language plpgsql
as $$
begin
  -- Validate email format
  if new.email !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' then
    raise exception 'Invalid email format';
  end if;
  
  -- Validate phone number (if provided)
  if new.phone is not null and new.phone !~ '^[0-9+\-\s()]+$' then
    raise exception 'Invalid phone number format';
  end if;
  
  return new;
end;
$$;

create trigger validate_profile_before_save
  before insert or update on profiles
  for each row
  execute function validate_profile();

Cascading Operations

sqlcascade_operations.sql

-- Update post count when posts are added/removed
create or replace function update_user_post_count()
returns trigger
language plpgsql
as $$
begin
  if (TG_OP = 'INSERT') then
    update profiles
    set post_count = post_count + 1
    where user_id = new.user_id;
  elsif (TG_OP = 'DELETE') then
    update profiles
    set post_count = post_count - 1
    where user_id = old.user_id;
  end if;
  
  if (TG_OP = 'DELETE') then
    return old;
  else
    return new;
  end if;
end;
$$;

create trigger update_user_post_count_trigger
  after insert or delete on posts
  for each row
  execute function update_user_post_count();

-- Update comment count on posts
create or replace function update_post_comment_count()
returns trigger
language plpgsql
as $$
begin
  if (TG_OP = 'INSERT') then
    update posts
    set comment_count = comment_count + 1
    where id = new.post_id;
  elsif (TG_OP = 'DELETE') then
    update posts
    set comment_count = comment_count - 1
    where id = old.post_id;
  end if;
  
  if (TG_OP = 'DELETE') then
    return old;
  else
    return new;
  end if;
end;
$$;

create trigger update_post_comment_count_trigger
  after insert or delete on comments
  for each row
  execute function update_post_comment_count();

Managing Triggers

sqlmanage_triggers.sql

-- List all triggers
select
  trigger_name,
  event_manipulation,
  event_object_table,
  action_timing
from information_schema.triggers
where trigger_schema = 'public'
order by event_object_table, trigger_name;

-- Disable trigger temporarily
alter table posts disable trigger posts_audit_trigger;

-- Enable trigger
alter table posts enable trigger posts_audit_trigger;

-- Drop trigger
drop trigger if exists posts_audit_trigger on posts;

-- Drop function (removes all associated triggers)
drop function if exists audit_trigger_func() cascade;

-- View trigger definition
select
  pg_get_triggerdef(oid) as trigger_definition
from pg_trigger
where tgname = 'posts_audit_trigger';

Triggers Best Practices

Keep Triggers Simple: Complex logic slows down all operations; move heavy processing to background jobs
Use BEFORE for Validation: Validate and transform data in BEFORE triggers to prevent invalid inserts
Use AFTER for Logging: Audit and logging operations work best in AFTER triggers when data is committed
Avoid Trigger Chains: Triggers calling triggers create complex dependencies and performance issues
Handle NULL Values: Always check for NULL in trigger logic to prevent unexpected behavior
Test Thoroughly: Test triggers with various data scenarios including edge cases
Document Trigger Logic: Add comments explaining why triggers exist and how they work

Performance Warning: Triggers execute for every affected row and can significantly slow down bulk operations. For heavy processing, consider using background jobs or queues instead. Combine triggers with Row Level Security carefully to avoid performance issues.

Common Issues

Trigger Not Firing: Check if trigger is enabled with \d+ table_name in psql or information_schema.triggers
Infinite Loop: Ensure triggers don't recursively modify the same table causing infinite loops
Permission Errors: Use SECURITY DEFINER carefully when triggers need elevated permissions
Performance Degradation: Profile slow queries and consider disabling triggers for bulk imports

Next Steps

Add Search Features: Implement full-text search with trigger-based indexing
Use in Projects: Apply triggers in React applications or Next.js projects
Version Control: Add triggers to database migrations for team collaboration
Enable Real-time: Combine with real-time subscriptions for live updates

Conclusion

PostgreSQL triggers and functions provide database-level automation executing custom logic automatically when data changes, guaranteeing execution regardless of access method and maintaining data integrity throughout application lifecycle. By writing PL/pgSQL functions for reusable logic, creating BEFORE triggers for validation and transformation, implementing AFTER triggers for audit logging and cascading operations, and following performance best practices, you build robust applications with business rules enforced at the database layer. Common automation patterns include automatic timestamps eliminating manual updated_at management, slug generation creating URL-friendly identifiers, audit trails tracking all modifications with complete history, data validation preventing invalid data entry, and cascading operations maintaining derived counts and statistics. Always keep triggers simple to avoid performance issues, use BEFORE triggers for validation, implement AFTER triggers for logging, test thoroughly with edge cases, and document trigger logic for team maintenance. With database automation mastered, your applications maintain data consistency, enforce business rules automatically, and reduce application-side code complexity. Continue building production features with search functionality, migration workflows, and framework integrations.