Trigger to compare inserted value with value from another table

Question

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.

Answer 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;