0

i have table called 'barang' with this trigger

enter image description here

so basically i used trigger to update column kategori_nama, based on another column on my table called kategori with barang.kategori = kategori.kode and then update table barang.kategori_nama with kategori.nama

when i build this trigger, why it turns error like this #1442 - Can't update table 'barang' in stored function/trigger because it is already used by statement which invoked this stored function/trigger

UPDATE

i did like @Solarflare advice to look at this answer https://stackoverflow.com/a/42333142/17202382

so i did this into my trigger (because there are several process so i combine into one trigger)

    DROP TRIGGER IF EXISTS `update_date`;CREATE DEFINER=`root`@`localhost` TRIGGER `update_date` BEFORE INSERT ON
 `barang` FOR EACH ROW BEGIN SET NEW.tanggal = NOW(); UPDATE barang t1 INNER JOIN kategori t2 ON t1.barang = t2.kode SET t1.kategori_nama = t2.nama; SET NEW.hargadepresiasi = NEW.hargabeli * 2 / 8; END
tryharder
  • 43
  • 6
  • 1
    Have a look at [ERROR 1442: Can't update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger](https://stackoverflow.com/q/6400618) (or any other of the "related" quesions on the right side) – Solarflare Nov 30 '21 at 02:56
  • That error basically means: the table specified for the action `barang`, _cannot_ also be used within the definition. – Paul T. Nov 30 '21 at 03:05
  • @Solarflare i already did based on that link's answer, but still turns the same error, check my update, thank you – tryharder Nov 30 '21 at 14:05
  • 1
    No, I don't think you have based it on that answer, or I misunderstood your problem. You cannot use an actual update, but have to *just* use (without update infront of it) `SET new.columnname = (select ... from othertable where ... = new.columnname)`. You can however only modify the one row you just inserted, no other row in the table. If you are trying to do that (e.g. if you are not just trying to get the correct value from another table into your newly inserted row): you cannot do it (you will probably have to fix your datamodel to not be required to do it). – Solarflare Nov 30 '21 at 14:17
  • omg, your explanation is so right, can you post it to answer so i can mark as an answer on ```SET new.columname = (select ... from othertable where ... = new.columnname) with your explanation that we cant update all the row, just one row – tryharder Nov 30 '21 at 15:07
  • I won't add an answer (I would literally write 1:1 what a lot of answers already wrote), but feel free to write an answer yourself, maybe emphasizing on the things that you missed from the other answers (e.g the "just one row"). Other users might be in the same situation as you were, and might be helped by an answer written from your perspective. – Solarflare Dec 01 '21 at 23:03

0 Answers0