如何优化PostgreSQL中维护活跃产品计数的触发器方案？

Hey there! Your current trigger only handles incrementing the count when new products are added, but it misses key scenarios like updates that flip a product's active status or deleting active products. Let's build a robust solution that covers every case where the active product count should change.

Step 1: Update the Trigger Function #

We need a function that checks the before-and-after state (for updates) or the state of inserted/deleted rows, then adjusts the count accordingly. Here's the improved version:

CREATE OR REPLACE FUNCTION public.product_count_adjust()
RETURNS trigger
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
    -- Handle INSERT: Increment count only if the new product is active
    IF TG_OP = 'INSERT' THEN
        IF NEW.prod_datetill IS NULL THEN
            UPDATE prodcount SET prod_count = prod_count + 1;
        END IF;
        RETURN NEW;
    END IF;

    -- Handle UPDATE: Compare old and new active status to adjust count
    IF TG_OP = 'UPDATE' THEN
        -- Case 1: Product was active, now becomes inactive
        IF OLD.prod_datetill IS NULL AND NEW.prod_datetill IS NOT NULL THEN
            UPDATE prodcount SET prod_count = prod_count - 1;
        -- Case 2: Product was inactive, now becomes active
        ELSIF OLD.prod_datetill IS NOT NULL AND NEW.prod_datetill IS NULL THEN
            UPDATE prodcount SET prod_count = prod_count + 1;
        END IF;
        RETURN NEW;
    END IF;

    -- Handle DELETE: Decrement count if the deleted product was active
    IF TG_OP = 'DELETE' THEN
        IF OLD.prod_datetill IS NULL THEN
            UPDATE prodcount SET prod_count = prod_count - 1;
        END IF;
        RETURN OLD;
    END IF;

    RETURN NULL;
END;
$BODY$;

Step 2: Create the Trigger to Cover All Relevant Operations #

We need the trigger to fire only when actions that affect active status happen:

• A new product is inserted
• The prod_datetill field is updated (this is the only field that determines active status)
• A product is deleted

CREATE TRIGGER trigger_product_count_adjust
AFTER INSERT OR UPDATE OF prod_datetill OR DELETE ON public.product
FOR EACH ROW
EXECUTE FUNCTION public.product_count_adjust();

Step 3: Initialize the Count Correctly #

Before enabling the trigger, make sure your prodcount table has the accurate initial count of active products:

-- Run this first if prodcount already exists (create the table first if it doesn't)
UPDATE prodcount SET prod_count = (SELECT COUNT(*) FROM product WHERE prod_datetill IS NULL);

Why This Works Better #

• Covers all state changes: It accounts for products switching between active/inactive via updates, new active products being added, and active products being deleted.
• Efficient: Only adjusts the count when the active status actually changes, avoiding unnecessary database writes.
• Transparent: Uses TG_OP to explicitly handle each operation type, making the logic easy to read and debug.

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