contoh Stored Procedured, Functional, dan Trigger

Berikut ini adalah contoh Stored Procedured, Functional, dan Trigger

Contoh Stored Pocedure

a. IN
   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"

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.

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.

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

DELETE PEMBELIAN
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'

    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

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 CABANG_BANK

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