hy9njQFt

1
2DELIMITER //
3DROP TRIGGER IF EXISTS pob_exists_barang_kode//
4CREATE TRIGGER pob_exists_barang_kode BEFORE INSERT
5ON `PURCHASE_ORDER_BARANG` FOR EACH ROW
6BEGIN
7	declare msg varchar(128);
8	CREATE TEMPORARY TABLE kode_barang_tbl 
9	SELECT ven.`KODE_BARANG`  as kode_barang
10	FroM  (
11			SELECT po.`REQ_VENDOR_ID` as rvid
12			FROM `PURCHASE_ORDER` po,
13				 `PURCHASE_ORDER_BARANG` pob
14			WHERE NEW.`PURCHASE_ORDER_ID` = po.`ID`
15		  ) `asdf`,
16		  `VENDOR_BARANG` ven
17	WHERE ven.`VENDOR_ID` = asdf.rvid;
18    
19    CREATE TEMPORARY TABLE dummy1
20    SELECT po.`REQ_VENDOR_ID` as rvid
21			FROM `PURCHASE_ORDER` po,
22				 `PURCHASE_ORDER_BARANG` pob
23			WHERE NEW.`PURCHASE_ORDER_ID` = po.`ID`;
24	
25	IF NEW.barang_kode not in (kode_barang_tbl.kode_barang) THEN 
26		set msg = concat('Kesalahan! Tidak ada kode barang ', 
27						  cast(new.barang_kode as char), 
28                          ' pada vendor id ', 
29                          cast(dummy1.rvid as char)
30						);
31		signal sqlstate '45000' set message_text = msg;
32    END IF;
33	
34    DROP TEMPORARY TABLE dummy1;
35    DROP TEMPORARY TABLE kode_barang_tbl;
36END;//
37
38DELIMITER ;
39
40insert into `PURCHASE_ORDER_BARANG` values ('po1', '1', 5135, null);