PostgreSQL创建触发器：插入Ride记录时扣减Card表monthly_deduction值

Got it, let's break down how to build this trigger exactly as you need it. The key here is to link the new Ride record back to the associated Card through the Customer table, then adjust the monthly_deduction value.

Step 1: Create the Trigger Function #

First, we need a PL/pgSQL function that handles the deduction logic. This function will run every time a new row is inserted into the Ride table:

CREATE OR REPLACE FUNCTION deduct_monthly_ride_fee()
RETURNS TRIGGER AS $$
BEGIN
    -- Traverse the relationships: Ride -> Customer -> Card
    UPDATE Card
    SET monthly_deduction = monthly_deduction - 2
    FROM Customer
    WHERE Customer.costumer_ID = NEW.rider_ID
      AND Card.card_id = Customer.card_number;
    
    RETURN NEW; -- Required for AFTER triggers to pass the row data along
END;
$$ LANGUAGE plpgsql;

What this does: #

• NEW refers to the newly inserted row in the Ride table, so we use NEW.rider_ID to find the corresponding Customer.
• We join Customer and Card using their foreign key relationships to target the correct Card record.
• The UPDATE statement subtracts 2 from the monthly_deduction of that Card. If there's no matching Customer or Card (e.g., a rider ID that doesn't exist), the update simply does nothing—no errors thrown.

Step 2: Attach the Function as a Trigger #

Next, we create a trigger that runs this function after a new Ride is inserted:

CREATE TRIGGER trigger_ride_deduct_fee
AFTER INSERT ON Ride
FOR EACH ROW
EXECUTE FUNCTION deduct_monthly_ride_fee();

Trigger details: #

• AFTER INSERT: We use AFTER because we don't need to modify the Ride record itself—we just need to react to its insertion.
• FOR EACH ROW: This ensures the function runs once for every single row inserted into Ride (works for both single-row and bulk inserts).

Optional: Add Error Handling (If Needed) #

If you want to enforce that every Ride must link to a valid Customer with an associated Card (and throw an error if not), you can modify the function to include checks:

CREATE OR REPLACE FUNCTION deduct_monthly_ride_fee()
RETURNS TRIGGER AS $$
DECLARE
    linked_card_id INT;
BEGIN
    -- Check if the rider exists in Customer and has a linked Card
    SELECT card_number INTO linked_card_id
    FROM Customer
    WHERE costumer_ID = NEW.rider_ID;
    
    IF linked_card_id IS NULL THEN
        RAISE EXCEPTION 'Rider ID % is not associated with a valid Customer or Card', NEW.rider_ID;
    END IF;
    
    -- Proceed with the deduction
    UPDATE Card
    SET monthly_deduction = monthly_deduction - 2
    WHERE card_id = linked_card_id;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

This version will throw an explicit error if the new Ride's rider_ID doesn't map to a Customer with a Card, which can help catch data inconsistencies early.

内容的提问来源于stack exchange，提问作者Gabriel Bernardo Lutiano