SonySe

Membuat Audit Delete Record dengan trigger di SQL 2005

Tulisan ini sebenarnya merupakan pemenuhan janji saya kepada salah satu peserta training yang ingin mengetahui tentang konsep Auditing Record Log di sebuah database MS SQL Server 2005 (any edition). Saya mohon maaf kepada Pak Indra yang sudah menanti cukup lama untuk tulisan ini .

Kita mulai dari cara yang sederhana yaitu dengan membuat sebuah database / menggunakan sebuah database yang sudah ada. Beberapa prasyarat awal untuk mengetahui lebih detail konsep ini bisa dimulai pemahaman tentang Trigger. Kenaapa menggunakan trigger ? Sebagai sebuah object database yang sebenarnya 'berdiri sendiri', trigger mempunyai kemampuan utk meng-antisipasi sebuah aksi yang dilakukan ke database / table dengan merespon balik berupa reaksi terhadap table itu sendiri (tempat trigger berada) / kepada object (table,view dll) yang juga terdapat di dalam database itu. Fungsi trigger sebenarnya mirip Check constraint tetapi  trigger mempunya fungsionalitas yang lebih luas dibandingkan dengan Check Constraint.

Mari kita mulai pembahasan topik ini dengan membuat sebuah skenario berikut : Sebuah aplikasi berbasis windows based client dan database MS SQL 2005 sebagai backend-nya sering mendapatkan sebuah kasus dimana database-nya / tabel / record-nya tiba-tiba data-nya menghilang, sebagai seorang DBA tentunya kita harus mempunyai cara untuk mengetahui siapa, kapan, dan lewat mana data tersebut 'terhapus' entah itu sengaja atau tidak sengaja. Beberapa prasyarat untuk membuatnya adalah sebagai berikut :

1. Tentukan terlebih dahulu mana tabel yang akan di audit, karena semakin banyak trigger yang harus di aktifkan di sebuah database dapat menurunkan performance dari database tersebut.
2. Buat database dan table kosong yang baru di SQL 2005 dengan menggunakan SQL Management Studio nama-nya terserah anda : "testingAudit" dengan menggunakan konfigurasi standard :

   create database testingAudit
   go
   create table testDelete
   (
   id_record varchar(5),
   Deskripsi varchar(20)
   )
   go
   
   'insert data dummy ke table testDelete, jalankan script :

   insert into testDelete values ('ABC','testing 123')
   insert into testDelete values ('BCD','testing 124')
   insert into testDelete values ('CDE','testing 125')
   insert into testDelete values ('EFG','testing 126')
   insert into testDelete values ('FGH','testing 126')
   insert into testDelete values ('GHI','testing 127')
   insert into testDelete values ('HIJ','testing 128')
   insert into testDelete values ('IJK','testing 129')
   insert into testDelete values ('JKL','testing 130')
   insert into testDelete values ('KLM','testing 131')
   insert into testDelete values ('LMN','testing 132')
   insert into testDelete values ('MNO','testing 133')

3. Setelah kita tentukan tabel mana yang akan di audit yaitu testdelete table, buatlah sebuah tabel yang dimanfaatkan sebagai audit log table yang di dalamnya terdapat beberapa informasi seperti : deleted_date_hour_minute,who_deleted,id_record_deleted,application. Berikut contoh script untuk membuat tabel-nya :
   use testingAudit
   go
   create table log_record
   (
   dateDeleted datetime,
   personWho varchar(30),
   Id_record varchar(10)
   )
4. Setelah table log dibuat, tahap berikut-nya adalah membuat trigger on delete di dalam table testDelete
   Setiap perintah transaction (insert,update,delete) statement di table MS SQL Server pasti selalu muncul beberapa table sementara yang hanya muncul disaat eksekusi trigger. Table-table itu diantaranya adalah sebagai berikut :
   -   inserted (muncul pada saat terjadi proses transaksi perintah insert terhadap sebuah tabel)
   -   deleted (muncul pada saat terjadi proses transaksi perintah delete terhadap sebuah tabel)
   -   Sedangkan untuk transaksi Update , proses yang terjadi adalah deleted yang pertama kali di kerjakan baru setelah itu Inserted.

   Gambaran script untuk pembuatan trigger-nya adalah sebagai berikut :
   create trigger tr_test on testdelete
   for delete
   as
   declare @date1 datetime,@person varchar(30) , @id varchar(10)
   set @date1 = getdate()
   set @person = suser_sname()
   select @id = id_record from deleted
   insert into log_record values (@date1,@person,@id)

5. Pastikan trigger tr_test sudah terdapat dalam tabel testDelete, dengan menggunaan SSMS (SQL Server Management Studio).
6. Coba jalankan perintah delete terhadap 1 record di tabel testDelete kemudian cek apakah log_record table telah terisi siapa, kapan dan record mana yang terhapus.
7. Tolong dipastikan juga bahwa user yang authorized delete record di testDelete bisa melakukan perintah insert di table log_record.
8. Selamat mencoba.

Feel free your comment,

Sony

Share this post: | | | |