oracle-databaseplsqltriggersoraclecommand

Problem with translating mysql command to oracle command - triggers


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


Solution

  • 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 :