Seringkali dibutuhkan program yang dapat berjalan sendiri (dijalankan oleh system) sehingga tidak perlu dieksekusi oleh user. Program tersebut biasanya dibuat menggunakan timer yang diatur waktu untuk eksekusi sebuah program. Pada database SQL Server, untuk menjalan program secara otomatis (dieksekusi oleh system) dapat dibuat dengan mudah karena ada fasilitas Trigger. Dengan Trigger ini, program dapat dijalankan karena ada pemicu ketika insert/update/delete terhadap sebuah table. Program yang akan dijalankan otomatis dapat disimpan di Trigger berjenis Insert jika pemicunya adalah Insert, disimpan di Trigger berjenis Delete jika pemicunya adalah Delete, dan disimpan di Trigger berjenis Update jika pemicunya adalah Delete.
Trigger adalah batch/sekumpulan perintah yang secara otomatis dikerjakan ketika terjadi penyisipan (insert), pengubahan (update) atau penghapusan data (delete) pada sebuah tabel.
Trigger :
- Program seperti Store Procedure atau Function yang dijalankan oleh system
- Program ini menempel pada sebuah Tabel, berbeda dengan SP dan Function menempel pada Database
- Dijalankan oleh system jika ada pemicu yaitu Insert terhadap table, Delete ataupun Update.
Sintaks penulisan :
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ delete ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
[ { IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ]
[ ...n ]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
{ comparison_operator } column_bitmask [ ...n ]
} ]
sql_statement [ ...n ]
}
}
ON { table | view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ delete ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
[ { IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ]
[ ...n ]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
{ comparison_operator } column_bitmask [ ...n ]
} ]
sql_statement [ ...n ]
}
}
Sebuah Contoh kita memiliki table tbPegawai yang berisi data NIK, NAMA dan TGLLAHIR. Pada table ini akan dibuat sebuah Trigger Insert yang akan mencatat data apa yang sedang diinsert di table tbPegawai tersebut. Setelah mengetahui data apa yang diinsert, maka pada TbLog akan dicatat log/historinya, Tgl berapa diinsertnya, Siapa yang menginsertnya dan Apa yang diinsertnya. TrgInsert_tbPegawai, adalah trigger yang aktif bila ada insert data pada table tbPegawai. Isi programnya adalah menuliskan Tgl berapa diinsertnya, Siapa yang menginsertnya dan Apa yang diinsertnya pada table tbLog. Ini programnya :
CREATE TRIGGER [dbo].[trgInsert_tbPegawai]
ON [dbo].[TBPEGAWAI]
FOR INSERT
AS
BEGIN
declare @nik as char(3)
declare @nama as varchar(50)
declare @tgllahir as datetime
--mengetahui data apa yang sedang diinsert
select @nik=NIK,@nama=NAMA, @tgllahir=TGLLAHIR from inserted
insert into TBLOG(TGL, PENGGUNA, KETERANGAN)
values(getdate(), LEFT(suser_Sname(),20),
@NIK+','+@NAMA+','+CONVERT(VARCHAR,@tgllahir))
END
Untuk membuat trigger caranya sbb :
A. Tabel Inserted dan Deleted
Saat sebuah trigger Insert dijalankan, maka akan diciptakan sebuah table “Inserted”. Tabel Inserted ini strukturnya akan sama dengan table induknya (dalam hal ini TbPegawai). Isi table Inserted ini adalah data yang sedang diinsertkan di table tbPegawai. Dengan demikian, kita dapat mengetahui data apa yang sedang diinsertkan ke dalam tbPegawai melalui table ini.
Untuk melihat apa yang di table Inserted dapat dilakukan perintah Select * from Inserted. Perintah ini harus ada didalam trigger Insert. Jika ingin mengetahui apa yang diinsert di table tbPegawai dapat dilakukan perintah sbb :
--mengetahui data apa yang sedang diinsert
select NIK, NAMA, TGLLAHIR from inserted
Jika kita ingin mengambil data dari tabel Inserted dan hasilnya ditampung pada variabel, perintahnya sbb :
declare @nik as char(3)
declare @nama as varchar(50)
declare @tgllahir as datetime
--mengetahui data apa yang sedang diinsert
select @nik=NIK,@nama=NAMA, @tgllahir=TGLLAHIR from inserted
Variabel @nik akan berisi NIK yang sedang diinsert, @nama berisi NAMA yang sedang diinsert, @tgllahir berisi TGLLAHIR yang sedang diinsert pada table tbPegawai.
Setelah data ditampung di variable-variabel tersebut, langkah selanjutnya adalah menuliskan ke table tbLog sebagai berikut :
insert into TBLOG(TGL, PENGGUNA, KETERANGAN)
values(getdate(), LEFT(suser_Sname(),20),
@NIK+','+@NAMA+','+CONVERT(VARCHAR,@tgllahir))
Nilai getdate() adalah fungsi untuk mengetahui tanggal dan jam system computer saat terjadi insert data di tbPegawai, Suser_Sname() adalah pengguna yang menginsert data di tbpegawai.
Berikut contoh untuk mengetahui apakah trgInsert_tbPegawai jalan atau tidak yaitu dengan menginsert data pada tbPegawai. Kemudian lihat apakah data masuk ke tbPegawai dan perhatikan apakah tbLog mencatat lognya.
INSERT INTO [DBTRIGGER].[dbo].[TBPEGAWAI]
([NIK]
,[NAMA]
,[TGLLAHIR])
VALUES
('400'
,'Tuti'
,'1980-12-12')
GO
select * from TBPEGAWAI
Go
select * from tblog
go
1. Buat DBhutang
2. Buat Tabel
a. Pemasok (IDPemasok, Nama, Hutang),
b. Beli (IDBeli, IDPemasok, TotalBeli),
c. Bayar (IDBayar, IDPemasok, TotalBayar)
3. Buat Relasi antara Pemasok dengan Beli dan Pemasok dengan Bayar
4. Buat TrgInsertBeli (menempel pada table Beli) yang fungsinya menambah Hutang pada table Pemasok.
dan TrgInsertBayar (menempel pada table Bayar) yang fungsinya mengurangi Hutang pada table PemasokCREATE TRIGGER TrgInsertBeli
ON dbo.TBBeli
AFTER insert
AS
BEGIN
declare @IDPemasok varchar(50)
declare @Totalbeli money
select @IDPemasok =IDPemasok, @Totalbeli = TotalBeli
from inserted
update dbo.TbPemasok
set Hutang = Hutang + @Totalbeli
where IDPemasok = @IDPemasok
END
GO
insert into dbo.TbPemasok (IDPemasok, Nama, Hutang)
values ('A-001', 'A', 5000)
--Data Hutang Pelasok sebelumnya
Select * from dbo.TbPemasok
--Insert Pembelian
Insert Into dbo.TBBeli (IDBeli, IDPemasok, Totalbeli)
values('B-001', 'A-001', 10000)
Insert Into dbo.TBBeli (IDBeli, IDPemasok, Totalbeli)
values('B-002', 'A-001', 10000)
--Data Hutang Pemasok sesudahnya
Select * from dbo.TBBeli
Select * from dbo.TbPemasok
CREATE TRIGGER TrgInsertBayar
ON dbo.TbBayar
AFTER insert
AS
BEGIN
declare @IDPemasok varchar(50)
declare @TotalBayar money
select @IDPemasok=IDPemasok, @TotalBayar=TotalBayar
from inserted
update dbo.TbPemasok
set Hutang = Hutang - @TotalBayar
where IDPemasok=@IDPemasok
END
GO
--Data Hutang Pemasok sebelumnya
Select * from dbo.TbPemasok
--Insert Pembayaran
Insert Into dbo.TbBayar (IDBayar, IDPemasok, TotalBayar)
values ('BY-002', 'A-001', 6000)
--Data Hutang Pemasok sesudahnya
Select * from dbo.TbBayar
Select * from dbo.TbPemasok
SINKRONISASI STOK BARANG
1. Table barang(Kode, Nama, stok)
2. Tabel jual (noFJ, tgl FJ, kode, qty, harga)
Buat trigger untuk sinkronisasi
1. Saaat ada insert data pada tabel jual maka stok barang di tbbrg akan berkurang
2. Update & delete
CREATE TRIGGER TrInsertBrg
ON dbo.tbJual
AFTER INSERT
AS
BEGIN
Declare @QTY INT, @KODE NVARCHAR(15)
SELECT @Qty = Qty, @Kode = Kode
FROM INSERTED
update dbo.tbBarang
set Stok = Stok - @Qty
where Kode = @Kode
END
INSERT INTO [SIPP].[dbo].[tbBarang]
([Kode]
,[Nama]
,[Stok])
VALUES
('B-001', 'meja', 100)
INSERT INTO [SIPP].[dbo].[tbJual]
([NoFJ]
,[TglFJ]
,[Kode]
,[Qty]
,[Harga])
VALUES
('FJ-0004', getdate(), 'B-001', 30, 10000)
select * from dbo.tbJual
select * from dbo.tbBarang
CREATE TRIGGER TrupdateBrg
ON dbo.tbJual
AFTER UPDATE
AS
BEGIN
Declare @KODE NVARCHAR(15), @QTY int, @QTYLAMA int
SELECT @Qty = Qty, @Kode = Kode
FROM INSERTED
select @QTYLama = Qty
from deleted
update dbo.tbBarang
set Stok = Stok + @QtyLama - @Qty
where Kode = @Kode
END
INSERT INTO [SIPP].[dbo].[tbBarang]
([Kode]
,[Nama]
,[Stok])
VALUES
('B-001', 'meja', 100)
INSERT INTO [SIPP].[dbo].[tbJual]
([NoFJ]
,[TglFJ]
,[Kode]
,[Qty]
,[Harga])
VALUES
('FJ-0004', getdate(), 'B-001', 30, 10000)
UPDATE [SIPP].[dbo].[tbJual]
SET [Qty] = 5
WHERE NoFJ = 'FJ-0004'
GO
select * from dbo.tbJual
select * from dbo.tbBarang
CREATE TRIGGER TrDeleteBrg
ON dbo.tbJual
AFTER DELETE
AS
BEGIN
Declare @QTY INT, @KODE NVARCHAR(15)
SELECT @Qty = Qty, @Kode = Kode
FROM DELETED
update dbo.tbBarang
set Stok = Stok + @Qty
where Kode = @Kode
END
DELETE FROM dbo.tbJual
WHERE NoFJ = 'FJ-0004'
select * from dbo.tbJual
select * from dbo.tbBarang
Update tbbrg
Set stok = stok - @Qty
Where kode = @kode
Kalau salah insert jmlh barang maka dibalikan lg ke sblmny dl
Update …
Set stok + @qtylama-@qtybaru
Where…
Modifikasi tbbrg mjd (kode, nama, stok, jual01…jual12, qty01…qty12)
Buat trjual
1. Update jual 01 dgn @qty*@harga saat ada insert/update pada tabel jual bln januari
2. Update qty01 dgn @qty saat ada insert/update pd tabel bulan januari
CREATE TRIGGER TrJual
ON dbo.tbJual
AFTER insert
AS
BEGIN
Declare @KODE NVARCHAR(15), @QTY int, @Harga money, @TglFJ datetime
SELECT @Qty = Qty, @Kode = Kode, @Harga = Harga, @TglFJ = TglFJ
FROM INSERTED
if(datepart(mm, @TglFJ )=1)
begin
update dbo.tbBarang
set jual01 = jual01 + @Qty*@Harga, qty01 = qty01 + @Qty
where Kode = @Kode
end
if(datepart(mm, @TglFJ )=2)
begin
update dbo.tbBarang
set jual02 = jual02 + @Qty*@Harga, qty02 = qty02 + @Qty
where Kode = @Kode
end
if(datepart(mm, @TglFJ )=3)
begin
update dbo.tbBarang
set jual03 = jual03+ @Qty*@Harga, qty03 = qty03 + @Qty
where Kode = @Kode
end
if(datepart(mm, @TglFJ )=4)
begin
update dbo.tbBarang
set jual04 = jual04 + @Qty*@Harga, qty04 = qty04 + @Qty
where Kode = @Kode
end
if(datepart(mm, @TglFJ )=5)
begin
update dbo.tbBarang
set jual05 = jual05 + @Qty*@Harga, qty05 = qty05 + @Qty
where Kode = @Kode
end
if(datepart(mm, @TglFJ )=6)
begin
update dbo.tbBarang
set jual06 = jual06 + @Qty*@Harga, qty06 = qty06 + @Qty
where Kode = @Kode
end
if(datepart(mm, @TglFJ )=7)
begin
update dbo.tbBarang
set jual07 = jual07 + @Qty*@Harga, qty07 = qty07 + @Qty
where Kode = @Kode
end
if(datepart(mm, @TglFJ )=8)
begin
update dbo.tbBarang
set jual08 = jual08 + @Qty*@Harga, qty08 = qty08 + @Qty
where Kode = @Kode
end
if(datepart(mm, @TglFJ )=9)
begin
update dbo.tbBarang
set jual09 = jual09 + @Qty*@Harga, qty09 = qty09 + @Qty
where Kode = @Kode
end
if(datepart(mm, @TglFJ )=10)
begin
update dbo.tbBarang
set jual10 = jual10 + @Qty*@Harga, qty10 = qty10 + @Qty
where Kode = @Kode
end
if(datepart(mm, @TglFJ )=11)
begin
update dbo.tbBarang
set jual11 = jual11 + @Qty*@Harga, qty11 = qty11 + @Qty
where Kode = @Kode
end
if(datepart(mm, @TglFJ )=12)
begin
update dbo.tbBarang
set jual12 = jual12 + @Qty*@Harga, qty12 = qty12 + @Qty
where Kode = @Kode
end
END
INSERT INTO [SIPP].[dbo].[tbJual]
([NoFJ]
,[TglFJ]
,[Kode]
,[Qty]
,[Harga])
VALUES
('FJ-0004', getdate(), 'B-001', 30, 10000)
select * from dbo.tbJual
select * from dbo.tbBarang
CREATE TRIGGER TrupdateJual
ON dbo.tbJual
AFTER update
AS
BEGIN
Declare @KODE NVARCHAR(15), @QTY int, @QTYbaru int, @Harga money, @Hargabaru money, @TglFJ datetime
select @Qtybaru = Qty, @Hargabaru = Harga
from inserted
SELECT @Qty = Qty, @Kode = Kode, @Harga = Harga, @TglFJ = TglFJ
FROM deleted
if(datepart(mm, @TglFJ )=1)
begin
update dbo.tbBarang
set jual01 = jual01 + @Qtybaru*@Hargabaru - @qty*@harga, qty01 = qty01 + @Qtybaru - @qty
where Kode = @Kode
end
if(datepart(mm, @TglFJ )=2)
begin
update dbo.tbBarang
set jual02 = jual02 + @Qtybaru*@Hargabaru - @qty*@harga, qty02 = qty02 + @Qtybaru - @qty
where Kode = @Kode
end
if(datepart(mm, @TglFJ )=3)
begin
update dbo.tbBarang
set jual03 = jual03 + @Qtybaru*@Hargabaru - @qty*@harga, qty03 = qty03 + @Qtybaru - @qty
where Kode = @Kode
end
if(datepart(mm, @TglFJ )=4)
begin
update dbo.tbBarang
set jual04 = jual04 + @Qtybaru*@Hargabaru - @qty*@harga, qty04 = qty04 + @Qtybaru - @qty
where Kode = @Kode
end
if(datepart(mm, @TglFJ )=5)
begin
update dbo.tbBarang
set jual05 = jual05 + @Qtybaru*@Hargabaru - @qty*@harga, qty05 = qty05 + @Qtybaru - @qty
where Kode = @Kode
end
if(datepart(mm, @TglFJ )=6)
begin
update dbo.tbBarang
set jual06 = jual06 + @Qtybaru*@Hargabaru - @qty*@harga, qty06 = qty06 + @Qtybaru - @qty
where Kode = @Kode
end
if(datepart(mm, @TglFJ )=7)
begin
update dbo.tbBarang
set jual07 = jual07 + @Qtybaru*@Hargabaru - @qty*@harga, qty07 = qty07 + @Qtybaru - @qty
where Kode = @Kode
end
if(datepart(mm, @TglFJ )=8)
begin
update dbo.tbBarang
set jual08 = jual08 + @Qtybaru*@Hargabaru - @qty*@harga, qty08 = qty08 + @Qtybaru - @qty
where Kode = @Kode
end
if(datepart(mm, @TglFJ )=9)
begin
update dbo.tbBarang
set jual09 = jual09 + @Qtybaru*@Hargabaru - @qty*@harga, qty09 = qty09 + @Qtybaru - @qty
where Kode = @Kode
end
if(datepart(mm, @TglFJ )=10)
begin
update dbo.tbBarang
set jual10 = jual10 + @Qtybaru*@Hargabaru - @qty*@harga, qty10 = qty10 + @Qtybaru - @qty
where Kode = @Kode
end
if(datepart(mm, @TglFJ )=11)
begin
update dbo.tbBarang
set jual11 = jual11 + @Qtybaru*@Hargabaru - @qty*@harga, qty11 = qty11 + @Qtybaru - @qty
where Kode = @Kode
end
if(datepart(mm, @TglFJ )=12)
begin
update dbo.tbBarang
set jual12 = jual12 + @Qtybaru*@Hargabaru - @qty*@harga, qty12 = qty12 + @Qtybaru - @qty
where Kode = @Kode
end
END
INSERT INTO [SIPP].[dbo].[tbJual]
([NoFJ]
,[TglFJ]
,[Kode]
,[Qty]
,[Harga])
VALUES
('FJ-0001', getdate(), 'B-001', 5, 10000)
UPDATE [SIPP].[dbo].[tbJual]
SET [Qty] = 5
WHERE NoFJ = 'FJ-0004'
select * from dbo.tbJual
select * from dbo.tbBarang
Tidak ada komentar:
Posting Komentar