everyone i am making an inventory database project for learning purpose.i am new to development please help me.
i have two tables
StockRecords(InventoriID, ItemID, Quantity)
Purchase_Details(SupplierID, supplier_invoice, itemID, quantity, purchase_date)
i need to update inventory whenever a new item is purchased.now i need to call a dml trigger but i am confused as how to get the last inserted value in the table.
i have written a procedure which i intend to call in trigger to update StockRecord table but i need to find the last inserted value in table Purchase_Detail so i can parse itemID and Quantity of last entry in my procedure to update record table.
There's only one column that looks like a candidate for that: the purchase_date, if it represents the last date when something was purchased. In that case, you'd have to find a row with the MAX(purchase_date). However, if there are more than one rows with the same value stored in that column, you're out of luck.
In other words, you need to modify current data model. It appears that purchase_details table doesn't have a primary key. If it did, and if it was populated by a sequence, then you'd have that column (MAX(primary_key_column)).
On the other hand, saying that there'a trigger involved: if it fires for each row, then you can use :new (or :old, depending on what you do) pseudorecord identifiers and - basically - don't care about the last row inserted - you'd deal with the current one.
Something like this:
create or replace trigger trg_ai_purdet
after insert on purchase_details
for each row
begin
insert into stockrecords (inventoriid, itemid, quantity)
values (some_seq.nextval, :new.itemid, :new.quantity);
end;
It also means that you, probably, don't need that "procedure" you mentioned, ifs only purpose is to insert a row into the STOCKRECORDS table.