Tutorial SQL Server - 6. Trigger

Trigger adalah sejenis stored procedure yang dijalankan secara otomatis ketika terjadi event dalam sebuah database. Sama seperti objek database lainnya, trigger bisa dibuat dengan statement CREATE TRIGGER, diubah dengan statement ALTER TRIGGER dan dihapus dengan statement DROP TRIGGER. Trigger bisa dikelompokkan menjadi 3 jenis yaitu :

1. DML Trigger
Adalah trigger yang terjadi ketika ada event DML (Data Manipulation Language) yakni Insert, Update, Delete
2. DDL Trigger
Adalah trigger yang terjadi ketika ada event DDL (Data Definition Langguage) yakni Create, Alter, Drop, Grant, Deny, Revoke, Update Statistics
3. Logon Trigger
Adalah trigger yang terjadi ketika ada event Logon

Referensi-referensi trigger dalam MSDN :

1. Create Trigger
2. Alter Trigger
3. Drop Trigger

Pada kesempatan ini, kita hanya membahas tentang DML Trigger. Berikut adalah contoh syntax trigger beserta komentar sebagai penjelas syntax tersebut.

NB : Sebelum dilanjutkan, pastikan database inventory1 sudah dibuat dan data-data sudah diisi.

USE inventory1;
GO

-- membuat trigger ketika user menyimpan data ditabel customer
CREATE TRIGGER insert_cust ON t_cust
AFTER INSERT
AS
BEGIN
 DECLARE @kd_cust VARCHAR(20), @nm_cust VARCHAR(100)
 SELECT @kd_cust = kd_cust, @nm_cust = nm_cust FROM inserted
 INSERT INTO t_log (waktu_log,ket_log) 
 VALUES (GETDATE(),'User memasukan data customer baru dengan kode "' + @kd_cust + '" dan nama "' + @nm_cust + '"')
END
GO

-- membuat trigger ketika user mengubah data ditabel customer
CREATE TRIGGER update_cust ON t_cust
AFTER UPDATE
AS
BEGIN
 DECLARE @kd_cust VARCHAR(20), @nm_cust_lama VARCHAR(100), @nm_cust_baru VARCHAR(100)
 SELECT @kd_cust = kd_cust FROM deleted
 SELECT @nm_cust_lama = nm_cust FROM deleted
 SELECT @nm_cust_baru = nm_cust FROM inserted
 INSERT INTO t_log (waktu_log,ket_log) 
 VALUES (GETDATE(),'User mengubah nama customer "' + @kd_cust + '" dari "' + @nm_cust_lama + '" menjadi "' + @nm_cust_baru + '"')
END
GO

-- membuat trigger ketika user menghapus data ditabel customer
CREATE TRIGGER delete_cust ON t_cust
AFTER DELETE
AS
BEGIN
 DECLARE @kd_cust VARCHAR(20) 
 SELECT @kd_cust = kd_cust FROM deleted
 INSERT INTO t_log (waktu_log,ket_log) 
 VALUES (GETDATE(),'User menghapus customer "' + @kd_cust + '"')
END
GO

-- mencoba apakah trigger berjalan atau tidak
INSERT INTO t_cust VALUES ('JY01','TOKO JAYA',0);
SELECT * FROM t_log;
UPDATE t_cust SET nm_cust='TOKO JAYA WIJAYA' WHERE kd_cust='JY01';
SELECT * FROM t_log;
DELETE FROM t_cust WHERE kd_cust='JY01';
SELECT * FROM t_log;

-- untuk mengubah trigger, gunakan keyword ALTER sebagai pengganti keyword CREATE pada saat membuat trigger

-- menghapus trigger
DROP TRIGGER insert_cust;
DROP TRIGGER update_cust;
DROP TRIGGER delete_cust;
GO

-- membuat trigger multi event dalam 1 tabel
CREATE TRIGGER all_event_on_cust ON t_cust
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
 IF EXISTS (SELECT * FROM deleted)
 BEGIN
  IF EXISTS (SELECT * FROM inserted)
  BEGIN -- artinya event update
   DECLARE @kd_cust_upd VARCHAR(20), @nm_cust_lama VARCHAR(100), @nm_cust_baru VARCHAR(100)
   SELECT @kd_cust_upd = kd_cust FROM deleted
   SELECT @nm_cust_lama = nm_cust FROM deleted
   SELECT @nm_cust_baru = nm_cust FROM inserted
   PRINT 'User mengubah nama customer "' + @kd_cust_upd + '" dari "' + @nm_cust_lama + '" menjadi "' + @nm_cust_baru + '"'
  END
  ELSE
  BEGIN -- artinya event delete
   DECLARE @kd_cust_del VARCHAR(20) 
   SELECT @kd_cust_del = kd_cust FROM deleted
   PRINT 'User menghapus customer "' + @kd_cust_del + '"'
  END
 END
 ELSE
 BEGIN -- artinya event insert
  DECLARE @kd_cust_ins VARCHAR(20), @nm_cust_ins VARCHAR(100)
  SELECT @kd_cust_ins = kd_cust, @nm_cust_ins = nm_cust FROM inserted
  PRINT 'User memasukan data customer baru dengan kode "' + @kd_cust_ins + '" dan nama "' + @nm_cust_ins + '"'
 END
END
GO

-- membuat trigger untuk melakukan perhitungan
CREATE TRIGGER all_event_on_jual_header ON t_jual_h
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
 DECLARE @kd_cust VARCHAR(20), @ttl_faktur MONEY
 IF EXISTS (SELECT * FROM deleted)
 BEGIN
  IF EXISTS (SELECT * FROM inserted)
  BEGIN -- artinya event update
   SELECT @kd_cust = kd_cust, @ttl_faktur = ttl_jual FROM deleted
   DECLARE @ttl_faktur_baru MONEY
   SELECT @ttl_faktur_baru = ttl_jual FROM inserted
   UPDATE t_cust SET sisa_piutang=sisa_piutang-@ttl_faktur+@ttl_faktur_baru WHERE kd_cust=@kd_cust
  END
  ELSE
  BEGIN -- artinya event delete
   SELECT @kd_cust = kd_cust, @ttl_faktur = ttl_jual FROM deleted
   UPDATE t_cust SET sisa_piutang=sisa_piutang-@ttl_faktur WHERE kd_cust=@kd_cust
  END
 END
 ELSE
 BEGIN -- artinya event insert
  SELECT @kd_cust = kd_cust, @ttl_faktur = ttl_jual FROM inserted
  UPDATE t_cust SET sisa_piutang=sisa_piutang+@ttl_faktur WHERE kd_cust=@kd_cust
 END
END
GO

-- menguji event insert pada trigger all_event_on_jual_header
SELECT * FROM t_cust WHERE kd_cust='B001';
SELECT * FROM t_jual_h WHERE kd_cust='B001';
INSERT INTO t_jual_h (kd_jual,tgl_jual,kd_cust,ttl_jual,tgl_jt) VALUES ('JL01','2013-07-30','B001',100000,'2013-07-30');
SELECT * FROM t_cust WHERE kd_cust='B001';
SELECT * FROM t_jual_h WHERE kd_cust='B001';

-- menguji event update pada trigger all_event_on_jual_header
SELECT * FROM t_cust WHERE kd_cust='B001';
SELECT * FROM t_jual_h WHERE kd_cust='B001';
UPDATE t_jual_h SET ttl_jual = 30000 WHERE kd_jual='JL01';
SELECT * FROM t_cust WHERE kd_cust='B001';
SELECT * FROM t_jual_h WHERE kd_cust='B001';

-- menguji event delete pada trigger all_event_on_jual_header
SELECT * FROM t_cust WHERE kd_cust='B001';
SELECT * FROM t_jual_h WHERE kd_cust='B001';
DELETE t_jual_h WHERE kd_jual='JL01';
SELECT * FROM t_cust WHERE kd_cust='B001';
SELECT * FROM t_jual_h WHERE kd_cust='B001';

NB : Ketika menginput banyak data dengan sekali insert, maka trigger akan terjadi 1 kali saja dan data yang akan terambil dari inserted tabel adalah data pertama saja.