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

please put your code in the question instead of linking the screenshot of it, as it will be easier for us to follow. Also, please add more details to your question if there are any. — thepunitsingh, Dec 20 '20 at 19:12

Barbaros Özhan · Accepted Answer · 2020-12-20T19:38:19.943

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

edited Dec 20 '20 at 19:38

answered Dec 20 '20 at 19:28

Barbaros Özhan

59,113
10
31
55

I understood what you wrote very well, but it says oracle inserted table does not exist. I guess the inserted command is something special to a sql server. – anyone Dec 20 '20 at 20:01
1

Thanks! @Barbaros Özhan – anyone Dec 20 '20 at 20:41
you're welcome @anyone Are you able to run ? – Barbaros Özhan Dec 20 '20 at 20:49
when i compiled it is show this errors; **ORA-01013: user requested cancellation of current operation, **Unexpected runtime exception while delivering HashStructureHookEvent @Barbaros Özhan – anyone Dec 20 '20 at 21:25
that's not pertaining to the trigger, seems like you started to run a DML, and interrupted before the data have been commited @anyone – Barbaros Özhan Dec 20 '20 at 21:35
I think I might have accidentally ran a DML for the 'Uruns' table while trying to do this trigger. Is there any way to stop this? @Barbaros Özhan – anyone Dec 20 '20 at 21:39
Which product are you using as IDE @anyone ? – Barbaros Özhan Dec 20 '20 at 21:41
I am working on a web project with visual studio and oracle sql developer. @Barbaros Özhan – anyone Dec 20 '20 at 21:44
Unfortunately I don't use that, but there should be an item on the top menu to break it up, or you can use `ALTER SYSTEM KILL SESSION ''` if you can access into sys as sysdba privilege @anyone – Barbaros Özhan Dec 20 '20 at 21:48
1

ok i will try thank you for your help @Barbaros Özhan – anyone Dec 20 '20 at 21:51
1

`satis_stok_azalt` and `sales_stock_reduction` are the same triggers ? Btw, You shouldn't create an object within system or sys schemas. Does `inserted` table contain more than one row? @anyone if that's the case then add a WHERE with a suitable condition and replace `no_data_found` with `others`. – Barbaros Özhan Dec 20 '20 at 21:51
yes the two are the same trigger. I created the 'inserted' table myself and added .'urunid' and 'adet' to it. Was it wrong? – anyone Dec 20 '20 at 21:54
if there's only one row, then no problem @anyone – Barbaros Özhan Dec 20 '20 at 21:55

Problem with translating mysql command to oracle command - triggers

1 Answers1