Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE
- [DEFINER = { USER | CURRENT_USER }]
- TRIGGER trigger_name
- trigger_time trigger_event
- ON tbl_name FOR EACH ROW
- trigger_body
- trigger_time: { BEFORE | AFTER }
- trigger_event: { INSERT | UPDATE | DELETE }
- DELIMITER //
- CREATE TRIGGER tbl_beli_subt_upd AFTER UPDATE ON tbl_beli
- FOR EACH ROW
- BEGIN
- IF NEW.harga_beli <=> OLD.harga_beli AND NEW.jumlah <=> OLD.jumlah THEN
- UPDATE tbl_beli SET sub_total = (NEW.harga_beli * NEW.jumlah) WHERE id_beli = OLD.id_beli;
- END IF;
- IF NEW.harga_beli = OLD.harga_beli AND NEW.jumlah <=> OLD.jumlah THEN
- UPDATE tbl_beli SET sub_total = (OLD.harga_beli * NEW.jumlah) WHERE id_beli = OLD.id_beli;
- END IF;
- IF NEW.harga_beli <=> OLD.harga_beli AND NEW.jumlah = OLD.jumlah THEN
- UPDATE tbl_beli SET sub_total = (NEW.harga_beli * OLD.jumlah) WHERE id_beli = OLD.id_beli;
- END IF;
- END;
- //
- DELIMITER;
- DELIMITER //
- CREATE TRIGGER upd_sbt AFTER INSERT ON tbl_beli
- FOR EACH ROW
- BEGIN
- SET @id_beli = NEW.id_beli;
- SET @harga_beli = NEW.harga_beli;
- SET @jumlah = NEW.jumlah;
- SET @sub_total = @harga_beli * @jumlah;
- UPDATE tbl_beli SET sub_total = @sub_total WHERE id_beli = @id_beli
- END;
- //
- DELIMITER;
- DROP TRIGGER trig_transaksi;
- DELIMITER //
- CREATE TRIGGER trig_transaksi after INSERT ON tbl_transaksi FOR EACH ROW
- BEGIN
- IF NEW.ktg_transaksi = 'Penjualan' THEN
- UPDATE tbl_barang SET stok = stok - NEW.jmlh WHERE kode = NEW.kode_brg;
- END IF;
- IF NEW.ktg_transaksi = 'Pembelian' THEN
- UPDATE tbl_barang SET stok = stok + NEW.jmlh, hrgbli = NEW.hrgbli WHERE kode = NEW.kode_brg;
- END IF;
- END
- //
- DELIMITER ;
- DROP TRIGGER trig_del_transaksi;
- DELIMITER //
- CREATE TRIGGER trig_del_transaksi after DELETE ON tbl_transaksi FOR EACH ROW
- BEGIN
- IF OLD.ktg_transaksi = 'Penjualan' THEN
- UPDATE tbl_barang SET stok = stok + OLD.jmlh WHERE kode = OLD.kode_brg;
- END IF;
- IF OLD.ktg_transaksi = 'Pembelian' THEN
- UPDATE tbl_barang SET stok = stok - OLD.jmlh WHERE kode = OLD.kode_brg;
- END IF;
- END
- //
- DELIMITER ;
- DROP TRIGGER trig_upd_transaksi;
- DELIMITER //
- CREATE TRIGGER trig_upd_transaksi after UPDATE ON tbl_transaksi FOR EACH ROW
- BEGIN
- IF NEW.ktg_transaksi = 'Penjualan' THEN
- UPDATE tbl_barang SET stok = stok + OLD.jmlh - NEW.jmlh WHERE kode = NEW.kode_brg;
- END IF;
- IF NEW.ktg_transaksi = 'Pembelian' THEN
- UPDATE tbl_barang SET stok = stok - OLD.jmlh + NEW.jmlh, hrgbli = NEW.hrgbli WHERE kode = NEW.kode_brg;
- END IF;
- END
- //
- DELIMITER ;
- DROP TRIGGER trig_kwi;
- DELIMITER //
- CREATE TRIGGER trig_kwi after INSERT ON tbl_kwi FOR EACH ROW
- BEGIN
- UPDATE tbl_kwi_det SET tgl_byr = NEW.tgl_byr WHERE kwitansi = NEW.kwitansi;
- END
- //
- DELIMITER ;
- DROP TRIGGER trig_kwi_lun;
- DELIMITER //
- CREATE TRIGGER trig_kwi_lun after UPDATE ON tbl_kwi_det FOR EACH ROW
- BEGIN
- UPDATE tbl_nota SET status_pembayaran = 'Lunas', tgl_byr = NEW.tgl_byr WHERE nota = OLD.nota;
- END
- //
- DELIMITER ;
- DELIMITER //
- CREATE TRIGGER trig_transaksi after INSERT ON tbl_beli FOR EACH ROW
- BEGIN
- INSERT INTO tbl_gudang (id_barang) VALUE (NEW.id_barang);
- END
- //
- DELIMITER ;
- DELIMITER //
- CREATE TRIGGER trig_transaksi after INSERT ON tbl_beli FOR EACH ROW
- BEGIN
- DECLARE pencacah INT;
- SET pencacah = 0;
- SELECT pencacah;
- ulang : LOOP
- SET pencacah = pencacah + 1;
- IF pencacah > 10 THEN leave ulang; END IF;
- END LOOP ulang;
- SELECT pencacah;
- END
- //
- DELIMITER ;
- DELIMITER //
- CREATE TRIGGER trig_transaksi BEFORE UPDATE ON tbl_beli FOR EACH ROW
- BEGIN
- DECLARE x INT;
- DECLARE n INT;
- SET x = 0;
- SET n = NEW.jumlah;
- ulang : LOOP
- SET x = x + 1;
- IF x > n THEN leave ulang; END IF;
- INSERT INTO tbl_gudang (id_barang) VALUE (NEW.id_barang);
- END LOOP ulang;
- END
- //
- DELIMITER ;
- DELIMITER //
- CREATE TRIGGER post_beli BEFORE UPDATE ON tbl_beli FOR EACH ROW
- BEGIN
- DECLARE x INT;
- DECLARE n INT;
- SET x = 0;
- SET n = NEW.jumlah;
- IF NEW.STATUS = 1 THEN
- ulang : LOOP
- SET x = x + 1;
- IF x > n THEN leave ulang; END IF;
- 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);
- END LOOP ulang;
- END IF;
- END
- //
- DELIMITER ;
- ====================================================================
- DROP TRIGGER post_beli;
- DELIMITER //
- CREATE TRIGGER post_beli BEFORE UPDATE ON tbl_beli FOR EACH ROW
- BEGIN
- DECLARE x INT;
- DECLARE n INT;
- SET x = 0;
- SET n = NEW.jumlah;
- IF NEW.STATUS = 1 THEN
- ulang : LOOP
- SET x = x + 1;
- IF x > n THEN leave ulang; END IF;
- 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);
- END LOOP ulang;
- END IF;
- END
- //
- DELIMITER ;
- ====================================================================
- DROP TRIGGER post_beli_nota;
- DELIMITER //
- CREATE TRIGGER post_beli_nota AFTER INSERT ON tbl_beli FOR EACH ROW
- BEGIN
- INSERT INTO UPDATE (STATUS) VALUE (NEW.id_barang,NEW.id_beli,NEW.harga_beli,NEW.laba,NEW.ppn,NEW.harga_jual,NEW.id_cabang);
- END
- //
- DELIMITER ;
- ====================================================================
- DROP TRIGGER post_jual;
- DELIMITER //
- CREATE TRIGGER post_jual AFTER INSERT ON tbl_jual FOR EACH ROW
- BEGIN
- DECLARE n INT;
- SET n = NEW.jumlah;
- UPDATE tbl_stok SET id_jual=NEW.id_jual
- WHERE id_stok IN (
- SELECT id_stok FROM (
- SELECT id_stok FROM tbl_stok
- WHERE
- id_jual IS NULL AND id_barang = NEW.id_barang
- ORDER BY id_stok ASC
- LIMIT 0, n
- ) tmp
- );
- END
- //
- DELIMITER ;
- ====================================================================
- DROP TRIGGER post_jual_del;
- DELIMITER //
- CREATE TRIGGER post_jual_del AFTER DELETE ON tbl_jual FOR EACH ROW
- BEGIN
- UPDATE tbl_stok SET id_jual=NULL WHERE id_jual = OLD.id_jual;
- END
- //
- DELIMITER ;
- ====================================================================
- DROP TRIGGER post_jual_upd;
- DELIMITER //
- CREATE TRIGGER post_jual_upd AFTER UPDATE ON tbl_jual FOR EACH ROW
- BEGIN
- DECLARE jumlah_lama INT;
- DECLARE jumlah_baru INT;
- DECLARE tambah INT;
- DECLARE kurang INT;
- SET jumlah_lama = OLD.jumlah;
- SET jumlah_baru = NEW.jumlah;
- SET tambah = jumlah_baru - jumlah_lama;
- SET kurang = jumlah_lama - jumlah_baru;
- IF jumlah_lama > jumlah_baru THEN
- UPDATE tbl_stok SET id_jual=NULL
- WHERE id_stok IN (
- SELECT id_stok FROM (
- SELECT id_stok FROM tbl_stok
- WHERE
- id_jual = OLD.id_jual AND id_barang = OLD.id_barang
- ORDER BY id_stok DESC
- LIMIT 0, kurang
- ) tmp
- );
- END IF;
- IF jumlah_lama < jumlah_baru THEN
- UPDATE tbl_stok SET id_jual=NEW.id_jual
- WHERE id_stok IN (
- SELECT id_stok FROM (
- SELECT id_stok FROM tbl_stok
- WHERE
- id_jual IS NULL AND id_barang = OLD.id_barang
- ORDER BY id_stok ASC
- LIMIT 0, tambah
- ) tmp
- );
- END IF;
- END
- //
- DELIMITER ;
- ====================================================================
- DROP TRIGGER post_piutang_nota;
- DELIMITER //
- CREATE TRIGGER post_piutang_nota AFTER INSERT ON tbl_piutang_nota FOR EACH ROW
- BEGIN
- UPDATE tbl_piutang_bayar SET STATUS = 1 WHERE id_piutang_nota = NEW.id_piutang_nota;
- END
- //
- DELIMITER ;
- ====================================================================
- DROP TRIGGER post_hutang_nota;
- DELIMITER //
- CREATE TRIGGER post_hutang_nota AFTER INSERT ON tbl_hutang_nota FOR EACH ROW
- BEGIN
- UPDATE tbl_hutang_bayar SET STATUS = 1 WHERE id_hutang_nota = NEW.id_hutang_nota;
- END
- //
- DELIMITER ;
Advertisement
Add Comment
Please, Sign In to add comment