求助：绑定变量解决方案及PLS-00049触发器错误排查

Hey, let's break down and fix that PLS-00049 error you're hitting with your trigger. The issue is super straightforward once you spot it:

错误根源 #

You're referencing :new.aatype in your insert statement, but your transaction_type table only has a type column—there's no aatype column to bind to! That's exactly why the database is throwing the "bad bind variable" error.

额外的代码优化点 #

Looking at your original code, there are a couple of unnecessary bits we can clean up to make it simpler:

• You declared an aatype variable that doesn't serve any practical purpose—we can directly use the actual column from the triggering table instead.
• Since this is an AFTER INSERT trigger, the INSERTING check in the CASE statement is redundant (the trigger only runs when an insert operation happens), so we can ditch that conditional block entirely.

修正后的触发器代码 #

CREATE OR REPLACE TRIGGER transaction_type_af_insert 
AFTER INSERT ON transaction_type 
FOR EACH ROW 
DECLARE
  action VARCHAR2(30);
BEGIN
  action := 'After_Insert_transaction_type';
  -- 使用transaction_type表实际存在的type列
  INSERT INTO transaction_type_log_history (aatype, action) 
  VALUES (:new.type, action);
END;
/
SHOW ERROR;

验证说明 #

This revised code will correctly pull the type value from the newly inserted row in transaction_type and insert it into the aatype column of your transaction_type_log_history table, along with the action label. After running this, the SHOW ERROR command should return no issues if your log table structure is properly set up.

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