PL/SQL触发器开发需求：统计码头停靠船只数量并更新字段

Alright, let's build that PL/SQL trigger to keep your Piers.NumOfShips column in sync with the actual number of ships docked at each pier. Here's how to do it properly:

This trigger will automatically update the ship count whenever a ship is docked, moved to another pier, or undocked (via changes to the Docking table):

CREATE OR REPLACE TRIGGER Update_Pier_Ship_Count
AFTER INSERT OR UPDATE OR DELETE ON Docking
FOR EACH ROW
DECLARE
BEGIN
    -- Update the new pier's count when inserting or changing a ship's dock
    IF INSERTING OR UPDATING THEN
        UPDATE Piers p
        SET p.NumOfShips = (
            SELECT COUNT(*)
            FROM Docking d
            WHERE d.PierID = :NEW.PierID
        )
        WHERE p.PierID = :NEW.PierID;
    END IF;

    -- Update the old pier's count when deleting or moving a ship away
    IF DELETING OR UPDATING THEN
        UPDATE Piers p
        SET p.NumOfShips = (
            SELECT COUNT(*)
            FROM Docking d
            WHERE d.PierID = :OLD.PierID
        )
        WHERE p.PierID = :OLD.PierID;
    END IF;
END;
/

Let's break down what this trigger does:

• Trigger Timing & Scope: We use AFTER INSERT OR UPDATE OR DELETE because we want to recalculate counts after the change to the Docking table is applied. The FOR EACH ROW clause ensures we handle every individual ship docking/moving/undocking event.
• INSERT/UPDATE Logic: When a new ship is docked (INSERT) or a ship is moved to a different pier (UPDATE), we refresh the ship count for the pier the ship is now at (:NEW.PierID).
• DELETE/UPDATE Logic: When a ship is undocked (DELETE) or moved away from a pier (UPDATE), we refresh the count for the pier the ship was previously at (:OLD.PierID).
• Count Calculation: The subquery COUNT(*) on the Docking table gives the exact number of ships currently docked at the target pier, even if that number drops to 0.

• Empty Piers: If all ships leave a pier, the COUNT(*) will return 0, which correctly sets NumOfShips to 0 (no need for special handling).
• Performance: Add an index on Docking.PierID to speed up the count subqueries, especially if your Docking table has a lot of records.
• Validation: Ensure that PierID values in Docking always exist in Piers (you can add a foreign key constraint to prevent invalid pier entries, which would break the trigger's update logic).

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