问题

这是代码部分，不需要翻译：

CREATE TRIGGER FACTPRODUCTSALES_INSERT 
AFTER INSERT ON FACTPRODUCTSALES
FOR EACH ROW
DECLARE
    ACTUALCOST NUMBER;
    SALESCOST  NUMBER;
BEGIN
    SELECT PRODUCTACTUALCOST INTO ACTUALCOST FROM DIMPRODUCT WHERE PRODUCTKEY = :NEW.PRODUCTID;
    SELECT PRODUCTSALESCOST INTO SALESCOST FROM DIMPRODUCT WHERE PRODUCTKEY = :NEW.PRODUCTID;
    
    :NEW.SALESTOTALCOST = SALESCOST * :NEW.QUANTITY;
    :NEW.PRODUCTACTUALCOST = ACTUALCOST * :NEW.QUANTITY;
END;

帮助你解决问题的信息：

在你的SQL触发器中，似乎出现了绑定变量的问题，导致它要求绑定变量。这是因为在SQL语句中使用了冒号（:NEW.PRODUCTID，:NEW.QUANTITY等），这被Oracle解释为绑定变量。

要解决这个问题，你可以尝试以下几种方法：

1. 在SQL Developer中关闭绑定变量替换： 你已经尝试过这一步骤，但如果没有成功，可以再次检查确保已正确设置。确保你在运行SQL语句之前执行了以下命令：SET DEFINE OFF;

2. 使用PL/SQL块内部变量： 你可以在PL/SQL块内部使用变量来代替绑定变量，然后将这些变量用于SQL语句。例如：

CREATE TRIGGER FACTPRODUCTSALES_INSERT 
AFTER INSERT ON FACTPRODUCTSALES
FOR EACH ROW
DECLARE
    ACTUALCOST NUMBER;
    SALESCOST  NUMBER;
BEGIN
    SELECT PRODUCTACTUALCOST INTO ACTUALCOST FROM DIMPRODUCT WHERE PRODUCTKEY = :NEW.PRODUCTID;
    SELECT PRODUCTSALESCOST INTO SALESCOST FROM DIMPRODUCT WHERE PRODUCTKEY = :NEW.PRODUCTID;
    
    :NEW.SALESTOTALCOST := SALESCOST * :NEW.QUANTITY;
    :NEW.PRODUCTACTUALCOST := ACTUALCOST * :NEW.QUANTITY;
END;

这样，你使用了PL/SQL块内的变量而不是绑定变量。

请尝试上述方法中的一个，以解决绑定变量的问题。如果仍然存在问题，请提供更多的上下文信息，以便更详细地帮助你解决问题。

CREATE TRIGGER FACTPRODUCTSALES_INSERT 
AFTER INSERT ON FACTPRODUCTSALES
FOR EACH ROW
DECLARE
    ACTUALCOST NUMBER;
    SALESCOST  NUMBER;
BEGIN
    SELECT PRODUCTACTUALCOST INTO ACTUALCOST FROM DIMPRODUCT WHERE PRODUCTKEY = :NEW.PRODUCTID;
    SELECT PRODUCTSALESCOST INTO SALESCOST FROM DIMPRODUCT WHERE PRODUCTKEY = :NEW.PRODUCTID;
    
    :NEW.SALESTOTALCOST = SALESCOST * :NEW.QUANTITY;
    :NEW.PRODUCTACTUALCOST = ACTUALCOST * :NEW.QUANTITY;
END;

答案1

得分: 0

• 触发器必须是 before 类型（第1行）（如果你想修改 :new 的值）
• 使用 := 而不是 =（第10、11行）

SQL> CREATE OR REPLACE TRIGGER FACTPRODUCTSALES_INSERT before INSERT ON FACTPRODUCTSALES
  2  FOR EACH ROW
  3  DECLARE
  4      ACTUALCOST NUMBER;
  5      SALESCOST  NUMBER;
  6  BEGIN
  7      SELECT PRODUCTACTUALCOST INTO ACTUALCOST FROM DIMPRODUCT WHERE PRODUCTKEY = :NEW.PRODUCTID;
  8      SELECT PRODUCTSALESCOST INTO SALESCOST FROM DIMPRODUCT WHERE PRODUCTKEY = :NEW.PRODUCTID;
  9
 10      :NEW.SALESTOTALCOST := SALESCOST * :NEW.QUANTITY;
 11      :NEW.PRODUCTACTUALCOST := ACTUALCOST * :NEW.QUANTITY;
 12  END;
 13  /
  
Trigger created.

SQL>

SQL> CREATE OR REPLACE TRIGGER FACTPRODUCTSALES_INSERT before INSERT ON FACTPRODUCTSALES
  2  FOR EACH ROW
  3  DECLARE
  4      ACTUALCOST NUMBER;
  5      SALESCOST  NUMBER;
  6  BEGIN
  7      SELECT PRODUCTACTUALCOST INTO ACTUALCOST FROM DIMPRODUCT WHERE PRODUCTKEY = :NEW.PRODUCTID;
  8      SELECT PRODUCTSALESCOST INTO SALESCOST FROM DIMPRODUCT WHERE PRODUCTKEY = :NEW.PRODUCTID;
  9
 10      :NEW.SALESTOTALCOST := SALESCOST * :NEW.QUANTITY;
 11      :NEW.PRODUCTACTUALCOST := ACTUALCOST * :NEW.QUANTITY;
 12  END;
 13  /

Trigger created.

SQL>