Sep 042009
 

Setelah kemaren kita belajar membuat trigger dan function di MySQL sekarang kita akan kembali pada Microsoft alias SQL Server, pada kali ini saya akan memberi contoh bagaimana membuat Trigger di SQL Server dan memanfaat trigger tersebut untuk membuat log pada table tersebut, ok mari kita mulai saja.

Pertama buat 4 Buat table pada SQL Server seperti pada source di bawah ini :

CREATE TABLE [Barang] (
 [kd_barang] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[nm_barang] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [stok] [int] NULL ,
 CONSTRAINT [PK_Barang] PRIMARY KEY  CLUSTERED
 (
 [kd_barang]
 )  ON [PRIMARY]
) ON [PRIMARY];

CREATE TABLE [pembelian] (
 [no_nota] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [tgl_beli] [smalldatetime] NULL ,
 [pembeli] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 CONSTRAINT [PK_pembelian] PRIMARY KEY  CLUSTERED
 (
 [no_nota]
 )  ON [PRIMARY]
) ON [PRIMARY];

CREATE TABLE [pembelian_detail] (
 [no_nota] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [kd_barang] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [jumlah] [int] NULL ,
 CONSTRAINT [PK_pembelian_detail] PRIMARY KEY  CLUSTERED
 (
 [no_nota],
 [kd_barang]
 )  ON [PRIMARY] ,
 CONSTRAINT [FK_pembelian_detail_Barang] FOREIGN KEY
 (
 [kd_barang]
 ) REFERENCES [Barang] (
 [kd_barang]
 ) ON UPDATE CASCADE ,
 CONSTRAINT [FK_pembelian_detail_pembelian] FOREIGN KEY
 (
 [no_nota]
 ) REFERENCES [pembelian] (
 [no_nota]
 ) ON DELETE CASCADE  ON UPDATE CASCADE
) ON [PRIMARY];

CREATE TABLE [pembelian_detail_log] (
 [nomor] [int] IDENTITY (1, 1) NOT NULL ,
 [no_nota] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [kd_barang] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [jumlah] [int] NULL ,
 [keterangan] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [tgl] [datetime] NULL ,
 CONSTRAINT [PK_pembelian_detail_log] PRIMARY KEY  CLUSTERED
 (
 [nomor]
 )  ON [PRIMARY]
) ON [PRIMARY]

Setelah kita menyiapkan tiga buat table langsung saja kita buat triggernya, maaf penjelasan tetentang trigger mungkin akan saya jelaskan lain waktu saja, atau anda silakan buat pertanyaan di sini.

Berikut adalah triggernya:

1. Untuk proses Insert

CREATE TRIGGER [tr_pembelian_detail_insert] ON dbo.pembelian_detail
FOR INSERT
AS
BEGIN
 declare @stok_awal int;
 declare @beli int;
 declare @stok_akhir int;
 declare @kd_barang varchar(5);
 set @kd_barang=(select kd_barang from inserted);
 set @beli=(select jumlah from inserted);
 set @stok_awal=(select stok from Barang where kd_barang=@kd_barang);
 set @stok_akhir=@stok_awal+@beli;
 update Barang set stok=@stok_akhir where kd_barang=@kd_barang;
 insert into pembelian_detail_log select *,'insert', GETDATE() from inserted;

END

2. Untuk Proses Delete

CREATE TRIGGER [tr_pembelian_detail_delete] ON dbo.pembelian_detail
FOR DELETE
AS
BEGIN
 declare @stok_delete int;
 declare @stok_akhir int;
 declare @stok_awal int;
 declare @kd_barang varchar(5);

 --Untuk delete
 set @stok_delete=(select jumlah from deleted);
 set @kd_barang=(select kd_barang from deleted);
 set @stok_awal=(select stok from Barang where kd_barang=@kd_barang);
 set @stok_akhir=@stok_awal-@stok_delete;
 update Barang set stok=@stok_akhir where kd_barang=@kd_barang;
 insert into pembelian_detail_log select *,'Delete', GETDATE() from deleted;

END

3. Untuk Proses Update

CREATE TRIGGER [tr_pembelian_detail_update] ON dbo.pembelian_detail
FOR UPDATE
AS
BEGIN
 declare @stok_delete int;
 declare @stok_insert int;
 declare @stok_akhir int;
 declare @stok_awal int;
 declare @kd_barang varchar(5);

 --Untuk delete
 set @stok_delete=(select jumlah from deleted);
 set @kd_barang=(select kd_barang from deleted);
 set @stok_awal=(select stok from Barang where kd_barang=@kd_barang);
 set @stok_akhir=@stok_awal-@stok_delete;
 update Barang set stok=@stok_akhir where kd_barang=@kd_barang;
 insert into pembelian_detail_log select *,'Update Lama', GETDATE() from deleted;

 --Untuk Insert
 set @stok_insert=(select jumlah from inserted);
 set @kd_barang=(select kd_barang from inserted);
 set @stok_awal=(select stok from Barang where kd_barang=@kd_barang);
 set @stok_akhir=@stok_awal+@stok_insert;
 update Barang set stok=@stok_akhir where kd_barang=@kd_barang;
 insert into pembelian_detail_log select *,'Update Baru', GETDATE() from inserted;

END

Ok Proses pembuata trigger kita sudah selesai, silakan explorasi dan kembangkan semoga membantu

Facebook Comments

  2 Responses to “Create log table with Triger in SQL Server”

  1. ammmaaaaazzzziiinnngggg..!!!!!

    thanks alo t bro…

    it helps so much on my project!

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

(required)

(required)