The Trigger Problem:

Two Tables
Item_Mstr(Item_Id, Descr, Bal_Stock)
Item_Request(Item_Id, Req_Code, I_Qty )

I wrote a trigger after deletion on the Item_Request table which subtracts I_Qty from Bal_Stock from Item_Mstr for I_id, using an UPDATE command. Thus indicating the requested order has been shipped.

Now, write a trigger that makes sure the Bal_Stock at any point never becomes negative, in the case it does happen so, the update command is rolled back.

//PL SQL Trigger for Item_Master and Item_request

create table item_request(i_id number, r_code number, Qty number);

create table item_master(i_id number primary key, i_desc varchar2(20),i_bal_stock number);

create or replace trigger trig_item
after delete on item_request for each row
begin
update item_master set i_bal_stock = i_bal_stock - :old.Qty where i_id = :old.i_id;
end;

Now in the new trigger for an update on item_master to check if balance is negative you have two options:
1. Rollback the change - Not allowed in triggers.
2.Update the table setting the new value to old - Not allowed again, Oracle says you can't change the value after an update (not allowed to change :new.bal_stock).

So yeah shiznit..

"Men without a dream aren't men at all"