MySQL - Update Column Value with Function and Trigger Before Insert

Question

i'm trying to update 2 column with trigger before insert, but i have unexpected result. i insert some data and the 2 last column will automatically inserted with values, here my first attempt

see? the last 2 column is null even i set some trigger and function to fill that columns automatically. Here my second attempt WITH EXACTLY SAME DATA

the last 2 column is filled with data, but i don't understand why the first attempt is fail?

here my trigger i use in the column total_harga

CREATE TRIGGER `set_total_harga` BEFORE INSERT ON `tbl_transaksi_detail`
 FOR EACH ROW BEGIN
set NEW.total_harga = hitungTotalHargaPerItem(NEW.qty, NEW.harga_satuan);
END

trigger for column harga_satuan

CREATE TRIGGER `set_harga_satuan` BEFORE INSERT ON `tbl_transaksi_detail`
 FOR EACH ROW BEGIN
set NEW.harga_satuan = set_Harga_Unit(NEW.unit, NEW.id_barang, NEW.no_transaksi);
END

function set_Harga_Unit

  BEGIN
  DECLARE
    q,
    id_toko INT;
  SET
    id_toko =(
    SELECT DISTINCT
      `tbl_transaksi`.`id_toko`
    FROM
      `tbl_transaksi`,
      `tbl_transaksi_detail`
    WHERE
      `tbl_transaksi`.`no_transaksi` = no_trans
  ); IF unit = "PCS" THEN
SET
  q =(
  SELECT
    `tbl_harga_jual`.`harga_pcs`
  FROM
    `tbl_harga_jual`
  WHERE
    `tbl_harga_jual`.`id_barang` = id_brg AND `tbl_harga_jual`.`id_toko` = id_toko
); RETURN q; ELSEIF unit = "PAK" THEN
SET
  q =(
  SELECT
    `tbl_harga_jual`.`harga_pak`
  FROM
    `tbl_harga_jual`
  WHERE
    `tbl_harga_jual`.`id_barang` = id_brg AND `tbl_harga_jual`.`id_toko` = id_toko
); RETURN q; ELSEIF unit = "KARTON" THEN
SET
  q =(
  SELECT
    `tbl_harga_jual`.`harga_karton`
  FROM
    `tbl_harga_jual`
  WHERE
    `tbl_harga_jual`.`id_barang` = id_brg AND `tbl_harga_jual`.`id_toko` = id_toko
); RETURN q;
END IF; RETURN q;
END

function hitungTotalHargaPerItem

BEGIN
DECLARE hasil int;
    set hasil = qty * harga_satuan;
    RETURN hasil;
END

Answer 1

it seem i have mistaken select query in function set_Harga_Unit, based on clues from @Shadow

SET
    id_toko =(
    SELECT DISTINCT
      `tbl_transaksi`.`id_toko`
    FROM
      `tbl_transaksi`,
      `tbl_transaksi_detail`<<== I DON'T NEED THIS
    WHERE
      `tbl_transaksi`.`no_transaksi` = no_trans
  ); IF unit = "PCS" THEN

when first insert in tbl_transaksi_detail, the value no_transaksi is null because i use trigger before insert in empty table (tbl_transaksi_detail), so i remove tbl_transaksi_detail from query

SET
        id_toko =(
        SELECT DISTINCT
          `tbl_transaksi`.`id_toko`
        FROM
          `tbl_transaksi`
        WHERE
          `tbl_transaksi`.`no_transaksi` = no_trans
      ); IF unit = "PCS" THEN

now it working, thanks everybody!

Answer 2

The root cause serms to be the select that sets id_toko variable's value:

  SET
    id_toko =(
    SELECT DISTINCT
      `tbl_transaksi`.`id_toko`
    FROM
      `tbl_transaksi`,
      `tbl_transaksi_detail`
    WHERE
      `tbl_transaksi`.`no_transaksi` = no_trans
  ); 

In the select you inner join tbl_transaksi_detail (the table with the trigger in question) on another table. However, in the 1st case tbl_transaksi_detail is still empty (the trigger is before insert), therefore id_toko variable is set to null.

This will result q being null, which in turn results in the entire calculation set to null.

In the 2nd case there is already a record inserted into tbl_transaksi_detail table, therefore the calculation returns a non null value. But it returns the correct values only because the 1st and 2nd records' details are exactly the same.

I do not really understand that select that calculates id_toko anyway. If that is a transaction id, then you may use last_insert_id() if it is auto increment and the transaction record has just been created or max(id_toko) to get the highest value of id_toko (this is not multi user safe).