Problem with translating mysql command to oracle command - triggers

Question

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

Answer 1

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 edited
• local variables might be defined as the data type of those have within the table
• each individual statements end with a semi-colon
• exception handling for NO_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