plsql - Does anyone know how to translate this mysql(trigger) syntax to oracle syntax?

Question

CREATE DEFINER=`belito`@`%` TRIGGER `sumupdate` AFTER INSERT ON `stavkaotpremnice` FOR EACH ROW UPDATE otpremnica a
   SET a.ukupno = 
    (SELECT SUM(ukupno) 
       FROM stavkaotpremnice
      WHERE brojotpremnice = a.brojotpremnice)
 WHERE a.brojotpremnice = NEW.brojotpremnice

Im struggling doing it, made some searches but didnt go nowhere.

Answer 1

Is this working for you in MySql? If you tried doing this in Oracle using an "AFTER INSERT .... FOR EACH ROW" trigger, you would run into a "Table is mutating error" during the actual insert into stavkaotpremnice. This is because you cannot query the table being inserted into inside of a row-level trigger on that same table. You can do so in a statement-level trigger, but then you lose access to the :new values. A sort of workaround is to use a compound trigger where you can set a variable to the :new value in the "AFTER EACH ROW" section, and then use that variable in your update in the "AFTER STATEMENT" section, but I would highly discourage doing so because that will only work for single row inserts, and you'll get unexpected results if there's ever an insert statement that inserts multiple rows with different values for stavkaotpremnice.brojotpremnice.

I think the best approach here is not to do this in a trigger. Instead, either issue the otpremnica update in your application code and/or via a batch update some time later.