Contoh-Contoh TRIGGER dalam Mysql

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 ;