Contoh Stored Procedure, Function, dan Trigger

Nim / Nama : 10.41010.0219 / Gerry Rinovel Harimu

Dosen : Tan Amelia

Tugas : Contoh Stored Procedure, Function, Trigger

• Stored Procedure

CREATE PROCEDURE SuppliersCity
@SuppliersName varchar (10)
AS SELECT CompanyName, ContactName, Country
FROM Suppliers
WHERE Country like @SuppliersName


EXEC SuppliersCity 'UK'

ALTER PROCEDURE Liat_Stock
@CountryName VARCHAR(25), @SumOfStock INT OUTPUT
AS
SELECT Suppliers.SupplierID, Suppliers.Country, Products.UnitsInStock,
SUM (ProductID) AS SumOfProducts
FROM Suppliers, Products
WHERE Country like @CountryName and UnitsInStock like @SumOfStock
GROUP BY Suppliers.SupplierID, Country, UnitsInStock


EXEC Liat_Stock 'USA', '0'

• Function

CREATE FUNCTION lincrement (@G integer)

RETURNS integer

BEGIN

while @G > 100

begin

       set @G=@G + 5

end

return @G

END

      

CREATE FUNCTION Harga (@quantity int , @Nilai varchar (20))

returns char

begin

if @quantity > 20

begin

            set @Nilai = 'Dapat Bonus'

end

            else

            set @Nilai = 'Perlu Di Tingkatkan'

return @Nilai

end

SELECT OrderID, COUNT (Quantity)as quantity

FROM Orders_Details

• Trigger

Insert

CREATE TRIGGER For_insert ON dbo.[Categories]

FOR INSERT

AS

INSERT INTO [Categories] (CategoryID, CategoryName, Description)

values ('1', 'Healt', 'Milk, Rice, Fish, Soup')

Delete

ALTER TRIGGER For_Delete ON dbo.Categories

FOR DELETE

AS

DECLARE

@ID int

set @ID = (select CategoryID FROM Categories)

BEGIN

   DELETE FROM Categories where CategoryID = @ID

END