Berikut ini adalah contoh Stored Procedured, Functional, dan Trigger
a. IN
Contoh berikut adalah untuk mengetahui perusahaan dengan suppliers ID dar negara "UK"
Contoh berikut adalah untuk mengetahui perusahaan dengan suppliers ID dar negara "UK"
CREATE PROCEDURE SupplierCountry
@Country varchar(15)
AS SELECT SupplierID, CompanyName, Country
FROM [Suppliers]
WHERE Country = @Country
EXEC SupplierCountry 'UK'
*Hasil setelah di EXECUTE
b. OUT
Contoh berikut untuk mengetahui berapa jumlah employee dengan employee ID milik "Anne"
Contoh berikut untuk mengetahui berapa jumlah employee dengan employee ID milik "Anne"
CREATE PROC CountEmployee
(@count varchar (20) OUT)
AS SELECT sum (EmployeeID) AS
CountEmployee, EmployeeID, FirstName
FROM Employees
WHERE FirstName like @count
GROUP BY EmployeeID, FirstName
EXEC CountEmployee 'Anne'
*Hasil setelah di EXECUTE
c. INOUT
Contoh tersebut digunakan untuk mengetahui nama perusahaan sesuai inputan dan unit stock yang ada dalam perusahaan tersebut.
d. IN
Contoh ini untuk mengetahui letak costomer yang berada di kota sesuai inputan.
e. OUT
Contoh berikut untuk ingin mengetahui berapa jumlah category dengan inputan categry "Beverages"
Contoh tersebut digunakan untuk mengetahui nama perusahaan sesuai inputan dan unit stock yang ada dalam perusahaan tersebut.
CREATE PROC Pemasok
@CompanyName VARCHAR(40), @SumOfStock INT OUTPUT
AS
SELECT Suppliers.SupplierID, Suppliers.CompanyName,Products.ProductName, SUM (ProductID) AS SumOfSock
FROM Suppliers, Products
WHERE CompanyName like @CompanyName and UnitsInStocklike @SumOfStock
GROUP BY Suppliers.SupplierID, Suppliers.CompanyName,Products.ProductName
EXEC Pemasok 'Exotic Liquids', '120'
d. IN
Contoh ini untuk mengetahui letak costomer yang berada di kota sesuai inputan.
CREATE PROCEDURE CustomerCity
@City varchar(15)
AS SELECT CustomerID, ContactName, City
FROM [Customers]
WHERE City = @City
EXEC CustomerCity 'London'
e. OUT
Contoh berikut untuk ingin mengetahui berapa jumlah category dengan inputan categry "Beverages"
CREATE PROC CountCategory
(@count varchar (15) OUT)
AS SELECT sum (CategoryID) AS
CountCategory, CategoryID, CategoryName
FROM Categories
WHERE CategoryName like @count
GROUP BY CategoryID, CategoryName
EXEC CountCategory 'Beverages'
*Hasil setelah di EXECUTE
Contoh Function
a. Fungsi ini digunakan untuk menghitung berapa pendapatan yang diperoleh
dari harga barang yang dikalikan dengan jumlah stok.
ALTER FUNCTION TotalPendapatan(@UnitPrice money,@UnitInStock smallint)
RETURN NUMERIC
AS
BEGIN
DECLARE @total NUMERIC;
SET @total = @UnitPrice * @UnitInStock;
RETURN @total;
END
SELECT ProductID, ProductName, UnitInstock,
dbo.TotalPendapatan (UnitPrice, UnitInStock)
AS TotalPendapatan FROM Products
b. Fungsi ini digunakan untuk menghitung total faktur dari harga yang ada pada tablel Invoices dan Customers.
c. Fungsi ini digunakan untuk mengetahui apakah ada stok pada barang tertentu di tabel Products.
e. Fungsi ini digunakan untuk menghitung total jumlah faktur yang diberi diskon pada jumlah tertentu.
Contoh Trigger
a. INSERT
ALTER FUNCTION JumlahFaktur(@UnitPrice MONEY, @Quantitysmallint)
RETURN NUMERIC
AS
BEGIN
DECLARE @jumlah NUMERIC;
SET @jumlah = @UnitPrice * @Quantity
RETURN @jumlah;
END
SELECT CompanyName, ProductName, Quantity, UnitPrice,
dbo.JumlahFaktur (UnitPrice, Quantity)
AS Jumlah Faktur FROM Invoices INNER JOIN Customers
ON Invoices.CustomerID = Customers.CustomerID
c. Fungsi ini digunakan untuk mengetahui apakah ada stok pada barang tertentu di tabel Products.
ALTER FUNCTION StatusBarang(@UnitInStock smallint)
RETURN varchar(15)
AS
BEGIN
DECLARE @status varchar(15);
IF (@UnitInStock <=0);
SET @status = 'empty';
ELSE
SET @status = 'here';
RETURN @status;
END
SELECT ProductID, ProductName, UnitInstock,
dbo.StatusBarang (UnitPrice, UnitInStock)
AS StatusBarang FROM Products
d. Fungsi ini digunakan untuk mengetahui apakan ada stok pada barang di tabel Products by Category.
ALTER FUNCTION InfoProduksi(@UnitInStock smallint)
RETURN varchar(10)
AS
BEGIN
DECLARE @info varchar(10);
IF (@UnitInStock>0);
SET @info = 'here';
ELSE
SET @info = 'empty';
RETURN @info;
END
SELECT *
dbo.InfoProduksi (UnitInStock)
AS InfoProduksi FROM [Products by Category]
e. Fungsi ini digunakan untuk menghitung total jumlah faktur yang diberi diskon pada jumlah tertentu.
CREATE FUNCTION JumlahFaktur(@UnitPrice MONEY, @Quantitysmallint)
RETURN NUMERIC
AS
BEGIN
DECLARE @jumlah NUMERIC;
SET @jumlah = @UnitPrice * @Quantity
RETURN @jumlah;
END;
ALTER FUNCTION DiskonFaktur(@Quantity smallint)
RETURN smallint
AS
BEGIN
DECLARE @diskon smallint;
SET
IF (@Quantity >= 20)
SET @diskon = 10;
ELSE IF (@Quantity >= 50)
SET @diskon = 15;
ELSE IF (@Quantity >= 100)
SET @diskon = 20;
ELSE IF (@Quantity >= 200)
SET @diskon = 30;
ELSE
SET @diskon = 0;
RETURN @diskon;
END
SELECT CompanyName, ProductName, Quantity, UnitPrice,
dbo.JumlahFaktur (UnitPrice, Quantity) AS Jumlah Faktur,
dbo.DiskonFaktur (Quantity)AS Diskon
FROM Invoices INNER JOIN Customers
ON Invoices.CustomerID = Customers.CustomerID
Contoh Trigger
a. INSERT
Adalah suatu trigger yang akan dijalankan ketika sebuah tabel yang disertai trigger tersebut dikenai operasi insert.
Berikut adalah contoh trigger INSERT_BELI yang bekerja pada tabel PEMBELIAN [No_Beli, Tgl_Beli, Kode_Sup, Kd.Brg, Hrg_Brg, dan Jml_Beli] dan merespon tabel BARANG [KODE, Nama, Harga_B, Harga_J, dan Jumlah].
Jika ada pembelian yang diinputkan, jumlah barang di Tabel BARANG berubah.Jumlah barang diperoleh dari jumlah barang lama di tabel barang ditambah jumlah barang di tabel barang dari hasil pembelian di tabel PEMBELIAN.
CREATE Trigger INSERT_BELI
ON PEMBELIAN
FOR Insert
AS
SELECT * FROM INSERTED
UPDATE BARANG
SET Jumlah = Jumlah + Jml_Beli
FROM INSERTED
WHERE BARANG.Kode = INSERTED.Kd_Brg
Lalu inputkan data baru pada tabel PEMBELIAN seperti contoh berikut:
INSERT INTO PEMBELIAN
VALUES ('000003', '04/25/2010', '222', 'PK456',120000, 7)
Maka akan terjadi penambahan pada tabel PEMBELIAN dengan kode "PK456" sebanyak 7 sehingga akan mempengaruhi jumlah stok akhir yang akan bertambah 7.
b. DELETE
trigger ini merupakan trigger yang akan dijalankan ketika sebuah tabel dikenai operasi DELETE. Trigger ini dapat melakukan perubahan pada tabel yang terhubung seperti pada trigger INSERT.
Contohnya Trigger dengan nama DELETED_BRG yang ditempatkan pada tabel BARANG.Jika salah satu data pata tabel tersebut dihapus maka seluruh data pada tabel PEMBELIAN harus dihapus.
CREATE TRIGGER DELETE_BRG
ON BARANG
FOR Delete
AS
ON BARANG
FOR Delete
AS
DELETE PEMBELIAN
FROM PEMBELIAN, Deleted
WHERE PEMBELIAN.Kd_Brg = Deleted.Kode
PRINT 'Data Pembelian telah dihapus !'
FROM PEMBELIAN, Deleted
WHERE PEMBELIAN.Kd_Brg = Deleted.Kode
PRINT 'Data Pembelian telah dihapus !'
Berikut adalah perintah query baru yang bertujuan untuk menghapus data barang dengan kode "PK456".
DELETE BARANG
WHERE Kode = 'PK456'
WHERE Kode = 'PK456'
Dari prpses ini, selain menghapus data di tabel BARANG juga pada tabel PEMBELIAN. Hal inibiasanya di tunjukkan oleh pesan pertama penghapusan data pada tabel PEMBELIAN dan pesan selanjutnya tentang penghapusan di tabel BARANG.
c. UPDATE
Trigger UPDATE merupakan trigger yang akan dijalankan ketika sebuah tabel dikenai operasi UPDATE.
Contoh trigger dengan nama GANTI_HARGA untuk mengecek kenaikan harga barang pada proses UPDATE ke tabel BARANG.Jika harga barang baru lebih dari 10% harga lama, maka proses update akan dibatalkan dengan ROLLBACK. Tapi jika besarnya kurang atau sama dengan 10%, maka proses dilanjutkan.
CREATE TRIGGER GANTI_HARGA
ON BARANG
FOR UPDATE
AS
DECLARE
@Harga_Lama MONEY,
@Harga _Baru MONEY,
@Prosen FLOAT
SELECT @Harga_Lama = Harga_B FROM Deleted
Berikut adalah perintah query baru di tabel BARANG dengan menaikkan harga barang menjadi 12% dari semula untuk kode barang "PK123".
d. INSERT
Contoh berikut adalah membuat nama trigger INSERT_JUAL yang bekerja di database TOKO_BUKU yang akan mengubah data pada tabel BUKU sekaligus mengubah stock buku di tabel BUKU dengan jumlah buku yang terjual.
e. UPDATE
Trigger berikut dibuat untuk memberikan konfirmasi tiapa data yang diubah pada tabel CABANG_BANK dengan nama trigger RubahNamaCabang.
Trigger UPDATE merupakan trigger yang akan dijalankan ketika sebuah tabel dikenai operasi UPDATE.
Contoh trigger dengan nama GANTI_HARGA untuk mengecek kenaikan harga barang pada proses UPDATE ke tabel BARANG.Jika harga barang baru lebih dari 10% harga lama, maka proses update akan dibatalkan dengan ROLLBACK. Tapi jika besarnya kurang atau sama dengan 10%, maka proses dilanjutkan.
CREATE TRIGGER GANTI_HARGA
ON BARANG
FOR UPDATE
AS
DECLARE
@Harga_Lama MONEY,
@Harga _Baru MONEY,
@Prosen FLOAT
SELECT @Harga_Lama = Harga_B FROM Deleted
PRINT 'Harga lama = Rp ' +STR(@Harga_Lama,7)
SELECT @Harga_Baru = Harga_B FROM Inserted
PRINT 'Harga baru = Rp ' +STR(@Harga_Baru,7)
SET @Prosen = ((@Harga_Baru -@Harga_Lama)/@Harga_Lama)*100
IF (@Harga_Baru > (@Harga_Lama*1.1))
BEGIN
PRINT 'Proses di batalkan (ROLLBACK)'
ROLLBACK
END
ELSE
BEGIN
PRINT 'Harga telah diubah'
END
PRINT 'Kenaikan harga barang ' + STR(@Prosen,3 + '%'
Berikut adalah perintah query baru di tabel BARANG dengan menaikkan harga barang menjadi 12% dari semula untuk kode barang "PK123".
UPDATE BARANG
SET Harga_B = Harga_B * 1.12
d. INSERT
Contoh berikut adalah membuat nama trigger INSERT_JUAL yang bekerja di database TOKO_BUKU yang akan mengubah data pada tabel BUKU sekaligus mengubah stock buku di tabel BUKU dengan jumlah buku yang terjual.
CREATE TRIGGER INSERT_JUAL
ON JUAL
FOR INSERT
AS
SELECT * FROM Inserted
UPDATE BUKU
SET Jumlah = Jumlah - Jml_Jual
FROM Insreted
WHERE BUKU.Kode = Inserted.Kb_Buku
Kemudian memasukan inputan ke tabel JUAL.
INSERT INTO JUAL
VALUES ('000004', '03/20/2005', '1111', 36000,1)
e. UPDATE
Trigger berikut dibuat untuk memberikan konfirmasi tiapa data yang diubah pada tabel CABANG_BANK dengan nama trigger RubahNamaCabang.
CREATE TRIGGER RubahNamaCabang
ON CABANG_BANK
FOR UPDATE
AS
PRINT 'Data Cabang Bank berhaasil di ubah!'
Lalu berikan perintah berikut untuk melakukan perubahan record pada tabel CABANG_BANK.
UPDATE
SET ALAMAT_BANK = 'Jl. Kaliurang km 9.5 no.92'
WHERE KODE_CABANG = 'BRUK'
sumber : http://yoeone.blogspot.co.id/2011/09/tugas-pbd.html
No comments:
Post a Comment