Contoh Trigger, Storage Procedure, Function

NIM / NAMA : 10.41010.0203 / Angga Wahyu
Dosen : Tan Amelia
Tugas  : contoh trigger, storage procedure, function

Disini saya menggunakan database "NORTHWIND"

1. Stored Procedure
Dengan mengunakan parameter "IN"
Contoh sintaksnya:



CREATE PROC SCountry
     @Cityname varchar(15)
     AS SELECT SupplierID, CompanyName, ContactName, City, Country, Phone
     FROM Suppliers
     WHERE Country like @Cityname

     EXEC SCountry 'USA'

Dengan menggunakan parameter "OUT"
Contoh sintaksnya:

CREATE PROC CountOrderByStatus
        (@total INT OUTPUT )
         AS
         SELECT ShipName,CustomerID,UnitPrice,Quantity , count(OrderID)      
         as SumOfProduct
         FROM Invoices
         WHERE Quantity like @total
         group by  ShipName,CustomerID,UnitPrice,Quantity

         EXEC CountOrderByStatus '24'

Cara update data mengunakan stored prosedure
Contoh sintaksnya:

CREATE PROC update_employee
@EmployeeID varchar (10),
@city varchar (15)
AS
UPDATE Employees
SET City=@city
where EmployeeID=@EmployeeID

EXEC update_employee '9','Surabaya'

2. Function
Contoh penggunaan function

CREATE FUNCTION firstName
(@fname char (20))
returns char
begin
return @fname
end

select FirstName, LastName,
dbo.FirstName (FirstName) from Employees

3. TRIGGER


Contoh dari trigger

CREATE TRIGGER For_Delete2 ON dbo.[Employees]
FOR DELETE
AS
DECLARE
@dlt int
set @dlt = (select EmployeeID FROM [Employees])
BEGIN
      if @dlt = 1
            BEGIN
            DELETE FROM [Employees] where EmployeeID = @dlt
            END
      ELSE
            BEGIN
            PRINT 'DATA TIDAK ADA'
            END
END