4zmy

Trigger Resume

Dec 10th, 2018
303
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 7.61 KB | None | 0 0
  1. CREATE
  2.     [DEFINER = { USER | CURRENT_USER }]
  3.     TRIGGER trigger_name
  4.     trigger_time trigger_event
  5.     ON tbl_name FOR EACH ROW
  6.     trigger_body
  7.  
  8. trigger_time: { BEFORE | AFTER }
  9.  
  10. trigger_event: { INSERT | UPDATE | DELETE }
  11.  
  12.  
  13. DELIMITER //
  14. CREATE TRIGGER tbl_beli_subt_upd AFTER UPDATE ON tbl_beli
  15. FOR EACH ROW
  16. BEGIN
  17.  
  18. IF NEW.harga_beli <=> OLD.harga_beli AND NEW.jumlah <=> OLD.jumlah THEN
  19. UPDATE tbl_beli SET sub_total = (NEW.harga_beli * NEW.jumlah) WHERE id_beli = OLD.id_beli;
  20. END IF;
  21.  
  22. IF NEW.harga_beli = OLD.harga_beli AND NEW.jumlah <=> OLD.jumlah THEN
  23. UPDATE tbl_beli SET sub_total = (OLD.harga_beli * NEW.jumlah) WHERE id_beli = OLD.id_beli;
  24. END IF;
  25.  
  26. IF NEW.harga_beli <=> OLD.harga_beli AND NEW.jumlah = OLD.jumlah THEN
  27. UPDATE tbl_beli SET sub_total = (NEW.harga_beli * OLD.jumlah) WHERE id_beli = OLD.id_beli;
  28. END IF;
  29.  
  30. END;
  31. //
  32. DELIMITER;
  33.  
  34.  
  35. DELIMITER //
  36. CREATE TRIGGER upd_sbt AFTER INSERT ON tbl_beli
  37. FOR EACH ROW
  38. BEGIN
  39. SET @id_beli = NEW.id_beli;
  40. SET @harga_beli = NEW.harga_beli;
  41. SET @jumlah = NEW.jumlah;
  42. SET @sub_total = @harga_beli * @jumlah;
  43. UPDATE tbl_beli SET sub_total = @sub_total WHERE id_beli = @id_beli
  44. END;
  45. //
  46. DELIMITER;
  47.  
  48.  
  49.  
  50. DROP TRIGGER trig_transaksi;
  51. DELIMITER //
  52. CREATE TRIGGER trig_transaksi after INSERT ON tbl_transaksi FOR EACH ROW
  53. BEGIN
  54. IF NEW.ktg_transaksi = 'Penjualan' THEN
  55. UPDATE tbl_barang SET stok = stok - NEW.jmlh WHERE kode = NEW.kode_brg;
  56. END IF;
  57. IF NEW.ktg_transaksi = 'Pembelian' THEN
  58. UPDATE tbl_barang SET stok = stok + NEW.jmlh, hrgbli = NEW.hrgbli WHERE kode = NEW.kode_brg;
  59. END IF;
  60. END
  61. //
  62. DELIMITER ;
  63.  
  64.  
  65. DROP TRIGGER trig_del_transaksi;
  66. DELIMITER //
  67. CREATE TRIGGER trig_del_transaksi after DELETE ON tbl_transaksi FOR EACH ROW
  68. BEGIN
  69. IF OLD.ktg_transaksi = 'Penjualan' THEN
  70. UPDATE tbl_barang SET stok = stok + OLD.jmlh WHERE kode = OLD.kode_brg;
  71. END IF;
  72. IF OLD.ktg_transaksi = 'Pembelian' THEN
  73. UPDATE tbl_barang SET stok = stok - OLD.jmlh WHERE kode = OLD.kode_brg;
  74. END IF;
  75. END
  76. //
  77. DELIMITER ;
  78.  
  79. DROP TRIGGER trig_upd_transaksi;
  80. DELIMITER //
  81. CREATE TRIGGER trig_upd_transaksi after UPDATE ON tbl_transaksi FOR EACH ROW
  82. BEGIN
  83. IF NEW.ktg_transaksi = 'Penjualan' THEN
  84. UPDATE tbl_barang SET stok = stok + OLD.jmlh - NEW.jmlh WHERE kode = NEW.kode_brg;
  85. END IF;
  86. IF NEW.ktg_transaksi = 'Pembelian' THEN
  87. UPDATE tbl_barang SET stok = stok - OLD.jmlh + NEW.jmlh, hrgbli = NEW.hrgbli WHERE kode = NEW.kode_brg;
  88. END IF;
  89. END
  90. //
  91. DELIMITER ;
  92.  
  93.  
  94. DROP TRIGGER trig_kwi;
  95. DELIMITER //
  96. CREATE TRIGGER trig_kwi after INSERT ON tbl_kwi FOR EACH ROW
  97. BEGIN
  98. UPDATE tbl_kwi_det SET tgl_byr = NEW.tgl_byr WHERE kwitansi = NEW.kwitansi;
  99. END
  100. //
  101. DELIMITER ;
  102.  
  103. DROP TRIGGER trig_kwi_lun;
  104. DELIMITER //
  105. CREATE TRIGGER trig_kwi_lun after UPDATE ON tbl_kwi_det FOR EACH ROW
  106. BEGIN
  107. UPDATE tbl_nota SET status_pembayaran = 'Lunas',  tgl_byr = NEW.tgl_byr WHERE nota = OLD.nota;
  108. END
  109. //
  110. DELIMITER ;
  111.  
  112. DELIMITER //
  113. CREATE TRIGGER trig_transaksi after INSERT ON tbl_beli FOR EACH ROW
  114. BEGIN
  115. INSERT INTO tbl_gudang (id_barang) VALUE (NEW.id_barang);
  116. END
  117. //
  118. DELIMITER ;
  119.  
  120.  
  121. DELIMITER //
  122. CREATE TRIGGER trig_transaksi after INSERT ON tbl_beli FOR EACH ROW
  123. BEGIN
  124.  
  125. DECLARE pencacah INT;
  126. SET pencacah = 0;
  127. SELECT pencacah;
  128. ulang : LOOP
  129. SET pencacah = pencacah + 1;
  130. IF pencacah > 10 THEN leave ulang; END IF;
  131. END LOOP ulang;
  132. SELECT pencacah;
  133.  
  134. END
  135. //
  136. DELIMITER ;
  137.  
  138.  
  139.  
  140.  
  141. DELIMITER //
  142. CREATE TRIGGER trig_transaksi BEFORE UPDATE ON tbl_beli FOR EACH ROW
  143. BEGIN
  144. DECLARE x INT;
  145. DECLARE n INT;
  146. SET x = 0;
  147. SET n = NEW.jumlah;
  148. ulang : LOOP
  149. SET x = x + 1;
  150. IF x > n THEN leave ulang; END IF;
  151. INSERT INTO tbl_gudang (id_barang) VALUE (NEW.id_barang);
  152. END LOOP ulang;
  153. END
  154. //
  155. DELIMITER ;
  156.  
  157.  
  158.  
  159.  
  160.  
  161.  
  162.  
  163.  
  164. DELIMITER //
  165. CREATE TRIGGER post_beli BEFORE UPDATE ON tbl_beli FOR EACH ROW
  166. BEGIN
  167. DECLARE x INT;
  168. DECLARE n INT;
  169. SET x = 0;
  170. SET n = NEW.jumlah;
  171. IF NEW.STATUS = 1 THEN
  172. ulang : LOOP
  173. SET x = x + 1;
  174. IF x > n THEN leave ulang; END IF;
  175. INSERT INTO tbl_gudang (id_barang,tanggal_kedaluarsa,id_cabang,id_beli,harga_beli,laba,ppn,harga_jual) VALUE (NEW.id_barang,NEW.tanggal_kedaluarsa,'GDG',NEW.id_beli,NEW.harga_beli,NEW.laba,NEW.ppn,NEW.harga_jual);
  176. END LOOP ulang;
  177.  
  178. END IF;
  179. END
  180. //
  181. DELIMITER ;
  182.  
  183.  
  184. ====================================================================
  185. DROP TRIGGER post_beli;
  186. DELIMITER //
  187. CREATE TRIGGER post_beli BEFORE UPDATE ON tbl_beli FOR EACH ROW
  188. BEGIN
  189. DECLARE x INT;
  190. DECLARE n INT;
  191. SET x = 0;
  192. SET n = NEW.jumlah;
  193. IF NEW.STATUS = 1 THEN
  194. ulang : LOOP
  195. SET x = x + 1;
  196. IF x > n THEN leave ulang; END IF;
  197. INSERT INTO tbl_stok (id_barang,id_beli,harga_beli,laba,ppn,harga_jual,id_cabang) VALUE (NEW.id_barang,NEW.id_beli,NEW.harga_beli,NEW.laba,NEW.ppn,NEW.harga_jual,NEW.id_cabang);
  198. END LOOP ulang;
  199.  
  200. END IF;
  201. END
  202. //
  203. DELIMITER ;
  204. ====================================================================
  205. DROP TRIGGER post_beli_nota;
  206. DELIMITER //
  207. CREATE TRIGGER post_beli_nota AFTER INSERT ON tbl_beli FOR EACH ROW
  208. BEGIN
  209. INSERT INTO UPDATE (STATUS) VALUE (NEW.id_barang,NEW.id_beli,NEW.harga_beli,NEW.laba,NEW.ppn,NEW.harga_jual,NEW.id_cabang);
  210. END
  211. //
  212. DELIMITER ;
  213. ====================================================================
  214. DROP TRIGGER post_jual;
  215. DELIMITER //
  216. CREATE TRIGGER post_jual AFTER INSERT ON tbl_jual FOR EACH ROW
  217. BEGIN
  218. DECLARE n INT;
  219. SET n = NEW.jumlah;
  220. UPDATE tbl_stok SET id_jual=NEW.id_jual
  221.      WHERE id_stok IN (
  222.          SELECT id_stok FROM (
  223.              SELECT id_stok FROM tbl_stok
  224.              WHERE
  225.              id_jual IS NULL AND id_barang = NEW.id_barang
  226.              ORDER BY id_stok ASC  
  227.              LIMIT 0, n
  228.          ) tmp
  229.      );
  230. END
  231. //
  232. DELIMITER ;
  233. ====================================================================
  234. DROP TRIGGER post_jual_del;
  235. DELIMITER //
  236. CREATE TRIGGER post_jual_del AFTER DELETE ON tbl_jual FOR EACH ROW
  237. BEGIN
  238. UPDATE tbl_stok SET id_jual=NULL WHERE id_jual = OLD.id_jual;
  239. END
  240. //
  241. DELIMITER ;
  242. ====================================================================
  243. DROP TRIGGER post_jual_upd;
  244. DELIMITER //
  245. CREATE TRIGGER post_jual_upd AFTER UPDATE ON tbl_jual FOR EACH ROW
  246. BEGIN
  247. DECLARE jumlah_lama INT;
  248. DECLARE jumlah_baru INT;
  249. DECLARE tambah INT;
  250. DECLARE kurang INT;
  251. SET jumlah_lama = OLD.jumlah;
  252. SET jumlah_baru = NEW.jumlah;
  253. SET tambah = jumlah_baru - jumlah_lama;
  254. SET kurang = jumlah_lama - jumlah_baru;
  255. IF jumlah_lama > jumlah_baru THEN
  256. UPDATE tbl_stok SET id_jual=NULL
  257.      WHERE id_stok IN (
  258.          SELECT id_stok FROM (
  259.              SELECT id_stok FROM tbl_stok
  260.              WHERE
  261.              id_jual = OLD.id_jual AND id_barang = OLD.id_barang
  262.              ORDER BY id_stok DESC  
  263.              LIMIT 0, kurang
  264.          ) tmp
  265.      );
  266. END IF;
  267. IF jumlah_lama < jumlah_baru THEN
  268. UPDATE tbl_stok SET id_jual=NEW.id_jual
  269.      WHERE id_stok IN (
  270.          SELECT id_stok FROM (
  271.              SELECT id_stok FROM tbl_stok
  272.              WHERE
  273.              id_jual IS NULL AND id_barang = OLD.id_barang
  274.              ORDER BY id_stok ASC  
  275.              LIMIT 0, tambah
  276.          ) tmp
  277.      );
  278.  
  279. END IF;
  280. END
  281. //
  282. DELIMITER ;
  283. ====================================================================
  284. DROP TRIGGER post_piutang_nota;
  285. DELIMITER //
  286. CREATE TRIGGER post_piutang_nota AFTER INSERT ON tbl_piutang_nota FOR EACH ROW
  287. BEGIN
  288. UPDATE tbl_piutang_bayar SET STATUS = 1 WHERE id_piutang_nota = NEW.id_piutang_nota;
  289. END
  290. //
  291. DELIMITER ;
  292. ====================================================================
  293. DROP TRIGGER post_hutang_nota;
  294. DELIMITER //
  295. CREATE TRIGGER post_hutang_nota AFTER INSERT ON tbl_hutang_nota FOR EACH ROW
  296. BEGIN
  297. UPDATE tbl_hutang_bayar SET STATUS = 1 WHERE id_hutang_nota = NEW.id_hutang_nota;
  298. END
  299. //
  300. DELIMITER ;
Advertisement
Add Comment
Please, Sign In to add comment