Database Triggers

Scenarios

After data is written to a database, several follow-up actions may be required: automatically refreshing update timestamps, recording price changes for auditing, recalculating inventory and summary data, and synchronizing derived fields. If the application manually triggers these actions after every write, they can easily be overlooked, and the same logic may become scattered across multiple places, making changes difficult to maintain. A trigger is a built-in, event-driven mechanism in PostgreSQL. Attached to a table, it automatically executes predefined SQL logic when the table is subject to an INSERT, UPDATE, or DELETE operation. The entire process takes place within the database, without application involvement. Triggers complement the Data API and RPC: the latter two handle calls initiated by applications, whereas triggers respond automatically to data changes.

Triggers execute within the database and can read from and write to the table on which they are defined and other tables, as well as call other functions. They are well-suited for handling data-dependent logic directly in the database. Because the logic and data reside in the same database, transactions can help maintain consistency across multiple tables without transferring intermediate results between the application and the database. Typical use cases include:

• Automatic field maintenance: Refreshing updated_at whenever a row is updated
• Change auditing: Recording the old and new values of fields such as prices
• Refreshing derived and redundant fields: Recalculating a product's minimum price when its pricing changes
• Pre-write validation and normalization: Rejecting invalid data before it is stored in the database

Triggers place change-handling logic in the database rather than the application because data changes are the source of these actions. Responding at the source makes the process more immediate and less prone to omissions. To deliver changes to external systems, use Database Webhooks. To update the frontend UI in real time as data changes, use Real-Time Subscriptions (Realtime), which push changes to connected clients over WebSocket connections.

This section explains the purpose of trigger functions, their basic syntax, and how to define them. The examples use the products and product_pricings tables.

Related Concepts

• Overview of Triggers

  As described above, a trigger is bound to a table and automatically executes a pre-bound function (called a trigger function) when a specified event occurs on that table. The application does not need to invoke the function directly. Triggers complement the Data API and RPC: the latter two handle calls initiated by applications, whereas triggers respond automatically to data changes. Because triggers execute entirely within the database, they can handle operations that must occur automatically when data changes, such as maintaining fields, writing audit logs, performing cascading validation, and calculating derived columns.

• Trigger Components

  Creating a trigger involves two separate database objects, as described in Table 1.

  Table 1 Trigger components

  Object	Function	Definition
  Trigger function	Contains the logic to be executed and returns the trigger type.	CREATE FUNCTION ... RETURNS trigger
  Trigger object	Specifies when and where the function is invoked, which table it is associated with, which events activate it, and how frequently it runs.	CREATE TRIGGER ...

  A trigger function can be reused by multiple triggers. For example, set_updated_at() can be bound to both the products and product_pricings tables.

• Trigger Timing: BEFORE and AFTER

  Triggers are classified by whether they execute before or after a change is written. This determines whether they can modify or cancel the change.

  Table 2 Trigger timing

  Timing	Execution Point	Can the Change Be Modified or Canceled?	Typical Use Cases
  BEFORE	Before the change is written	Yes. It can modify NEW field values or return NULL to cancel the operation for that row.	Automatically populating or modifying fields, validating data before writing, and normalizing data
  AFTER	After the change is written	No. (Data has been written to the database.)	Audit logging, derived calculations, and notifications

  A BEFORE trigger allows an operation to proceed by returning NEW (continuing the write with modified values). Returning NULL cancels the operation for that row. The return value of an AFTER trigger has no effect. Either NULL or NEW can typically be returned.

• Trigger Frequency: Row-Level and Statement-Level

  Triggers can execute once for each affected row or once for the entire SQL statement.

  Table 3 Trigger frequency

  Frequency	Meaning	Use Cases
  FOR EACH ROW	Executes once for each affected row.	Operations that need to read or write NEW or OLD for individual rows, as in most business triggers
  FOR EACH STATEMENT	Executes once for the entire SQL statement, regardless of how many rows it affects.	Statistics and aggregate calculations for batch operations.

  Business triggers commonly use FOR EACH ROW because auditing and automatic field maintenance require access to the specific row that changed. FOR EACH STATEMENT is suitable for statement-level tasks, such as tracking the total number of rows affected by an operation, and can be more efficient for batch operations.

• Trigger Events and Column-Specific Triggers

  A trigger event specifies which operation activates the trigger: INSERT, UPDATE, DELETE, or TRUNCATE. Multiple events can be specified, such as INSERT OR UPDATE. TRUNCATE is a special case. It can be used only with statement-level triggers and cannot be combined with INSERT, UPDATE, or DELETE in the same trigger. For UPDATE, you can also specify that the trigger fires only when particular columns are targeted by the update.

  -- Fire only when the price column is targeted by an UPDATE (modifying other columns does not trigger).
  CREATE TRIGGER trg_price_audit
    AFTER UPDATE OF price ON product_pricings
    FOR EACH ROW
    EXECUTE FUNCTION log_price_change();

  Using UPDATE OF price narrows the trigger's activation conditions compared with an unrestricted UPDATE trigger. Updates that target unrelated columns, such as tier or unit, will not activate it, avoiding unnecessary execution.

• Special Variables in Trigger Functions

  Trigger functions can access a set of special variables that describe the context and data involved in the current trigger invocation.

  Table 4 Special variables in trigger functions

  Variable	Description
  NEW	New row data. It is available for INSERT and UPDATE, and can be modified in a BEFORE trigger.
  OLD	Old row data. It is available for UPDATE and DELETE.
  TG_OP	Triggering operation (INSERT, UPDATE, DELETE, or TRUNCATE).
  TG_WHEN	Trigger timing (BEFORE or AFTER).
  TG_LEVEL	Frequency (ROW or STATEMENT).
  TG_TABLE_NAME	Name of the table on which the trigger is defined.
  TG_TABLE_SCHEMA	Schema containing the table on which the trigger is defined.
  TG_NAME	Name of the trigger.
  TG_ARGV	Array of string arguments passed when the trigger is created.
  TG_NARGS	Number of arguments in TG_ARGV.

  For triggers that handle multiple events, such as INSERT OR UPDATE OR DELETE, the function can use TG_OP to branch into different logic for each operation, allowing the same function to be reused.

Use Cases

Triggers respond to data changes and execute follow-up logic within the database. Like Database Webhooks and Realtime in Real-Time Subscriptions (Realtime), triggers are driven by data changes. The difference lies in who receives the event and how it is handled.

Use triggers for operations within the database, Webhooks for sending outbound requests to external systems, and Realtime for pushing updates to frontends. Based on Table 6, determine when to use triggers and when to leave the logic to the application.

Key benefits: Triggers automate operations within the database in response to data changes, handle follow-up actions without application intervention, and maintain transactional consistency by executing logic close to the data.

Defining and Using Triggers

Triggers are defined using SQL and created by executing SQL statements in the SQL Editor of the console. The examples in this section use the products and product_pricings tables created in Database Data APIs.

• Basic Syntax

  Defining a trigger involves two steps: First, create a function that returns trigger; then, create a trigger object and bind it to a table.

  -- Step 1: Define a trigger function.
  CREATE OR REPLACE FUNCTION function_name()
  RETURNS trigger
  LANGUAGE plpgsql
  AS $$
  BEGIN
    -- Function body: Read or write NEW/OLD, and call other SQL statements.
    RETURN NEW;   -- BEFORE triggers return NEW to proceed or NULL to cancel the operation on the row.
  END;
  $$;
  
  -- Step 2: Create a trigger.
  CREATE TRIGGER trigger_name
    {BEFORE | AFTER} {INSERT | UPDATE | DELETE [OR ...]}
    ON table_name
    [FOR EACH ROW | FOR EACH STATEMENT]
    [WHEN (condition)]
    EXECUTE FUNCTION function_name(optional_arguments...);

  Consider the common task of automatically maintaining updated_at. This field is typically assigned a default value only when a row is inserted and is not automatically refreshed when the row is updated. A trigger can set updated_at to the current time before each UPDATE.

  -- Trigger function: Set the new row's updated_at to the current time before an UPDATE operation.
  CREATE OR REPLACE FUNCTION set_updated_at()
  RETURNS trigger
  LANGUAGE plpgsql
  AS $$
  BEGIN
    NEW.updated_at = now();   -- Update updated_at in the new row.
    RETURN NEW;               -- Return the new row so the operation can proceed.
  END;
  $$;
  
  -- Bind the trigger to UPDATE operations on products to automatically refresh updated_at before each update.
  CREATE TRIGGER trg_products_updated_at
    BEFORE UPDATE ON products
    FOR EACH ROW
    EXECUTE FUNCTION set_updated_at();

  Then, updated_at is automatically refreshed whenever an UPDATE operation is performed on the products table, without requiring the application to supply a value. The same function can be reused for other tables, such as product_pricings, by creating a separate trigger for each table.

  • BEFORE and AFTER: For details, see BEFORE Triggers: Validating and Modifying Data Before Writes and AFTER Triggers: Maintaining Redundant Computed Columns.
  • Multiple events: A trigger can handle multiple events, such as INSERT OR UPDATE OR DELETE.
  • WHEN: A trigger is activated only when the specified condition is met. For details, see Triggers with a WHEN Condition.
• BEFORE Triggers: Validating and Modifying Data Before Writes

  Example scenario: Prices must not be negative when a product is listed or repriced. Use a BEFORE INSERT OR UPDATE trigger to validate prices before writing. If a price is invalid, abort the operation to prevent invalid data from being committed to the database and then rolled back.

  CREATE OR REPLACE FUNCTION check_price_positive()
  RETURNS trigger
  LANGUAGE plpgsql
  AS $$
  BEGIN
    IF NEW.price < 0 THEN
      RAISE EXCEPTION 'Price cannot be negative: %', NEW.price;  -- Abort the operation with an error.
    END IF;
    RETURN NEW;   -- Allow the operation to proceed if validation succeeds.
  END;
  $$;
  
  CREATE TRIGGER trg_check_price
    BEFORE INSERT OR UPDATE ON product_pricings
    FOR EACH ROW
    EXECUTE FUNCTION check_price_positive();

  Note: In a BEFORE trigger, RAISE EXCEPTION aborts the current SQL statement and leaves the transaction in an aborted state, requiring a ROLLBACK before follow-up operations can proceed. To skip only the affected row without aborting the entire statement, return NULL instead. The row operation is canceled, while other rows can proceed. NEW can be read and modified in this trigger, and returning NEW allows the operation to proceed after validation succeeds.

• AFTER Triggers: Maintaining Redundant Computed Columns

  Example scenario: Whenever a price tier in the product_pricings table changes, make the current minimum price for that product directly available in the products table, avoiding repeated cross-table aggregation queries. Use an AFTER INSERT OR UPDATE OR DELETE trigger to maintain a redundant column. Unlike modifying the current row in a BEFORE trigger, this approach also allows updates to a parent table.

  First, add a column to store the minimum price in products:

  ALTER TABLE products ADD COLUMN min_price NUMERIC(10,2); 

  Then, define a trigger to recalculate the minimum price for the associated product after a pricing change:

  CREATE OR REPLACE FUNCTION refresh_product_min_price()
  RETURNS trigger
  LANGUAGE plpgsql
  AS $$
  DECLARE
    pid bigint;
  BEGIN
    -- NEW is unavailable for DELETE. Retrieve product_id from the appropriate row based on the operation type.
    IF TG_OP = 'DELETE' THEN
      pid := OLD.product_id;
    ELSE
      pid := NEW.product_id;
    END IF;
    UPDATE products p
      SET min_price = COALESCE((SELECT min(price) FROM product_pricings WHERE product_id = pid), 0)
      WHERE p.id = pid;
    RETURN NULL;   -- The return value is ignored in AFTER triggers.
  END;
  $$;
  
  CREATE TRIGGER trg_refresh_min_price
    AFTER INSERT OR UPDATE OR DELETE ON product_pricings
    FOR EACH ROW
    EXECUTE FUNCTION refresh_product_min_price();

  Note: An AFTER trigger ensures that the new row values have been applied before recalculating the minimum price. In a trigger that handles multiple events, NEW is unavailable for DELETE, and OLD is unavailable for INSERT. Accessing either when it is unavailable causes an error, so use TG_OP to determine which row to access. Redundant columns are suitable for aggregate values in read-heavy, write-light workloads, trading space for query performance.

• Column-Specific Triggers: UPDATE OF

  By default, an UPDATE trigger fires when any column is updated. Use UPDATE OF to specify the columns that trigger it, avoiding unnecessary execution when unrelated columns change. UPDATE OF price was demonstrated in Trigger Events and Column-Specific Triggers. This example shows how to recalculate the minimum price only when the price or effective date changes.

  CREATE TRIGGER trg_refresh_min_price_on_price
    AFTER UPDATE OF price, effective_from ON product_pricings
    FOR EACH ROW
    EXECUTE FUNCTION refresh_product_min_price();

  The trigger fires only when price or effective_from is specified in an UPDATE statement. Updates to tier or unit do not trigger it.

• Triggers with a WHEN Condition

  Use WHEN to add a condition at the trigger level. If the condition is not satisfied, the trigger is skipped entirely and its function is not called. For example, to audit price changes, first create an audit table and an audit function (that uses IF to ensure that only price changes are recorded).

  -- Audit table: Record the old and new prices.
  CREATE TABLE product_price_audit (
    id          BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    product_id  BIGINT NOT NULL,
    old_price   NUMERIC(10,2),
    new_price   NUMERIC(10,2),
    changed_at  TIMESTAMPTZ DEFAULT now()
  );
  
  CREATE OR REPLACE FUNCTION log_price_change()
  RETURNS trigger
  LANGUAGE plpgsql
  AS $$
  BEGIN
    IF NEW.price IS DISTINCT FROM OLD.price THEN
      INSERT INTO product_price_audit (product_id, old_price, new_price)
      VALUES (NEW.product_id, OLD.price, NEW.price);
    END IF;
    RETURN NEW;
  END;
  $$;

  Next, use WHEN to apply an additional filter at the trigger level. If the price has not changed, log_price_change is not called, avoiding the overhead of executing the function.

  CREATE TRIGGER trg_price_audit_when
    AFTER UPDATE ON product_pricings
    FOR EACH ROW
    WHEN (NEW.price IS DISTINCT FROM OLD.price)
    EXECUTE FUNCTION log_price_change();

  IS DISTINCT FROM safely compares values that may contain NULL. By contrast, comparisons using = return NULL (treated as false) when NULL values are compared, which can cause changes to be missed.

• Statement-level Triggers

  A FOR EACH STATEMENT trigger fires once per SQL statement, not once per row, making it suitable for statement-level processing, such as logging the type of each operation.

  CREATE OR REPLACE FUNCTION log_pricing_stmt()
  RETURNS trigger
  LANGUAGE plpgsql
  AS $$
  BEGIN
    RAISE LOG 'An % operation occurred on product_pricings', TG_OP;
    RETURN NULL;
  END;
  $$;
  
  CREATE TRIGGER trg_pricing_stmt
    AFTER INSERT OR UPDATE OR DELETE ON product_pricings
    FOR EACH STATEMENT -- Fires once per statement, regardless of the number of rows affected.
    EXECUTE FUNCTION log_pricing_stmt();

  NEW and OLD are unavailable in statement-level triggers because they do not operate on individual rows. Use context variables such as TG_OP instead.

• Dropping Triggers

  DROP TRIGGER trg_check_price ON product_pricings; 

  This removes the trigger object but leaves its associated function, allowing the function to be reused by other triggers. If the trigger cannot be dropped directly because its function resides in a restricted schema, you can drop the function along with its dependent objects.

  DROP FUNCTION IF EXISTS check_price_positive() CASCADE;  -- CASCADE also removes triggers that depend on this function.

  CASCADE removes all triggers that depend on the function. Before deleting it, verify that no other triggers depend on the function.

• Triggers, Permissions, and Security

  Pay particular attention to the execution privileges of trigger functions:

  • Trigger functions execute with the privileges of the function owner (PostgreSQL trigger functions also run with the owner's privileges even when not marked SECURITY DEFINER), allowing them to write to parent tables across tables and bypass the calling user's RLS policies. This is a prerequisite for trigger-based interactions and also means that trigger functions inherently behave like SECURITY DEFINER functions: Their privileges depend on the function owner rather than the role executing the SQL statement.
  • Exercise caution with write operations inside trigger function bodies to prevent them from being exploited indirectly for unauthorized writes. A trigger function defined in the public schema and owned by a highly privileged role, such as postgres with the BYPASSRLS attribute, effectively serves as a high-privilege entry point.
  • You are advised to keep trigger functions in a controlled schema, assign ownership to an operations role, and implement necessary validation in the function body. After making changes, scan for potential issues using Supabase Database Advisors or the security checks of the console.

  Trigger functions cannot be invoked through RPC because functions returning the trigger type are intended exclusively for use by triggers. Regular functions cannot be bound directly as triggers (trigger functions must return the trigger type).

Creating and Managing Triggers

There are two ways to create triggers, and both produce equivalent trigger objects: Execute SQL statements in the SQL Editor or configure triggers through the visual interface under Dashboard > Triggers. The SQL Editor is suitable for triggers with complex logic or those that need to be included in migration scripts, whereas the visual interface is convenient for quickly creating common triggers using existing functions and managing them in daily operations.

• Method 1: Create Triggers in the SQL Editor

  Execute the CREATE FUNCTION and CREATE TRIGGER statements described in Defining and Using Triggers in the console's SQL Editor to create a trigger. To view existing triggers, query the system view:

  SELECT trigger_name AS trigger_name,
         event_object_table AS table_name,
         event_manipulation AS event,
         action_timing AS timing,
         action_orientation AS frequency
  FROM information_schema.triggers
  WHERE trigger_schema = 'public'
  ORDER BY event_object_table, trigger_name;

  To delete a trigger, use DROP TRIGGER. For details, see Dropping Triggers.

• Method 2: Create Triggers Through Dashboard > Triggers

  On the Supabase Dashboard, navigate to Database > Triggers (or the Triggers tab on a table's details page) to create and manage triggers through the visual interface.

  Creation steps:

  1. Log in to the RDS Supabase Dashboard.
  2. Choose Database > Triggers to open the trigger management page.
     Figure 1 Supabase Dashboard trigger management page
     
  3. Click New trigger.
     Figure 2 Supabase Dashboard trigger creation page
     
     Configure the parameters according to Table 7.

     Table 7 Trigger creation parameters

     Parameter	Description	Example Value
     Name of trigger	Name of the trigger.	trg_check_price
     Table	Table to be listened	product_pricings
     Events	One or more events: Insert, Update, or Delete.	Insert, Update
     Trigger type	Before the event or After the event.	Before the event
     Orientation	Row-level or statement-level.	FOR EACH ROW
     Function to trigger.	Select a previously defined function that returns trigger.	check_price_positive

  4. Review the settings and save them. The platform generates and executes the corresponding CREATE TRIGGER statement.

  The visual interface exposes commonly used settings that correspond to the parameters of CREATE TRIGGER described in Defining and Using Triggers. For more complex definitions, such as UPDATE OF with multiple columns or a WHEN clause containing a subquery, use Method 1: Create Triggers in the SQL Editor directly to write SQL statements if the visual interface cannot express the required configuration.

  Notice: The trigger function must be defined in advance, such as set_updated_at in Basic Syntax or check_price_positive in BEFORE Triggers: Validating and Modifying Data Before Writes. When creating a trigger through the visual interface, select the existing function from the drop-down list.

• Management and Debugging

  Regardless of the creation method, triggers can be managed through Database > Triggers or the table details page. Common operations include viewing triggers for each table, enabling or disabling individual triggers (often used during debugging), dropping triggers, and navigating to trigger functions.

  Temporarily disable the trigger during debugging. Then, re-enable it with ENABLE TRIGGER when debugging is complete. Use this with caution in production environments, as validation and related operations will not run while the trigger is disabled.

  ALTER TABLE product_pricings DISABLE TRIGGER trg_check_price;

Case: Automatic In-Database Updates with Product Price Changes

Example scenario: After an operations team member updates a price in product_pricings, the database must automatically perform three tasks: Validate that the price is non-negative before the update, refresh the products.min_price cache after the update, and write the old and new prices to an audit table. This combines one BEFORE trigger and two AFTER triggers on the same table, enabling automatic database-side operations without requiring changes to the application.

1. BEFORE trigger: Validate that the price is non-negative.

   Reuse the check_price_positive function in BEFORE Triggers: Validating and Modifying Data Before Writes to prevent negative prices from being stored in the database.

2. AFTER trigger: Refresh the minimum-price cache.

   Reuse refresh_product_min_price in AFTER Triggers: Maintaining Redundant Computed Columns to recalculate products.min_price after the price change.

3. Trigger with a WHEN condition: Record price changes in the audit log.

   Reuse trg_price_audit_when, created in Triggers with a WHEN Condition, along with the product_price_audit table and log_price_change function. When the price changes, the trigger records product_id, the old price, and the new price in the audit table for future reference. The trigger already includes a WHEN condition that ensures it fires only when the price actually changes, so there is no need to redefine it.

4. Trigger-based automation through the SDK

   All three operations are configured on the database side, so no application-side changes are required. The operations specialist can update prices normally through the SDK, whereas the triggers handle the associated operations automatically in the background.

   // The operations team member updates a price from the frontend using a standard SDK update.
   const { error } = await supabase
     .from('product_pricings')
     .update({ price: 128.00 })
     .eq('id', 1) // Pricing tier id = 1
   if (error) { console.error(error); return }
   
   // Behind the update, the following operations occur:
   // (1) The BEFORE trigger check_price_positive validates that 128 > 0 and allows the update.
   // (2) The data is saved and the price changes from its old value to 128.00.
   // (3) The two AFTER triggers fire in alphabetical order by trigger name. They are independent of each other and do not affect the result:
   //    - trg_price_audit_when records the old and new prices in product_price_audit.
   //    - trg_refresh_min_price recalculates min_price for the associated product.

   From the application's perspective, this is just a standard .update() call. Validation, cache refreshes, and auditing are handled automatically. This is the value of triggers: They move post-update operations into the database, so application code does not need to be aware of these operations or repeatedly orchestrate the same side effects at every call site.

   After updating a price, query the following two tables to verify that the triggers have worked as expected:

   • Check that the minimum price has been refreshed: SELECT id, min_price FROM products WHERE id = <product_id>
   • Check that a new price change has been recorded: SELECT product_id, old_price, new_price FROM product_price_audit ORDER BY changed_at DESC LIMIT 5

   Multiple triggers defined on the same table execute in alphabetical order by trigger name. BEFORE triggers execute prior to AFTER triggers. If the order of operations matters, reflect the desired sequence in the trigger names or consolidate the operations into a single trigger function and execute them in the required order.

Reference

The following open-source Supabase documents are provided for reference only.

• Supabase Postgres Triggers
• Supabase Database Functions
• PostgreSQL — CREATE TRIGGER
• PostgreSQL — Triggers