在Oracle SQL中，为什么我的触发器一直要求我提供绑定变量？

huangapple
go评论101阅读模式
英文:

Why does my trigger keep asking me for bind variables in Oracle SQL

问题

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

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

帮助你解决问题的信息：

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

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

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

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

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

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

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

英文:

I have this trigger where I try to change SALESTOTALCOST AND PRODUCTACTUALCOST whenever a value is inserted in FACTPRODUCTSALES using the PRODUCTACTUALCOST AND PRODUCESALESCOST from DIMPRODUCT table.

Here's the code:

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

But it keeps asking me for bind variables; what is this? Why's that? And what should I do about it?

I've already tried doing SET DEFINE OFF base on the answers in this and this and it didn't work.

答案1

得分: 0

  • 触发器必须是 before 类型（第1行）（如果你想修改 :new 的值）
  • 使用 := 而不是 =（第10、11行）
  1. SQL> CREATE OR REPLACE TRIGGER FACTPRODUCTSALES_INSERT before INSERT ON FACTPRODUCTSALES
  2.   2  FOR EACH ROW
  3.   3  DECLARE
  4.   4      ACTUALCOST NUMBER;
  5.   5      SALESCOST  NUMBER;
  6.   6  BEGIN
  7.   7      SELECT PRODUCTACTUALCOST INTO ACTUALCOST FROM DIMPRODUCT WHERE PRODUCTKEY = :NEW.PRODUCTID;
  8.   8      SELECT PRODUCTSALESCOST INTO SALESCOST FROM DIMPRODUCT WHERE PRODUCTKEY = :NEW.PRODUCTID;
  9.   9
  10.  10      :NEW.SALESTOTALCOST := SALESCOST * :NEW.QUANTITY;
  11.  11      :NEW.PRODUCTACTUALCOST := ACTUALCOST * :NEW.QUANTITY;
  12.  12  END;
  13.  13  /
  14.   
  15. Trigger created.
  17. SQL>
英文:

Presuming that tables exist and contain columns involved, then:

  • trigger must be before (line #1) (if you want to modify :new values)
  • use := instead of = (lines #10, 11)
  1. SQL> CREATE OR REPLACE TRIGGER FACTPRODUCTSALES_INSERT before INSERT ON FACTPRODUCTSALES
  2.   2  FOR EACH ROW
  3.   3  DECLARE
  4.   4      ACTUALCOST NUMBER;
  5.   5      SALESCOST  NUMBER;
  6.   6  BEGIN
  7.   7      SELECT PRODUCTACTUALCOST INTO ACTUALCOST FROM DIMPRODUCT WHERE PRODUCTKEY = :NEW.PRODUCTID;
  8.   8      SELECT PRODUCTSALESCOST INTO SALESCOST FROM DIMPRODUCT WHERE PRODUCTKEY = :NEW.PRODUCTID;
  9.   9
  10.  10      :NEW.SALESTOTALCOST := SALESCOST * :NEW.QUANTITY;
  11.  11      :NEW.PRODUCTACTUALCOST := ACTUALCOST * :NEW.QUANTITY;
  12.  12  END;
  13.  13  /
  15. Trigger created.
  17. SQL>

huangapple
  • 本文由 发表于 2023年6月8日 23:26:37
  • 转载请务必保留本文链接：https://go.coder-hub.com/76433449.html
  • oracle
  • sql
  • triggers
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定