Trigger pada Microsoft SQL Server

Dalam pengembangan sistem basis data modern, integritas data dan otomatisasi proses bisnis menjadi aspek yang sangat penting. Salah satu fitur yang disediakan oleh Microsoft SQL Server untuk mendukung kebutuhan tersebut adalah Trigger. Trigger merupakan objek database yang akan dieksekusi secara otomatis ketika terjadi suatu peristiwa tertentu pada tabel atau view, seperti operasi INSERT, UPDATE, atau DELETE. Dengan trigger, administrator database maupun pengembang aplikasi dapat menerapkan aturan bisnis (business rules), audit data, logging aktivitas, validasi data, hingga sinkronisasi data secara otomatis. Pada Microsoft SQL Server, trigger sering digunakan untuk:

• Melakukan validasi data tambahan.
• Menolak perubahan data tertentu.
• Menyimpan riwayat perubahan data.
• Membuat audit aktivitas pengguna.
• Menjaga konsistensi antar tabel.
• Melakukan otomatisasi proses tertentu.

Konsep Dasar Trigger

Trigger adalah stored program yang secara otomatis dijalankan ketika suatu event terjadi pada database. Karakteristik Trigger:

• Trigger berjalan otomatis.
• Trigger tidak dapat dijalankan secara manual seperti Stored Procedure.
• Trigger terikat pada tabel atau view.
• Trigger dapat berjalan setelah atau sebelum perubahan data tertentu.
• Trigger dapat digunakan untuk menjaga integritas data.

Event yang Memicu Trigger

Pada SQL Server, trigger dapat dipicu oleh:

• DML (Data Manipulation Language)
• DDL (Data Definition Language)
• Logon

DML Trigger

DML Trigger adalah trigger yang berjalan ketika terjadi manipulasi data yaitu INSERT, UPDATE dan DELETE. DML Trigger dipakai untuk melakukan audit perubahan data, validasi transaksi, menolak penghapusan data tertentu, dan untuk melakukan sinkronisasi tabel.

DML Trigger dibedakan berdasarkan waktu dijalankannya sebagai berikut:

• INSTEAD OF Trigger, dijalankan sebelum operasi DML.
• AFTER Trigger, dijalankan setelah operasi DML.

DDL Trigger

DDL Trigger digunakan untuk memonitor perubahan struktur database, yaitu pada saat CREATE, ALTER, dan DROP obyek database. DDL Trigger dipakai untuk melakukan audit perubahan struktur database, mencegah DROP obyek database, dan mencatat perubahan schema.

LOGON Trigger

LOGON Trigger akan dijalankan ketika user melakukan login ke SQL Server. Logon Trigger dipakai untuk membatasi jam login, melakukan audit login pengguna dan membatasi jumlah koneksi.

Sintaks Pembuatan Trigger

CREATE TRIGGER nama_trigger
ON nama_obyek_database
[ INSTEAD OF / AFTER ] INSERT, UPDATE, DELETE
AS
BEGIN
    -- Isi trigger
END

Sintaks Perubahan Trigger

ALTER TRIGGER nama_trigger
ON nama_obyek_database
[ INSTEAD OF / AFTER ] INSERT, UPDATE, DELETE
AS
BEGIN
    -- Isi trigger
END

Sintaks Menonaktifkan Trigger

DISABLE TRIGGER nama_trigger ON nama_obyek_database

Sintaks Mengaktifkan Trigger

ENABLE TRIGGER nama_trigger ON nama_obyek_database

Sintaks Penghapusan Trigger

DROP TRIGGER nama_trigger

Variabel Default Trigger

Terdapat 2 variabel default trigger yaitu INSERTED dan DELETED

• INSERTED, digunakan untuk menyimpan data baru. Variabel ini dapat dipakai pada trigger INSERT atau UPDATE
• DELETED, digunakan untuk menyimpan data lama. Variabel ini dapat dipakai pada trigger DELETE atau UPDATE

Studi Kasus

Trigger DML Instead Of

Misalnya menggunakan tabel pada materi inner join sebelum melakukan insert ke tabel nilai_siswa dilakukan pengecekan ke tabel siswa. Jika belum terdapat di tabel siswa maka secara otomatis diinsert data siswa tersebut di tabel siswa.

Pembuatan Trigger

CREATE TRIGGER trg_cek_siswa
ON NILAI_SISWA
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    declare @li_exist int = 0

    -- Cek apakah ada siswa
    select @li_exist = count(*) from siswa s
    where exists (select 1 from inserted where id_siswa=s.id_siswa)

   IF @li_exist=0
     BEGIN
        insert into siswa(id_siswa)
        select id_siswa from inserted
     END
END

Perubahan Trigger

ALTER TRIGGER trg_cek_siswa
ON NILAI_SISWA
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    declare @li_exist int = 0
    -- Cek apakah ada siswa
    select @li_exist = count(*) from siswa s
    where exists (select 1 from inserted where id_siswa=s.id_siswa)

   IF @li_exist=0
     BEGIN
        insert into siswa(id_siswa)
        select id_siswa from inserted
     END

    INSERT INTO nilai_siswa
    select id_nilai, id_siswa, nilai from inserted
END

Pengujian Trigger

Sebelum melakukan pengujian, lakukan pengecekan data pada tabel nilai_siswa tabel siswa

select * from siswa
select * from nilai_siswa

Misalnya isi tabel Siswa sebagai berikut:

ID_Siswa	Nama_Siswa	ID_Kelas
4	Siswa 4	NULL
5	Siswa 5	NULL
6	Siswa 6	NULL

Dan isi tabel Nilai_Siswa sebagai berikut:

ID_Nilai	ID_Siswa	Nilai
5	5	85

Dari hasil query terlihat bahwa pada tabel nilai siswa hanya terdapat nilai ID Siswa 5

Pengujian dengan kondisi siswa pada data nilai siswa belum ada di tabel siswa

Pada pengujian ini akan dipakai data nilai siswa untuk ID_Siswa yang belum ada di tabel siswa dan tabel nilai_siswa, misalnya siswa dengan ID_Siswa 1

insert into nilai_siswa (id_nilai, id_siswa, nilai)
values(1,1,80)

Setelah dijalankan perintah DML untuk insert data tersebut lakukan lagi query sebagai berikut:

select * from siswa
select * from nilai_siswa

Maka isi tabel Siswa akan berubah sebagai berikut:

ID_Siswa	Nama_Siswa	ID_Kelas
1	NULL	NULL
4	Siswa 4	NULL
5	Siswa 5	NULL
6	Siswa 6	NULL

Dan isi tabel Nilai_Siswa sebagai berikut:

ID_Nilai	ID_Siswa	Nilai
1	1	80
5	5	85

Pengujian dengan kondisi siswa pada data nilai siswa sudah ada di tabel siswa

Pada pengujian ini akan dipakai data nilai siswa untuk ID_Siswa yang sudah ada di tabel siswa tetapi belum ada di tabel nilai_siswa, misalnya siswa dengan ID_Siswa 4

insert into nilai_siswa (id_nilai, id_siswa, nilai)
values(4,4,90)

Setelah dijalankan perintah DML untuk insert data tersebut lakukan lagi query sebagai berikut:

select * from siswa
select * from nilai_siswa

Maka isi tabel Siswa tidak berubah sebagai berikut:

ID_Siswa	Nama_Siswa	ID_Kelas
1	NULL	NULL
4	Siswa 4	NULL
5	Siswa 5	NULL
6	Siswa 6	NULL

Sedangkan isi tabel Nilai_Siswa berubah sebagai berikut:

ID_Nilai	ID_Siswa	Nilai
1	1	80
4	4	90
5	5	85

Kesimpulan

• Trigger berjalan otomatis ketika terjadi suatu kejadian di database.
• Trigger dapat menjaga integritas data.
• Trigger dapat mengotomatisasi proses.
• Terintegrasi langsung dengan database.

Materi slide dapat dilihat di sini