如何用PL/pgSQL触发器限制单次删除超过5条数据？

Hey there! Your goal to restrict deleting more than 5 records at once is totally achievable—let's fix your trigger implementation. The core issue with your current code is that you're using a row-level trigger (FOR EACH ROW), which fires once per deleted record, so you can't get the total count of rows being deleted in a single operation. Let's switch to a statement-level trigger and adjust the logic accordingly.

Key Fixes to Your Approach #

• Replace FOR EACH ROW with FOR EACH STATEMENT: This makes the trigger run once for the entire DELETE query, not per row.
• Use OLD TABLE (PostgreSQL 12+) to get all rows targeted by the DELETE: This lets you accurately count how many records are about to be deleted.
• Raise an EXCEPTION instead of a NOTICE: A notice won't stop the delete—an exception will abort the query entirely, which is what you want for enforcement.

Working Implementation (PostgreSQL 12+) #

First, create the trigger function:

CREATE OR REPLACE FUNCTION public.limit_delete_rows()
RETURNS TRIGGER AS $$
DECLARE
    delete_count INT;
BEGIN
    -- Count all rows that will be deleted by this statement
    SELECT COUNT(*) INTO delete_count FROM OLD TABLE;
    
    IF delete_count > 5 THEN
        RAISE EXCEPTION 'Cannot delete more than 5 records at once. You attempted to delete % records.', delete_count;
    END IF;
    
    RETURN NULL; -- Statement-level triggers only need to return NULL
END;
$$ LANGUAGE plpgsql;

Then create the statement-level trigger:

CREATE TRIGGER limit_delete_trigger
BEFORE DELETE ON schemaname.tablename
FOR EACH STATEMENT
EXECUTE PROCEDURE public.limit_delete_rows();

For PostgreSQL 11 or Older (No OLD TABLE Support) #

If you're on an older PostgreSQL version, you can parse the current DELETE query to count matching rows before execution. Note this has edge cases (like complex WHERE clauses), but works for most simple scenarios:

CREATE OR REPLACE FUNCTION public.limit_delete_rows_old()
RETURNS TRIGGER AS $$
DECLARE
    delete_count INT;
    query TEXT;
BEGIN
    -- Build a query to count rows targeted by the DELETE
    IF current_query() LIKE '%WHERE%' THEN
        query := 'SELECT COUNT(*) FROM ' || quote_ident(TG_TABLE_SCHEMA) || '.' || quote_ident(TG_RELNAME) || ' WHERE ' || substring(current_query() from 'WHERE (.*)');
    ELSE
        -- No WHERE clause means deleting the entire table
        query := 'SELECT COUNT(*) FROM ' || quote_ident(TG_TABLE_SCHEMA) || '.' || quote_ident(TG_RELNAME);
    END IF;
    
    EXECUTE query INTO delete_count;
    
    IF delete_count > 5 THEN
        RAISE EXCEPTION 'Cannot delete more than 5 records at once. You attempted to delete % records.', delete_count;
    END IF;
    
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Create the same statement-level trigger for this function:

CREATE TRIGGER limit_delete_trigger
BEFORE DELETE ON schemaname.tablename
FOR EACH STATEMENT
EXECUTE PROCEDURE public.limit_delete_rows_old();

How It Works #

• When a DELETE query runs, the trigger fires once before any rows are deleted.
• It counts how many rows would be deleted by the query.
• If the count exceeds 5, it throws an error and cancels the delete operation.

内容的提问来源于stack exchange，提问作者Deepan Kaviarasu