I am creating a trigger BEFORE INSERT
that checks a record from another table to see if it exists, otherwise signal sqlstate
. The trigger definition shown below
DELIMITER //
DROP TRIGGER IF EXISTS pob_exists_barang_kode//
CREATE TRIGGER pob_exists_barang_kode BEFORE INSERT
ON `PURCHASE_ORDER_BARANG` FOR EACH ROW
BEGIN
declare msg varchar(128);
CREATE TEMPORARY TABLE kode_barang_tbl
SELECT ven.`KODE_BARANG` as kode_barang
FroM (
SELECT po.`REQ_VENDOR_ID` as rvid
FROM `PURCHASE_ORDER` po,
`PURCHASE_ORDER_BARANG` pob
WHERE NEW.`PURCHASE_ORDER_ID` = po.`ID`
) `asdf`,
`VENDOR_BARANG` ven
WHERE ven.`VENDOR_ID` = asdf.rvid;
CREATE TEMPORARY TABLE dummy1
SELECT po.`REQ_VENDOR_ID` as rvid
FROM `PURCHASE_ORDER` po,
`PURCHASE_ORDER_BARANG` pob
WHERE NEW.`PURCHASE_ORDER_ID` = po.`ID`;
IF NEW.barang_kode not in (kode_barang_tbl.kode_barang) THEN
set msg = concat('Kesalahan! Tidak ada kode barang ',
cast(new.barang_kode as char),
' pada vendor id ',
cast(dummy1.rvid as char)
);
signal sqlstate '45000' set message_text = msg;
END IF;
DROP TEMPORARY TABLE dummy1;
DROP TEMPORARY TABLE kode_barang_tbl;
END;//
DELIMITER ;
Trigger created fine, so I ran this script below to test if the trigger works:
insert into `PURCHASE_ORDER_BARANG` values ('po2', '1', 5135, null);
but it returns an error message
Error Code: 1109. Unknown table 'kode_barang_tbl' in field list
Executing the part of the statement works as expected:
SELECT ven.`KODE_BARANG` as kode_barang
FroM (
SELECT po.`REQ_VENDOR_ID` as rvid
FROM `PURCHASE_ORDER` po,
`PURCHASE_ORDER_BARANG` pob
WHERE po.`ID` = 'po2'
) `asdf`,
`VENDOR_BARANG` ven
WHERE ven.`VENDOR_ID` = asdf.rvid;
I'm not really sure which line causes the error, but I am sure I have created the temporary tables and the trigger correctly. I also have no table kode_barang_tbl
inside the database except in this trigger. Am I missing something? Any help is appreciated.
Best Answer
As akina aleready noted you don't need any temp tables
db<>fiddle here