Oracle 16岁年龄限制触发器报错求助：raise语句语法错误

Let's break down the issues in your current trigger code and fix them step by step:

Key Problems in the Original Code #

• Syntax Error: You added an extra raise keyword before raise_application_error. This procedure is called directly—no need to prefix it with raise.
• Incorrect Age Calculation: Hardcoding 2018 and just subtracting the birth year is unreliable. It ignores the month/day of birth, so someone born in December 2007 would be incorrectly flagged as 16 in January 2023 (when they’re actually still 15).
• Unlinked Query: Your SELECT doesn’t connect to the new entry record’s compno, so it checks all competitors instead of just the one being added/updated in the entry table. Also, a bare SELECT without INTO will throw a runtime error.
• Trigger Scope: While UPDATE OF compno makes sense, we need to ensure we’re validating the correct competitor for the modified entry.

Corrected Trigger Code #

CREATE OR REPLACE TRIGGER PREVENT16YRS
BEFORE INSERT OR UPDATE OF compno ON entry
FOR EACH ROW
DECLARE
    v_age NUMBER;
BEGIN
    -- Calculate the exact age of the competitor using their date of birth
    SELECT TRUNC(MONTHS_BETWEEN(SYSDATE, compdob)/12)
    INTO v_age
    FROM competitor
    WHERE compno = :NEW.compno;

    -- Check if age is under 16
    IF v_age < 16 THEN
        raise_application_error(-20001, 'Cannot add/update entry: Competitor is under 16 years old.');
    END IF;

EXCEPTION
    -- Handle case where the competitor doesn't exist (optional but recommended)
    WHEN NO_DATA_FOUND THEN
        raise_application_error(-20002, 'Cannot add/update entry: Competitor ID does not exist.');
END;
/

What We Changed #

1. Removed Extra raise: Fixed the syntax error by calling raise_application_error directly.
2. Accurate Age Calculation: Used MONTHS_BETWEEN(SYSDATE, compdob)/12 to calculate precise age, then TRUNC to get the whole number of years (no partial years counted).
3. Linked Query: Added WHERE compno = :NEW.compno to target only the competitor associated with the new/updated entry, and stored the result in a local variable v_age with INTO.
4. Added Exception Handling: Included a NO_DATA_FOUND handler to catch invalid competitor IDs, making the trigger more robust.
5. Clearer Error Message: Updated the error text to be more descriptive, so users know exactly why the operation failed.

Alternative Simplified Version (Without Local Variable) #

If you prefer a more concise approach, you can skip the local variable and check the condition directly in the query:

CREATE OR REPLACE TRIGGER PREVENT16YRS
BEFORE INSERT OR UPDATE OF compno ON entry
FOR EACH ROW
BEGIN
    -- Check if any under-16 competitor matches the new compno
    SELECT 1
    FROM dual
    WHERE EXISTS (
        SELECT 1
        FROM competitor
        WHERE compno = :NEW.compno
        AND TRUNC(MONTHS_BETWEEN(SYSDATE, compdob)/12) < 16
    );

    -- If the EXISTS returns a row, raise the error
    raise_application_error(-20001, 'Cannot add/update entry: Competitor is under 16 years old.');

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- No under-16 competitor found, allow the operation
        NULL;
    WHEN TOO_MANY_ROWS THEN
        -- Handle unexpected duplicate competitor IDs
        raise_application_error(-20003, 'Unexpected error: Multiple competitors found with the same ID.');
END;
/

This version uses EXISTS to check for under-16 competitors, and relies on NO_DATA_FOUND to skip raising the error if the competitor is old enough.

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