Contoh Procedure, Function, dan Trigger

NIM / Nama : 10.41010.0245 / Isnainul Amanda Perwirasari

Dosen : Tan Amelia

Tugas : Contoh Procedure, Function, dan Trigger 

CONTOH PROCEDURE

1. Membuat Procedure Untuk Menginputkan data pada tabel Categories

CREATE PROC InsertCategory @CategoryID int, @CategoryName nvarchar(15), @Description ntext, @picture image, @stock int AS INSERT INTO Categories VALUES (@CategoryID, @CategoryName, @Description, @picture, @stock)

EXEC InsertCategory @CategoryID = 8, @CategoryName = 'Fruit' , @Description = 'banana, apple, orange', @picture='' , @stock = 20

 

2. Membuat Procedure untuk mencari data berdasarkan Id Customer dimana contoh dibawah ini menampilkan yg Id Customer bernama ALFKI

CREATE PROC ViewOrder @CustomerID nvarchar(5) AS SELECT Orders.CustomerID, OrderID, CompanyName, OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID WHERE Orders.CustomerID = @CustomerID

EXEC ViewOrder @CustomerID = 'ALFKI'

3. Menampilkan Jumlah Data Product berdasarkan Category

CREATE PROC JmlProduct @CategoryID int AS SELECT Products.CategoryID, CategoryName, count(Products.CategoryID) AS Jumlah from Products INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID AND Products.CategoryID = @CategoryID GROUP BY Products.CategoryID, CategoryName

EXEC JmlProduct @CategoryID = 2

4. Mengupdate data pada tabel Employee dimana yang di update hanya LastName dan FirstName

CREATE PROC UpdateEmployees @LastName nvarchar(20), @FirstName nvarchar(10), @EmployeeID int AS UPDATE Employees SET LastName = @LastName, FirstName = @FirstName WHERE EmployeeID = @EmployeeID SELECT * FROM Employees WHERE EmployeeID = @EmployeeID

EXEC UpdateEmployees @LastName = 'Perwirasari', @FirstName = 'Manda', @EmployeeID = 3

 

5. Menampilkan Data Supplier yang tinggal di Negara Tertentu

CREATE PROC ViewSupplier @Country nvarchar(15) AS SELECT CompanyName, ContactName, ContactTitle, City FROM Suppliers WHERE Country = @Country

Exec ViewSupplier @Country = 'USA'

CONTOH FUNCTION

1. Membuat Function Untuk Menghitung Total Harga Dari Products berdasrkan Stock dan Price

ALTER FUNCTION total (@UnitPrice money, @UnitInStock smallint) returns numeric AS

      begin

                                    declare @total numeric;

                                    set @total = @UnitPrice * @UnitInStock;

                  return @total;

 end;

select ProductID, ProductName,UnitPrice, UnitsInStock, dbo.total (UnitPrice, UnitsInStock)AS total from Products

2. Membuat Fundtion Untuk mengetahui apakah Stock pada suatu product Kosong atau tidak

ALTER FUNCTION status (@UnitsInStock smallint) returns varchar(15)       AS

                        BEGIN

                        DECLARE @status varchar(15);

                        IF (@UnitsInStock <=0)

                                    SET @status = 'KOSONG';

                        ELSE

                                    SET @status = 'ADA'

                        RETURN @status;

                        END;

select ProductID, ProductName, UnitsInStock, dbo.status UnitsInStock)AS total from Products

  

3. Membuat Suatu Function Untuk Menghitung total dari Suatu Invoice yang berasalkan dari Harga dan Jumlahnya dan dilihat tiap Customer

CREATE FUNCTION TotalInvoice(@UnitPrice money, @Quantity smallint)

returns numeric

            AS

                        BEGIN

                        DECLARE @total numeric;

                        SET @total = @UnitPrice * @Quantity

                        RETURN @total;

                        END;

SELECT CompanyName, ProductName, Quantity, UnitPrice, dbo.TotalInvoice(UnitPrice, Quantity) AS TOTAL FROM Invoices INNER JOIN Customers ON Invoices.CustomerID = Customers.CustomerID

 

4. Membuat Function Untuk Menghitung Total dari Invoice serta Memberikan suatu diskon yang mana jika Jumlah >= 20 mendapat diskon 10% serta Jika Jumlah >= 50 mendapat diskon 15% dan untuk Jumlah >=100 mendapat diskon 20%

CREATE FUNCTION TotalInvoice(@UnitPrice money, @Quantity smallint) returns numeric

            AS

                        BEGIN

                        DECLARE @total numeric;

                        SET @total = @UnitPrice * @Quantity

                        RETURN @total;

                        END;

ALTER FUNCTION DiskonInvoice(@Quantity smallint)returns 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

                                                SET @diskon = 0;

                                    RETURN @diskon

                        END;

SELECT CompanyName, ProductName, Quantity, UnitPrice, dbo.TotalInvoice(UnitPrice, Quantity) AS TOTAL, dbo.DiskonInvoice(Quantity) AS Diskon FROM Invoices INNER JOIN Customers ON Invoices.CustomerID = Customers.CustomerID

 

5. Membuat Function Untuk Mengetahui apakah Stock dari ProductByCategory ada atau tidak

CREATE FUNCTION StockProductByCategory (@UnitsInStock smallint)returns varchar(10)

            AS

                        BEGIN

                                    DECLARE @stat varchar(15)

                                    IF(@UnitsInStock >0)

                                                SET @stat = 'ADA';

                                    ELSE

                                                SET @stat = 'KOSONG';

                                    RETURN @stat

                        END;

SELECT *,dbo.StockProductByCategory(UnitsInStock) AS Status FROM [Products by Category]

CONTOH TRIGGER

1. Trigger Insert dari tabel Categories

CREATE TRIGGER InsertCategories ON Categories FOR INSERT AS INSERT INTO Categories (CategoryID, CategoryName, Description) VALUES(9,'Cake','Tart, Bread')

SELECT * FROM Categories

2. Trigger Update dari Tabel Categories

CREATE TRIGGER UpdateCategories ON Categories FOR UPDATE AS

DECLARE @CategoryName nvarchar(15);

DECLARE @CategoryID int;

UPDATE Categories SET CategoryName = @CategoryName WHERE CategoryID = @CategoryID

UPDATE Categories SET CategoryName = 'Buah' WHERE CategoryID = 8

3. Trigger Menghapus Data pada tabel Categories

CREATE TRIGGER DropCategories ON Categories FOR DELETE AS DELETE FROM Categories WHERE CategoryID = 1

4. Trigger Menghapus Data Dari Tabel Customer

CREATE TRIGGER DropCustomers ON Customers FOR DELETE AS

DECLARE @CustomerID nvarchar(5);

DELETE FROM Customers WHERE CustomerID = @CustomerID

5. Trigger Update dari tabel Products

CREATE TRIGGER UpdateProducts ON Products FOR UPDATE AS

DECLARE @ProductName nvarchar(40);

DECLARE @UnitPrice money;

DECLARE @UnitsInStock smallint;

DECLARE @ProductID int

UPDATE Products SET ProductName = @ProductName, UnitPrice = @UnitPrice, UnitsInStock = @UnitsInStock WHERE ProductID = @ProductID