StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POOracle PL/SQL Trigger to limit orders per customer
text
Body
copied!<p>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.</p> <p>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.</p> <pre><code>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 </code></pre> <p>That is the basic jist of the code but it just won't run I can show the full code if anyone would like?</p> <p>HERE IS THE CODE - sorry i don't know how to use SQLFiddle right now.</p> <pre><code> 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; </code></pre> <p>When i run the script in oracle i get Error at line 4: PL/SQL: ORA-00933: SQL command not properly ended</p> <p>Thanks a lot Richard</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload