Oracle PL/SQL触发器以限制每个客户的订单 [英] Oracle PL/SQL Trigger to limit orders per customer

问题描述

只需创建一个小触发器即可.我希望此触发器执行的操作是确保一个客户当前一次只能在已下达的订单表中拥有10个订单.不要问为什么我的客户想要这个对我来说似乎很愚蠢.

Just working on creating a little trigger. What I'm wanting this trigger to do is make sure that a customer can only have 10 orders in the placed order table at one current time. Don't ask why my client wants this it seems silly to me ha.

但是基本上触发器是在下订单表上，我目前在customer_ID上选择一个DISTINCT COUNT并将其放入v_count VARIABLE中.

But basically the trigger is on the placed order table, I'm currently selecting a DISTINCT COUNT on the customer_ID and placing it into a v_count VARIABLE.

IF v_count < 10 INSERT INTO placed_order
ELSE
DBMS_OUTPUT.PUT_LINE ('you have 10 or more orders processing please wait')
END if
END

那是代码的基本要旨，但它不会运行，如果有人愿意，我可以显示完整的代码吗?

That is the basic jist of the code but it just won't run I can show the full code if anyone would like?

代码在这里-抱歉，我现在不知道如何使用SQLFiddle.

HERE IS THE CODE - sorry i don't know how to use SQLFiddle right now.

     CREATE OR REPLACE TRIGGER trg_order_limit
     BEFORE INSERT
     ON placed_order
     FOR EACH ROW
     DECLARE
     v_count number;
     BEGIN
     SELECT COUNT(DISTINCT FK1_customer_id) FROM placed_order into v_count;
     if v_count < 10 then
     INSERT INTO placed_order
     (order_id,  order_date, delivery_date, FK1_customer_id, FK2_employee_id,        FK3_Order_type_id)
     VALUES
     (:NEW.order_id, :NEW.order_date, :NEW.delivery_date, :NEW.FK1_customer_id,     :NEW.FK2employee_id, :NEW.FK3_order_type_id);
     ELSE
     v_count > 10 then
     DBMS_OUTPUT.PUT_LINE('You currently have 10 or more orders processing.');
     end if;
     end;

当我在oracle中运行脚本时，我得到了 第4行出现错误:PL/SQL:ORA-00933:SQL命令未正确结束

When i run the script in oracle i get Error at line 4: PL/SQL: ORA-00933: SQL command not properly ended

非常感谢 理查德

推荐答案

即使您的触发器没有语法错误，它也不起作用:它只会输出不能超过10个订单"消息，插入内容仍将继续；此外，如果它通过了测试，您将陷入插入循环中.如果有太多的记录，则需要使它引发异常，并且您的应用程序需要捕获该异常，并且如果它通过了测试，则什么也不做.

Even if your trigger didn't have a syntax error, it wouldn't work: It would just output the "Can't have more than 10 orders" message and the insert would still go ahead; moreover, if it passed the test, you'd be put into a loop of inserts. You need to make it throw an exception, in the event of too many records, and your application needs to catch that, and do nothing if it passes the test.

至于错误，我认为问题出在这一行:

As for the error, I think the problem is with this line:

SELECT COUNT(DISTINCT FK1_customer_id) FROM placed_order into v_count;

应该是:

SELECT COUNT(DISTINCT FK1_customer_id) into v_count FROM placed_order;

但是，此查询仍然是错误的:它将返回已下订单的唯一客户的数量.您正在寻找的是:

However, this query is wrong anyway: It will return the number of unique customers that have made orders. What you're looking for is:

select count(order_id) into v_count from placed_order where fk1_customer_id = :new.fk1_customer_id

假定order_id具有唯一性约束；这似乎有可能！因此，无论如何，您的触发代码应类似于:

Presuming that order_id has a uniqueness constraint; which seems likely! Anyway, therefore, your trigger code should be something like:

create or replace trigger trg_order_limit
before insert on placed_order for each row
declare
  v_count number;
begin
  -- Get current order count
  select count(order_id)
  into   v_count
  from   placed_order
  where  fk1_customer_id = :new.fk1_customer_id;

  -- Raise exception if there are too many
  if v_count >= 10 then
    raise_application_error(-20000, 'You currently have 10 or more orders processing.');
  end if;
end;

但是，根据 @DazzaL的评论，这种方法可能不是一个好主意.

However, as per @DazzaL's comment, this approach probably isn't a good idea.

编辑突然意识到，您要查询每个客户的订单数是完全错误的.更新了我的答案.

EDIT Sudden realisation that your query to count the number of orders per client is totally wrong. Updated my answer.

这篇关于Oracle PL/SQL触发器以限制每个客户的订单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！