Contoh membuat Stored Procedures, Functions, dan Trigger dalam SQL Server

  Nim / Nama     : 10.41010.0228 / Septian Dwi Jaya A.P

  Dosen            : Tan Amelia

  Tugas            : Contoh Stored Procedure, Function, Trigger

Membuat Stored Procedure


Contoh membuat proc barang dengan menampilkan semua data pada table product di database nortwind :
                          CREATE PROC BARANG AS
                          SELECT * FROM Products


Disini kita memanggil data product dimana productId=@kode, dan saat menjalankan kita masukkan paramete @kodenya = 2.
                          create proc data_brg(@kode int)
                          as
                          select * from Products
                          where ProductID = @kode

                          exec data_brg '2'

Jika dengan 2 parameter

                          create proc data_brg2
                          @cat int, @stock int
                          as
                          select * from Products
                          where CategoryID = @cat where CategoryID = @cat
                          and UnitsInStock < @stock exec data_brg2 @cat='2', @stock='5' execute data_brg2 '2', '5'

Mengindikasikan bahwa parameter berupa return parameter. Nilai parameter dpt diubah dan diberikan nilainya pada program pemanggilnya. nilainya pada program pemanggilnya.

                         create proc lihat_brg
                         (@kode varchar(5),
                         @nama varchar(30) output,
                         @stock decimal output,
                         @hrgj decimal output)
                         AS
                         select @nama=ProductName,
                         @stock=UnitsInStock,
                         @hrgj=UnitPrice
                         From Products
                         where ProductID = @kode

Untuk menjalankan, variabel pada program yg memanggil harus dideklarasikan dan saat dieksekusi diikuti kata output

                         Declare @nm varchar(50), @jml decimal,
                         @hargaj decimal
                         Execute lihat_brg '1', @nm output, @jml
                         output, @hargaj output
                         Print 'Nama Brg : ' + @nm
                         Print 'Stock : ' + str(@jml) Print 'Stock : ' + str(@jml)
                         Print 'Harga Jual : ' + str(@hargaj)
                         Print 'Total Harga : ' + str(@jml * @hargaj)

Membuat Functions

SUM untuk agregat dan agregat baris

Contoh ini menunjukkan perbedaan antara fungsi agregat dan fungsi agregat baris. Yang pertama menunjukkan fungsi agregat memberikan data ringkasan saja, dan menunjukkan fungsi agregat baris kedua memberikan detail dan data ringkasan.

1. USE pubs  
2. GO  
3. -- Aggregate functions  
4. SELECT type, SUM(price), SUM(advance)  
5. FROM titles  
6. WHERE type LIKE '%cook'  
7. GROUP BY type  
8. ORDER BY type  
9. GO  
10.   
11. USE pubs  
12. GO  
13. -- Row aggregates  
14. SELECT type, price, advance  
15. FROM titles  
16. WHERE type LIKE '%cook'  
17. ORDER BY type  
18. COMPUTE SUM(price), SUM(advance) BY type

Contoh menghitung total kelompok dengan lebih dari satu kolom, menghitung jumlah harga dan advance untuk setiap jenis buku.

1. USE pubs  
2. GO  
3. SELECT type, SUM(price), SUM(advance)  
4. FROM titles  
5. GROUP BY type  
6. ORDER BY type  
7. GO 

Membuat Trigger

tabel yang ada :

           TourID |   StartDate   |   EndDate
           T001        5/18/2007      5/13/2007

Trigger untuk Insert dan Update :

        CREATE TRIGGER Tour_Duration_TG
               ON  Tour
               FOR INSERT,UPDATE
        AS
               SET NOCOUNT ON;
               DECLARE @DURATION AS INT
               SELECT @DURATION = DATEDIFF(dd,Start_Date, End_Date) FROM Tour
               IF ((@DURATION BETWEEN 3 AND 18) OR (@DURATION=0))
        BEGIN
               ROLLBACK TRANSACTION
               RAISERROR('Tour duration must with in 3 to 18!',16,1)
   
        END
        GO

        DROP TRIGGER Tour_Duration_TG

        INSERT INTO Tour
        (Tour_ID, Start_Date, End_Date)
        VALUES
        ('T008', 5/11/2007, 5/16/2007)

Untuk menghapus trigger

Bentuk umum dan contoh menghapus trigger :

CREATE TRIGGER [trigger_delete]

02	ON  [dbo].[Table_Siswa]

03	for DELETE

04

AS

05

BEGIN

06	SET NOCOUNT ON;

07

08	declare @ko_sis as char(7)

09	declare @na_sis as varchar(50)

10	declare @usr varchar(30)

11	declare @tgl datetime

12

13	set @usr = suser_sname()

14	set @tgl = getdate()

15	select @ko_sis = kode_siswa, @na_sis = nama_siswa from deleted

16	insert into Log_insert_delete values(@ko_sis, @na_sis, 'Delete', @usr, @tgl)

17

END