基于表A插入更新表B的触发器报错‘Enter Binds for New’排查

Let's break down why you're hitting that "Enter Binds for New" prompt and get your trigger working correctly. Here are the key issues and fixes:

1. Incomplete WHERE Clause (Most Likely Culprit) #

Looking at your code snippet, the UPDATE statement cuts off at wher... — that's a critical syntax error. Oracle can't parse an incomplete WHERE clause, which is probably causing the tool you're using (like SQL Developer or PL/SQL Developer) to incorrectly prompt for bind variables.

You need to finish the WHERE condition to explicitly match IDENT from table A to the combined REGION_CODE_MW || MW_ID value in table B.

2. Improper NULL/Empty Check for IDENT #

Your current check IF(:NEW.IDENT != '') THEN has a flaw: if IDENT is NULL, this comparison won't work as expected (in Oracle, comparing NULL with any value using = or != returns NULL, not true/false). Instead, use IS NOT NULL to properly handle null values, and combine it with the empty string check if you need to account for both cases.

3. Optional: Simplify Variable Usage #

You don't strictly need the link_id variable — you can directly reference :NEW.IDENT in the UPDATE statement to streamline the code, unless you plan to modify the value before using it.

Fixed Trigger Code #

Here's the corrected version of your trigger with all issues addressed:

create or replace trigger testtrigger 
after insert on A 
for each row 
BEGIN
  -- Properly check if IDENT is not null or empty
  IF :NEW.IDENT IS NOT NULL AND :NEW.IDENT != '' THEN
    UPDATE B 
    SET IMPL_DSGN = 'Yes', 
        EQUIP_AVAILABLE = 'Yes'
    -- Match A's IDENT to B's combined region + ID value
    WHERE REGION_CODE_MW || MW_ID = :NEW.IDENT;
  END IF;
END;
/

Quick Additional Checks #

• Ensure REGION_CODE_MW and MW_ID in table B can be concatenated to match the data type of IDENT in table A (e.g., cast numeric columns to strings if needed).
• If you're using an older Oracle version where empty strings are treated as NULL, the :NEW.IDENT != '' check is redundant — you can simplify the condition to just IF :NEW.IDENT IS NOT NULL THEN.

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