0

I have two tables:

Stockitem (serialno, qty) serialno is the primary key and Order(serialno,orderno,qty) orderno and serialno are the primary keys.

I want to create a trigger in order to ensure that when I input the quanitity in the Order table, the quantity should be less than that of the Stockitem. How can I accomplish this? I am using Oracle SQL.

  • 4
    OK, lets say you accomplish this - with a trigger or whatever. How do you prevent the placing of 2 or more consecutive orders of, say 90 qty, when the qty of an item is 100? – ypercubeᵀᴹ Jan 18 '16 at 7:36
1

According to your question the sample tables are follows:

Table:ITEMS

Field's Name      Type
------------      ------
Serialno          Number (PK)
Qty               Number 

Table:ORDERS

Field's Name      Type
------------      ------
Orderno           Number (PK)
Serialno          Number (PK)(FK)
Qty               Number

The following trigger may solve your problem.

CREATE OR REPLACE TRIGGER check_qty 
AFTER INSERT OR UPDATE ON ORDERS
FOR EACH ROW
DECLARE
 item_qty ITEMS.qty%type;
BEGIN
 SELECT qty
 INTO item_qty
 FROM ITEMS
 WHERE serialno = :NEW.serialno;

IF(:NEW.qty>item_qty)THEN
 RAISE_APPLICATION_ERROR(-20250,'QTY is greater than stock qty!');
END IF;
END;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.