SQLite3中订单表空配送地址从客户表填充的触发器实现问询

Got it, let's build this trigger for your food delivery database coursework. This solution will automatically pull the customer's preferred home address from the customer table whenever a new order is inserted (or an existing order is updated) with an empty delivery address.

Step 1: Full Trigger Code #

Here's the complete trigger script tailored to your table structure:

CREATE TRIGGER IF NOT EXISTS fill_empty_delivery_address
BEFORE INSERT OR UPDATE ON customer_order
FOR EACH ROW
WHEN (NEW.delivery_address IS NULL OR NEW.delivery_address = '')
BEGIN
    -- Fetch the customer's preferred home address and assign it to the order
    SELECT preferred_home_address INTO NEW.delivery_address
    FROM customer
    WHERE customer.customer_id = NEW.customer_id;
END;

Step 2: Breakdown of How It Works #

Let's walk through each part to make sure you understand:

• Trigger Timing: BEFORE INSERT OR UPDATE ensures the trigger runs before the order is saved to the database—so we can fix the empty address before it's stored.
• Trigger Condition: WHEN (NEW.delivery_address IS NULL OR NEW.delivery_address = '') covers both NULL values and empty strings (common edge cases for "empty" addresses). Adjust this if your definition of "empty" is different (e.g., only NULL).
• Address Assignment: The SELECT ... INTO NEW.delivery_address line grabs the preferred_home_address from the customer table where the customer_id matches the order's customer ID, then directly sets that value on the order being inserted/updated.

Step 3: Handling Multiple Address Fields #

If your customer table stores the preferred address across multiple preferred_ prefixed fields (like preferred_street, preferred_city, preferred_zip), you can concatenate them into a single delivery address string:

CREATE TRIGGER IF NOT EXISTS fill_empty_delivery_address
BEFORE INSERT OR UPDATE ON customer_order
FOR EACH ROW
WHEN (NEW.delivery_address IS NULL OR NEW.delivery_address = '')
BEGIN
    SELECT preferred_street || ', ' || preferred_city || ' ' || preferred_zip
    INTO NEW.delivery_address
    FROM customer
    WHERE customer.customer_id = NEW.customer_id;
END;

Step 4: Testing the Trigger #

To verify it works:

• Insert an order with delivery_address set to NULL or an empty string, linked to a customer with a valid preferred_home_address.
• Check the customer_order table—you should see the delivery address automatically filled with the customer's preferred address.
• Try updating an existing order's delivery address to empty, and confirm it gets replaced with the preferred address.

Key Notes #

• Make sure your customer_order table has a customer_id field that links to the customer table's primary key (this is how we match the right customer to the order).
• If a customer doesn't have a preferred_home_address set, the trigger will leave the delivery address as NULL—you might want to add a fallback (like a default address) if needed for your coursework.

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