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

Materi Stored Procedures, Functions dan Trigger pada SQL Server

Nama / NIM : Septian Dwi Jaya Ade Putra / 10.41010.0228
Dosen         : Tan Amelia
Tugas        : membuat tulisan tentang materi Stored Procedures, Functions dan Trigger pada SQL Server.

Stored Procedures

Procedure adalah program yang disimpan dalam database seperti halnya data. Hal ini sebenanya cukup tidak umum, karena kita mengharapkan yang disimpan dalam database adalah data bukannya program.

Adanya dukungan Stored Procedure akan membuat program anda lebih ringkas dan mudah untuk dikembangkan. Dengan stored procedure : algoritma yang lama dimasukkan ke dalam stored procedure di SQL server, disimpan di server (misal dengan nama DoProsesStock). Pada program, cukup ditulis perintah SQL : Exec DoProsesStock StartDate, EndDate . Selanjutnya, SQL Server akan mengeksekusi perintah proses yang dinginkan. Satu kali perubahan proses, akan berlaku untuk semua user yang terhubung ke database, bahkan saat program masih berjalan di tiap komputer user (program tidak perlu dimatikan dulu).

Stored procedure akan menyederhanakan perintah SELECT * FROM table yang memang rumit, misal untuk laporan. Perintahnya dan algoritma nya sama dengan yang nomor 1. Bayangkan bila harus membuat perintah SELECT dari sebuah tabel, tapi juga diikuti kondisi-kondisi tertentu yang biasanya berhubungan dengan data dari tabel lain.

Stored procedure akan membantu dalam membuat laporan yang sifatnya analisa data, yang biasanya memerlukan banyak sekali tabel-tabel pembantu. Stored procedure, dapat membuat tabel temporary yang disimpan sementara di dalam memori server selama proses berlangsung, atau bisa juga selama program connect.

Stored procedure mengefisienkan proses, sehingga semua daya hanya akan dipusatkan di komputer server saja. Komputer client dapat berupa pentium 233 MMX, tapi dapat melakukan serangkaian proses yang rumit dan tetap tidak lambat. Karena bukan client yang berpikir, tetapi server yang melakukan pekerjaan, jadi client hanya akan mendapatkan data jadinya saja.

Membuat Stored Prosedures :

CREATE PROCEDURE MHSMTK

AS

BEGIN

    SELECT KDPST,NIMHS,NMMHS

    FROM MSMHS

    WHERE KDPST='44201'

     END

                

Dengan menggunakan parameter :

CREATE PROCEDURE MHSMTKcari

  @KDPRODI NVARCHAR(12)

AS

BEGIN

    SELECT KDPST,NIMHS,NMMHS

    FROM MSMHS

    WHERE KDPST=@KDPRODI

     END

                 

                     

Memanggil Stored Procedures :

     EXECUTE akademik.dbo.MHSMTK;

    EXECUTE akademik.dbo.MHSMTKcari ‘44201’

          

        

FUNCTION

Function dalam bahasa pemrograman adalah sebuah subrutin yang digunakan untuk mengenkapsulasi logika yang sering digunakan. Dengan function, kode program yang membutuhkan logika seperti terdapat dalam function tidak perlu menuliskan ulang akan tetapi cukup memanggil function tersebut. Perbedaan function dengan store procedure adalah function memberikan nilai keluaran sementara store procedure tidak.

Sintaks dasar untuk pembuatan Function :

CREATE FUNCTION nama_function

( [ @parameter tipe_data [ ,...n ] ] )

RETURNS tipe_data_return

[ AS ]

BEGIN

statement

RETURN nilai_return

END

Beberapa function yang disediakan untuk melakukan suatu operasi tertentu :

1.       §  Aggregate Functions

   Melakukan operasi yang mengkombinasi beberapa nilai menjadi satu.

   Contoh : COUNT, SUM, MIN dan MAX

        §  Cunfiguration Functions

   Merupakan Function dengan nilai skalar yang memberikan nilai konfigurasi setting

        §  Cursor Functions

   Memberikan informasi tentang status cursor

        §  Date and time Functions

   Memanipulasi nilai datetime dan smalldatetime

        §  Mathematical Functions

   Melakukan operasi trigonometri, geometri dan numerik

        §  Meta data Functions

   Memberikan informasi tentang atribut dari database dan obyek database

        §  Rowset Functions

   Memberikan rowset yang dapat digunakan pada tabel referensi dalam statement Transact-SQL

        §  Security Functions

   Memberikan informasi tentang users dan roles

        §  String Functions

   Memanipulasi nilai char, varchar, nchar, nvarchar, binary dan varbinary

        §  System Functions

   Operasi atau laporan pada bermacam level sistem dan obyek

TRIGGER

Trigger adalah prosedur tersimpan pada Microsoft SQL Server yang secara otomatis dijalankan apabila data di dalam tabel berubah karena eksekusi perintah SQL (INSERT, UPDATE atau DELETE). Salah satu penggunaannya yang paling umum adalah untuk menerapkan pembatasan yang lebih kompleks dari yang telah diizinkan melalui pembatasan CHECK, yang berfungsi membatasi informasi yang disisipkan ke dalam kolom. Trigger bisa dibuat bersama dengan perintah INSERT, yang akan melakukan query ke tabel lain dan mengembalikan nilai logik yang membantu membatasi data yang diberikan kepada kolom tertentu.

Sebagai contoh:

Trigger bisa dibuat untuk menjalankan replikasi, misalnya apabila ada sebuah baris disisipkan ke dalam database Z, sebuah baris dengan informasi yang sama akan ditambahkan ke dalam database Y.

Trigger dibuat sebagai sebuah transaksi dan bisa dimundurkan apabila ada masalah yang dideteksi.

Kegunaannya Triger :
1.Menentukan nilai kolom – kolom tertentu secara otomatis
2.Menghindarkan transaksi yang tidak valid
3.Membuat autorisasi sekuriti yang kompleks
4.Membuat bussines rule yang kompleks
Event eksekusi trigger :
1.DML event (INSERT, UPDATE & DELETE) statement.
2.DDL event (CREATE, ALTER & DROP) statement
3.Database event (Startup/Shutdown, Error Message & User event (Logon/Logoff))

Conoh syntax :

   ALTER Trigger [dbo].[UpdateTableCust]
        On [dbo].[Customer]
        For Insert,Update
   As
   Declare @Code As Varchar(50);
   Declare @Name As Varchar(50);

   Select @Code = i.CustCode From inserted i;
   Select @Name = i.CustName From inserted i;

   If @Code is not Null
   Begin
        If Not Exists(Select CustCode From TableCust WhereCustCode = @Code and CustName =@Name)
            Begin
                Insert Into TableCust(CustCode,CustName) Values (@Code,@Name)

   End