编译代码时遇NEW.UTC_OFFSET等绑定变量无效错误求助

Hey there, let's work through this Bad Bind Variable error you're facing with those NEW.* variables. Based on the value table schema you shared, here are the most common issues and fixes to check:

• Make sure your trigger is tied to the right table
  The NEW pseudorecord only exists in row-level triggers for the table you're inserting/updating into. If your trigger is created on a different table (not value), those fields won't exist in NEW—that's a super common slip-up. Double-check your trigger's ON clause to confirm it's targeting the value table.

• Verify column name case sensitivity
  Oracle stores column names in uppercase by default unless you created the table with double quotes around lowercase names. For example, if your table was created with "utc_offset" (lowercase), you need to reference it as :NEW."utc_offset" in the trigger. If you didn't use quotes, stick to uppercase like :NEW.UTC_OFFSET to match how Oracle stores the column name.

• Check your trigger's timing and type
  NEW is only valid in INSERT or UPDATE row-level triggers. If you're using a DELETE trigger, you should be using OLD instead—using NEW here will throw this error immediately. Confirm your trigger starts with something like AFTER INSERT OR UPDATE ON value FOR EACH ROW.

• Watch out for missing colons (Oracle-specific)
  In Oracle PL/SQL triggers, you need to prefix NEW and OLD with a colon (:) when referencing them in the trigger body. So instead of NEW.UTC_OFFSET, it should be :NEW.UTC_OFFSET. Forgetting that colon is one of the top reasons for this exact error.

• Double-check for typos
  Compare your trigger's variable names against the value table columns one by one:

  • Table has utc_offset → trigger should use :NEW.utc_offset (or uppercase with colon)
  • Table has data_date → :NEW.data_date
  • Table has hr, hr_num, data_code → :NEW.hr, :NEW.hr_num, :NEW.data_code
    Even a tiny typo (like hr_num vs hrnum) will cause this error.

• Check for variable name conflicts
  If you've declared a local variable in your trigger with the same name as a column (e.g., DECLARE utc_offset NUMBER;), it will override the NEW.utc_offset reference. Make sure there are no duplicate names in your trigger's declaration section.

Example of a Valid Trigger Snippet #

Here's a quick example of how your trigger should look to avoid these errors:

CREATE OR REPLACE TRIGGER trg_value_after_insert_update
AFTER INSERT OR UPDATE ON value
FOR EACH ROW
BEGIN
  -- Safe usage of NEW variables with colon prefix
  DBMS_OUTPUT.PUT_LINE('New UTC Offset: ' || :NEW.utc_offset);
  DBMS_OUTPUT.PUT_LINE('Data Date: ' || :NEW.data_date);
  -- Add your business logic here
END;
/

If you're still stuck, sharing the full code of your trigger would help pinpoint the exact issue.

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