Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

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

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...