Oracle触发器错误排查与正确实现：限制关联已取消奥运会的赛事

Let's break down the issues in your trigger code that are causing those frustrating errors, plus fix a logic mix-up that would have kept it from working as intended:

What's Causing the Errors? #

1. Unnecessary join with the Event table
   Your SELECT statement joins Olympic_Game to Event, but this is totally unnecessary. We only need to check if the og_id being used in the new/updated Event row links to a cancelled game. Joining with Event creates two big problems:

   • ORA-01422 (Too many rows returned): If there are multiple existing Event rows with the same og_id, your query returns all of them—but the INTO v_og_cancel clause can only handle a single value.
   • ORA-01403 (No data found): When inserting a new Event, the row hasn't been written to the table yet, so the join finds no matches, leaving your variable empty and triggering the error.

2. Backwards error logic
   Your trigger raises an error when og_cancel = 'N' (for active games), which is the opposite of what you need! You want to block operations when the Olympic Game is cancelled (og_cancel = 'Y').

3. Messy PL/SQL structure
   You nested an extra BEGIN DECLARE block inside the trigger's main block—this isn't breaking things here, but it's unnecessary and makes the code harder to read.

Corrected Trigger Code #

Here's the fixed version that addresses all these issues:

CREATE OR REPLACE TRIGGER TR_event_on_cancelled_og
BEFORE INSERT OR UPDATE ON Event
FOR EACH ROW
DECLARE
    v_og_cancel Olympic_Game.og_cancel%TYPE;
BEGIN
    -- Grab the cancellation status of the linked Olympic Game
    SELECT og_cancel
    INTO v_og_cancel
    FROM Olympic_Game
    WHERE og_id = :NEW.og_id;

    -- Block the operation if the game is cancelled
    IF v_og_cancel = 'Y' THEN
        RAISE_APPLICATION_ERROR(-20001, 'Cannot add/update event: This Olympic Game has been cancelled.');
    END IF;
EXCEPTION
    -- Handle edge case where og_id doesn't exist (foreign key should prevent this, but just in case)
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20002, 'Invalid og_id: No corresponding Olympic Game exists.');
END;
/

Key Fixes & Improvements: #

• Removed the Event table join: We now directly query Olympic_Game using the :NEW.og_id from the trigger's context—this eliminates the row count issues entirely.
• Fixed the error condition: Now it only throws an error when the game is marked as cancelled (og_cancel = 'Y'), which matches your requirement.
• Added exception handling: While the foreign key constraint on Event.og_id should stop invalid IDs from being used, the NO_DATA_FOUND handler gives a clearer error message if something slips through.
• Cleaned up the code structure: The variable is declared in the correct place, and we got rid of the unnecessary nested block.

Testing the Trigger #

To verify it works, try inserting an Event using one of your cancelled game IDs (like og_id = 6 from your test data):

INSERT INTO event(event_id, sport_id, og_id, event_title, event_team, no_per_team, event_gender)
VALUES(event_seq.nextval, 1, 6, 'Test 100m', 'N', 1, 'M');

You should see your custom error: ORA-20001: Cannot add/update event: This Olympic Game has been cancelled.

Inserting or updating events linked to active games will work normally.

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