CODEWITHSUNDEEP
oracle-databaseplsqltriggers
Richard C
Richard C

Reputation: 417

Oracle PL/SQL Trigger to limit orders per customer

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.

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?

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;

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

Thanks a lot Richard

Upvotes: 1

Views: 1759

Answers (1)

Xophmeister
Xophmeister

Reputation: 9211

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;

It should be:

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

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;

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.

Upvotes: 3

Related Questions