Trigger to compare inserted value with value from another table

insertoracletrigger

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.

Best Answer

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;

Related Solutions

Mysql – How to create a TRIGGER in MySQL from this Oracle syntax

Check the MySQL documentation about the trigger syntax and examples.

You cannot have AFTER INSERT OR UPDATE triggers in MySQL. Define 2 triggers, one for INSERT and one for UPDATE. Another limitation is that AFTER triggers cannot change the updated rows, so you can use BEFORE triggers. Like this:

delimiter //
DROP TRIGGER IF EXISTS TRG_PRODUCT_REORDER_BEFORE_INSERT ;
CREATE TRIGGER TRG_PRODUCT_REORDER_BEFORE_INSERT
BEFORE INSERT ON PRODUCT
FOR EACH ROW
    BEGIN
        IF NEW.P_ONHAND < NEW.P_MIN THEN
            SET NEW.P_REORDER = 1;
        END IF;
    END; //
delimiter ;

and similarly for the BEFORE UPDATE trigger

Mysql – trigger to delete records with same value from another table after inserting record in another table

Try this:

DELIMITER $$
CREATE TRIGGER tgr_1 AFTER INSERT ON abnormal
FOR EACH ROW
BEGIN
  DELETE FROM normal where id = NEW.id;
END $$

Best Answer

Related Solutions

Mysql – How to create a TRIGGER in MySQL from this Oracle syntax

Mysql – trigger to delete records with same value from another table after inserting record in another table

Related Question