Oracle审计触发器创建报错排查：绑定变量无效及审计表无数据问题

Let's break down the issues with your trigger and fix it to meet your requirement of auditing bookings where customers give a score of 2 or lower.

First, Why You're Getting Compilation Errors #

The PLS-00049 errors are telling you that your BOOKING table doesn't have columns named CUSTOMER_NAME, START_DATE, or SHIP_NAME. When you use :NEW or :OLD in a trigger, those variables can only reference columns from the table the trigger is attached to (here, BOOKING). You probably store these details in related tables (like VOYAGES for voyage info or CUSTOMERS for customer names) instead.

On top of that:

• Your initial SELECT queries are completely unnecessary and would cause runtime errors (like NO_DATA_FOUND if no rows match, or TOO_MANY_ROWS if multiple rows do) if the trigger ever ran.
• You're not checking if the evaluation is ≤ 2—so your trigger would audit every single insert/update/delete, which isn't what you want.
• Your EVALUATIONAUDIT table requires a value for AUDITT_ID (it's marked NOT NULL), but your insert statements don't provide one—this would cause a constraint violation even if the trigger compiled.

Fixed Trigger Code #

First, let's assume a common schema setup (adjust this to match your actual tables):

• BOOKING has columns: BOOKING_ID, CUSTOMER_ID, VOYAGES_ID, BOOKING_EVALUATION
• CUSTOMERS has: CUSTOMER_ID, CUSTOMER_NAME
• VOYAGES has: VOYAGES_ID, START_DATE, SHIP_NAME, VOYAGE_NAME (for the voyage name you mentioned)
• We'll use a sequence to generate unique AUDITT_ID values (create it if you haven't: CREATE SEQUENCE EVAL_AUDIT_SEQ START WITH 1 INCREMENT BY 1;)

Here's the corrected trigger:

CREATE OR REPLACE TRIGGER EVALUATION_AUDIT_TRG
BEFORE INSERT OR UPDATE OR DELETE ON BOOKING
FOR EACH ROW
DECLARE
    v_customer_name CUSTOMERS.CUSTOMER_NAME%TYPE;
    v_start_date VOYAGES.START_DATE%TYPE;
    v_ship_name VOYAGES.SHIP_NAME%TYPE;
BEGIN
    -- Only perform audit when the evaluation is 2 or lower
    CASE
        WHEN INSERTING AND :NEW.BOOKING_EVALUATION <= 2 THEN
            -- Fetch customer and voyage details for the new booking
            SELECT c.CUSTOMER_NAME, v.START_DATE, v.SHIP_NAME
            INTO v_customer_name, v_start_date, v_ship_name
            FROM CUSTOMERS c
            JOIN VOYAGES v ON :NEW.VOYAGES_ID = v.VOYAGES_ID
            WHERE c.CUSTOMER_ID = :NEW.CUSTOMER_ID;
            
            -- Insert the audit record
            INSERT INTO EVALUATIONAUDIT (
                AUDITT_ID,
                VOYAGES_ID,
                CUSTOMER_NAME,
                START_DATE,
                SHIP_NAME,
                BOOKING_EVALUATION
            ) VALUES (
                EVAL_AUDIT_SEQ.NEXTVAL,
                :NEW.VOYAGES_ID,
                v_customer_name,
                v_start_date,
                v_ship_name,
                :NEW.BOOKING_EVALUATION
            );
        
        WHEN UPDATING AND :NEW.BOOKING_EVALUATION <= 2 THEN
            -- Fetch details using the updated booking values
            SELECT c.CUSTOMER_NAME, v.START_DATE, v.SHIP_NAME
            INTO v_customer_name, v_start_date, v_ship_name
            FROM CUSTOMERS c
            JOIN VOYAGES v ON :NEW.VOYAGES_ID = v.VOYAGES_ID
            WHERE c.CUSTOMER_ID = :NEW.CUSTOMER_ID;
            
            INSERT INTO EVALUATIONAUDIT (
                AUDITT_ID,
                VOYAGES_ID,
                CUSTOMER_NAME,
                START_DATE,
                SHIP_NAME,
                BOOKING_EVALUATION
            ) VALUES (
                EVAL_AUDIT_SEQ.NEXTVAL,
                :NEW.VOYAGES_ID,
                v_customer_name,
                v_start_date,
                v_ship_name,
                :NEW.BOOKING_EVALUATION
            );
        
        WHEN DELETING AND :OLD.BOOKING_EVALUATION <= 2 THEN
            -- Fetch details using the deleted booking's old values
            SELECT c.CUSTOMER_NAME, v.START_DATE, v.SHIP_NAME
            INTO v_customer_name, v_start_date, v_ship_name
            FROM CUSTOMERS c
            JOIN VOYAGES v ON :OLD.VOYAGES_ID = v.VOYAGES_ID
            WHERE c.CUSTOMER_ID = :OLD.CUSTOMER_ID;
            
            INSERT INTO EVALUATIONAUDIT (
                AUDITT_ID,
                VOYAGES_ID,
                CUSTOMER_NAME,
                START_DATE,
                SHIP_NAME,
                BOOKING_EVALUATION
            ) VALUES (
                EVAL_AUDIT_SEQ.NEXTVAL,
                :OLD.VOYAGES_ID,
                v_customer_name,
                v_start_date,
                v_ship_name,
                :OLD.BOOKING_EVALUATION
            );
    END CASE;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- Optional: Handle cases where customer/voyage records are missing
        -- You could log an error here instead of NULL if needed
        NULL;
END;
/

Key Changes & Explanations #

1. Removed useless queries: The initial SELECT statements that didn't contribute to your audit logic are gone, eliminating potential runtime crashes.
2. Added evaluation check: We only trigger the audit when the score is ≤ 2, which aligns with your requirement.
3. Correct data retrieval: We join related tables to get the customer name and voyage details since those columns aren't in BOOKING.
4. Handled mandatory ID: We use a sequence to populate AUDITT_ID, so we don't violate the NOT NULL constraint.
5. Cleaner logic: The CASE statement makes the trigger easier to read and maintain compared to multiple separate IF blocks.
6. Exception handling: Added a basic handler for NO_DATA_FOUND to prevent the trigger from failing if related customer/voyage records are missing (adjust this based on your business rules).

How to Verify It Works #

1. Compile the trigger and check for errors:

   SHOW ERRORS TRIGGER EVALUATION_AUDIT_TRG;
   

2. Test with a booking that has a low evaluation:

   -- Replace with valid IDs from your tables
   INSERT INTO BOOKING (BOOKING_ID, CUSTOMER_ID, VOYAGES_ID, BOOKING_EVALUATION)
   VALUES (1001, 501, 301, 1);
   

3. Check the audit table to confirm the record was inserted:

   SELECT * FROM EVALUATIONAUDIT;
   

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