创建Oracle SQL触发器遇ORA-24344编译错误，请求排查

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

Key Errors Causing ORA-24344 #

1. Incorrect sequence reference: You tried to access the sequence as new.seq_cardholder, but sequences aren't columns in the customers table. The correct way to get the next value from an Oracle sequence is using seq_name.NEXTVAL.
2. Misleading comment: Your comment says you're inserting into customers, but the actual target is cardholder—this doesn't break compilation, but it's confusing for future maintenance.

Corrected Trigger Code #

CREATE OR REPLACE TRIGGER tgr_customer_insert
AFTER INSERT ON customers
FOR EACH ROW
BEGIN
    -- Insert new record into cardholder table
    INSERT INTO cardholder (
        card_number, customer_id, credit_limit
    ) VALUES (
        seq_cardholder.NEXTVAL, :new.customer_id, :new.credit_limit
    );
END;
/

What Changed? #

• Sequence call fixed: Replaced new.seq_cardholder with seq_cardholder.NEXTVAL—this properly fetches the next value from your sequence.
• Comment updated: Adjusted the comment to reflect the actual table being modified, making the code easier to follow.
• Added trailing /: In Oracle SQL environments like SQL*Plus or SQL Developer, this terminates the PL/SQL block and triggers compilation. Omitting it can sometimes lead to hidden compilation issues.

Optional: If You Need to Fetch from temp_table_customers #

Your original requirement mentions getting customer_id and credit_limit from temp_table_customers—if that's not a typo and you need to pull data from that temp table instead of the inserted customers row, here's how to adjust the trigger:

CREATE OR REPLACE TRIGGER tgr_customer_insert
AFTER INSERT ON customers
FOR EACH ROW
DECLARE
    v_credit_limit cardholder.credit_limit%TYPE;
BEGIN
    -- Retrieve credit_limit from temp_table_customers (adjust the WHERE clause as needed)
    SELECT credit_limit
    INTO v_credit_limit
    FROM temp_table_customers
    WHERE customer_id = :new.customer_id;

    INSERT INTO cardholder (
        card_number, customer_id, credit_limit
    ) VALUES (
        seq_cardholder.NEXTVAL, :new.customer_id, v_credit_limit
    );
END;
/

Just make sure the WHERE clause correctly matches the temp table's data to the newly inserted customers row.

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