Trigger to compare inserted value with value from another table 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.
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&gt;item_qty)THEN
 RAISE_APPLICATION_ERROR(-20250,'QTY is greater than stock qty!');
END IF;
END;