基本Oracle触发器审计表 [英] Basic Oracle Trigger audit table

问题描述

我遇到此错误，不知道如何解决该错误.请帮助我.

I am having this error and do not know how to fix the error.so please help me.

错误(8,11):PL/SQL:SQL语句已忽略

Error(8,11): PL/SQL: SQL Statement ignored

错误(10,26):PL/SQL:ORA-00904:"NEWRETAILPRICE":无效的标识符

Error(10,26): PL/SQL: ORA-00904: "NEWRETAILPRICE": invalid identifier

错误(17,8):PL/SQL:SQL语句已忽略

Error(17,8): PL/SQL: SQL Statement ignored

错误(19,57):PL/SQL:ORA-00904:"NEWRETAILPRICE":无效的标识符

Error(19,57): PL/SQL: ORA-00904: "NEWRETAILPRICE": invalid identifier

错误(25,6):PL/SQL:SQL语句已忽略

Error(25,6): PL/SQL: SQL Statement ignored

错误(27,26):PL/SQL:ORA-00904:"OLDRETAILPRICE":无效的标识符

Error(27,26): PL/SQL: ORA-00904: "OLDRETAILPRICE": invalid identifier

我的代码是

CREATE OR REPLACE TRIGGER product_audit
    BEFORE INSERT OR DELETE OR UPDATE ON DD_Products
    FOR EACH ROW
  DECLARE
       Var_ChangeType CHAR(1);

  BEGIN

    IF INSERTING THEN
          VAR_ChangeType := 'I';
          INSERT INTO DD_PriceChange
          (PriceChangeNo, Change_Type, ChangeBy, ChangeDate, 
         NewProductPrice,NewRetailPrice)
         VALUES (ProductHistory_SEQ.NEXTVAL, VAR_ChangeType, USER, SYSDATE,
            :NEW.ProductPrice,  :NEW.RetailPrice);

      ELSIF UPDATING THEN
      VAR_ChangeType := 'U';

       INSERT INTO DD_PriceChange
     (PriceChangeNo, Change_Type, ChangeBy, ChangeDate, 
         OldProductPrice,OldRetailPrice,NewProductPrice,NewRetailPrice)
   VALUES (ProductHistory_SEQ.NEXTVAL, VAR_ChangeType, USER, SYSDATE,
           :OLD.ProductPrice,  :OLD.RetailPrice,:NEW.ProductPrice,  :NEW.RetailPrice);

        ELSE
         VAR_ChangeType := 'D';
     INSERT INTO DD_PriceChange
    (PriceChangeNo, Change_Type, ChangeBy, ChangeDate, 
         OldProductPrice,OldRetailPrice)
   VALUES (ProductHistory_SEQ.NEXTVAL, VAR_ChangeType, USER, SYSDATE,
           :OLD.ProductPrice,  :OLD.RetailPrice);
    END IF;

  END; 
  /

我创建了一个这样的表

 Create table DD_PriceChange
 (
      PriceChangeNo NUMBER(5) PRIMARY KEY, 
      Change_Type CHAR(1), 
      ChangeBy VARCHAR2(20),
      ChangeDate DATE, 
      OldProductPrice NUMBER(5),
      NewProductPrice NUMBER(5),
      OldRetailPrice NUMBER(5),
      NewRetailPrice NUMBER(5)
 );

和

CREATE SEQUENCE ProductHistory_SEQ;

有人知道为什么我会收到此错误吗?

Does anybody know why I am getting this error please.

推荐答案

不知道为什么会出现错误，但是我剥离了所有内容，并从基础知识重建了触发器，并使之起作用:

No idea why you were getting the error but I stripped everything back and rebuilt the trigger from the basics and got this to work:

CREATE OR REPLACE TRIGGER product_audit
  BEFORE INSERT OR DELETE OR UPDATE ON DD_Products
  FOR EACH ROW
DECLARE
  VAR_ChangeType CHAR(1);
BEGIN
  IF INSERTING   THEN VAR_ChangeType := 'I';
  ELSIF UPDATING THEN VAR_ChangeType := 'U';
  ELSE                VAR_ChangeType := 'D';
  END IF;
  INSERT INTO DD_PriceChange
  (
    PriceChangeNo,
    Change_Type,
    ChangeBy,
    ChangeDate, 
    NewProductPrice,
    NewRetailPrice, 
    OldProductPrice,
    OldRetailPrice
  ) VALUES (
    ProductHistory_SEQ.NEXTVAL,
    VAR_ChangeType,
    USER,
    SYSDATE,
    :NEW.ProductPrice,
    :NEW.RetailPrice,
    :OLD.ProductPrice,
    :OLD.RetailPrice
  );
END product_audit;
/

SQLFIDDLE

这篇关于基本Oracle触发器审计表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！