1

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.

2
  • The problematic row is IF NEW.barang_kode not in (kode_barang_tbl.kode_barang) THEN. You must use IF NEW.barang_kode not in (SELECT kode_barang FROM kode_barang_tbl) THEN – Akina Nov 19 '20 at 9:31
  • PS. Your code is strange... for example, the subquery asdf by fact only checks does at least one row with ID = 'po2' exists in PURCHASE_ORDER. And by fact temporary tables not needed at all. – Akina Nov 19 '20 at 9:36
0

As akina aleready noted you don't need any temp tables

CREATE TABLE PURCHASE_ORDER(id int ,REQ_VENDOR_ID varchar(50) )
INSERT INTO PURCHASE_ORDER VALUES (1,'or123')
CREATE TABLE PURCHASE_ORDER_BARANG (barang_kode  varchar(50),PURCHASE_ORDER_ID int, id2 int,id3 int)
CREATE TABLe VENDOR_BARANG (id int, `VENDOR_ID` varchar(50),KODE_BARANG varchar(50))
INSERT INTO VENDOR_BARANG values (1,'or123','or123')
CREATE TRIGGER pob_exists_barang_kode BEFORE INSERT
ON `PURCHASE_ORDER_BARANG` FOR EACH ROW
BEGIN
    declare msg varchar(128);

     IF NOT EXISTS (SELECT 1
    FroM  (
            SELECT po.`REQ_VENDOR_ID` as rvid
            FROM `PURCHASE_ORDER` po INNER JOIN
                 `PURCHASE_ORDER_BARANG` pob
            WHERE NEW.`PURCHASE_ORDER_ID` = po.`ID`
          ) `asdf` INNER JOIN 
          `VENDOR_BARANG` ven ON
     ven.`VENDOR_ID` = asdf.rvid 
    WHERE NEW.barang_kode = ven.`KODE_BARANG`) then
        SELECT po.`REQ_VENDOR_ID` INTO @rvid
            FROM `PURCHASE_ORDER` po,
                 `PURCHASE_ORDER_BARANG` pob
            WHERE NEW.`PURCHASE_ORDER_ID` = po.`ID`;
            
        set msg = concat('Kesalahan! Tidak ada kode barang ', 
                          cast(new.barang_kode as char), 
                          ' pada vendor id ', 
                          cast(COALESCE(@rvid,-1) as char)
                        );
        signal sqlstate '45000' set message_text = msg;
    END IF;
    
END
insert into `PURCHASE_ORDER_BARANG` values ('po2', '1', 5135, null);
Kesalahan! Tidak ada kode barang po2 pada vendor id -1

db<>fiddle here

0

The solution is now found by Akina, I made a mistake on IF NEW.barang_kode not in (kode_barang_tbl.kode_barang) THEN which should be IF NEW.barang_kode not in (SELECT kode_barang FROM kode_barang_tbl) THEN. I also made changes to my script which makes the same error on cast(dummy1.rvid as char). So here's my full script in the end. Thanks to Akina.

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
            WHERE NEW.`PURCHASE_ORDER_ID` = po.`ID`
          ) `asdf`,
          `VENDOR_BARANG` ven
    WHERE ven.`VENDOR_ID` = asdf.rvid;
    
    SELECT po.`REQ_VENDOR_ID` as rvid
            FROM `PURCHASE_ORDER` po
            WHERE NEW.`PURCHASE_ORDER_ID` = po.`ID` INTO @rvid;
    
    
    IF NEW.barang_kode not in (SELECT kode_barang FROM kode_barang_tbl) THEN 
        set @errmsg = concat('Kesalahan! Tidak ada kode barang ', 
                          cast(new.barang_kode as char), 
                          ' pada vendor id ', 
                          cast(@rvid as char)
                        );
            DROP TEMPORARY TABLE kode_barang_tbl;
        signal sqlstate '45000' set message_text = @errmsg;
    END IF;
    
    DROP TEMPORARY TABLE kode_barang_tbl;
END;//

DELIMITER ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.