I had trouble converting the following command to the oracle command. I will be glad if you help!
Create Trigger sales_stock_reduction
On SalesMovements
After insert
as
Declare @ProductId int
Declare @Piece int
Select @ProductId=ProductId, @Piece=Piece from inserted
Update Uruns set stock=stock - @Piece where ProductId=@ProductId
In this code, when sales are made, the number of stocks in the product table is reduced through the sales movement table. I could not write this code in oracle. Wonder how to write in Oracle
You can convert that like this
CREATE OR REPLACE TRIGGER sales_stock_reduction
AFTER INSERT ON SalesMovements
FOR EACH ROW
DECLARE
v_ProductId inserted.ProductId%type;
v_Piece inserted.Piece%type;
BEGIN
BEGIN
SELECT ProductId, Piece
INTO v_ProductId, v_Piece
FROM inserted;
EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
END;
UPDATE Uruns
SET stock=stock - v_Piece
WHERE ProductId=v_ProductId;
END;
/
In Oracle :
OR REPLACE
clause is used whenever the trigger needs to be editedNO_DATA_FOUND
is added due to presuming at most one
row returns from the current query for the inserted
table which doesn't have
a WHERE
condition to restrict the result set