Stored Procedures, Functions dan Trigger pada SQL Server

NIM/NAMA : 10.41010.0224 / Filzah Apritasari
Dosen        :  Tan Amelia
Tugas         : Stored Procedures, Functions dan Trigger pada SQL Server.

Stored Procedure

      Stored Procedure, atau kadang disingkat sproc, merupakan salah satu fungsi pemrograman prosedural yang tersedia pada SQL Server. Prinsip pembuatan sproc pada dasarnya mirip dengan pembuatan fungsi atau class pada pemrograman berbasis objek (OOP). Pada SQL Server disediakan keyword function yang dapat mengembalikan nilai tertentu sesuai parameter yang telah ditentukan sebelumnya.

        Stored procedure dapat digunakan untuk memanipulasi data, melakukan sebuah pekerjaan (job) secara otomatis, melakukan perawatan (maintenance) secara otomatis, dll.

               Menerapkan stored procedure pada SQL Server pada dasarnya tidaklah terlalu sulit. Terlebih lagi, dikarenakan fungsi ini telah tersedia semenjak SQL Server 7.0. Berikut adalah sintaks pemrograman dari sebuah stored procedure:

  1: CREATE PROCEDURE|PROC <sproc name>

  2: [<parameter name> [schema.]<data type> [VARYING] [= <default value>] [OUT

  3: [PUT]][,

  4: <parameter name> [schema.]<data type> [VARYING] [= <default value>]

  5: [OUT[PUT]][,

  6: ...

  7: ...

  8: ]]

  9: [WITH

 10: RECOMPILE| ENCRYPTION | [EXECUTE AS { CALLER|SELF|OWNER|<’user name’>}]

 11: [FOR REPLICATION]

 12: AS

 13: <code> | EXTERNAL NAME <assembly name>.<assembly class>

Untuk mengakses stored procedure yang telah dibuat, maka dapat menggunakan perintah seperti ini:

  1: EXEC <sproc name>

Jika diperhatikan, sintaks tersebut pada dasarnya menggunakan logika pemrograman prosedural standar dari sebuah fungsi, dimana ketika kita mendefinisikan sebuah variabel maka kita perlu mendefinisikan tipe datanya terlebih dahulu.

Untuk lebih jelasnya, kita akan mencoba membuat sebuah stored procedure yang bertujuan untuk memasukkan data siswa baru dan memberikan ID secara otomatis dengan format sebagai berikut:

inisial nama depan (1 karakter) + inisial nama belakang (1 karakter) + tahun (4 karakter) + bulan (2 karakter) + nomor urut (4 karakter)

Berikut adalah implementasi dari pembuatan stored procedure yang bertujuan untuk memanipulasi data secara otomatis:

  1: CREATE PROC sp_add_siswa

  2: @id_siswa          varchar(20),

  3: @namaDepan         varchar(50),

  4: @namaBelakang      varchar(50),

  5: @tgl_lahir         date

  6: AS

  7:        DECLARE @kunci          varchar(20)

  8:        DECLARE @urut           varchar(20)

  9:

 10:        --keyword SET berfungsi untuk mendefinisikan nilai dari variabel

 11:        --nilai dari variabel @kunci bertujuan untuk menghasilkan format inisial, tahun, dan bulan

 12:        SET @kunci = UPPER(SUBSTRING(LTRIM(@namaDepan),1,1)) +

 13:                    UPPER(SUBSTRING(LTRIM(@namaBelakang),1,1)) +

 14:                    CONVERT(varchar,DATEPART(YEAR,@tgl_lahir)) +

 15:                    RIGHT('0' + CONVERT(varchar,DATEPART(MONTH,@tgl_lahir)),2)

 16:

 17:        --sintaks ini bertujuan untuk menghasilkan nomor urut yang akan

 18:        --bertambah secara otomatis

 19:        SELECT @urut = ISNULL(MAX(RIGHT(id_siswa,4)),0) + 1 FROM Siswa WHERE id_siswa LIKE @kunci + '%'

 20:        SET @urut = RIGHT('000' + @urut,4)

 21:

 22:        SET @kunci = @kunci + @urut

 23:

 24:        INSERT INTO Siswa VALUES(@kunci,@nama,@tgl_lahir)

Untuk mengeksekusi stored procedure yang telah dibuat, maka cukup digunakan perintah EXEC [nama_stored_procedure] disertai dengan urutan nilai yang ingin dimasukkan ke dalam basis data:

  1: --bagian yang dikosongkan adalah bagian id_siswa yang nilainya

  2: --akan dihasilkan secara otomatis oleh sproc

  3: EXEC sp_add_number '', 'Unyil', 'Bocil', '1988-2-16'

Jika perintah ini dijalankan, maka dapat dilihat bahwa stored procedure akan menghasilkan ID dari siswa dengan nilai: ‘UB2011030001’.

Function

Manfaat SQL Server User-Defined Function sangat banyak. Pertama, kita dapat menggunakan fungsi-fungsi di berbagai tempat begitu banyak bila dibandingkan dengan Store Procedure SQL Server . Kemampuan untuk fungsi untuk bertindak seperti table (untuk table Inline dan Multi-Statement table) memberikan pengembang kemampuan untuk membuat logika kompleks ke dalam blok kode lebih pendek dan lebih pendek. Ini biasanya akan memberikan manfaat tambahan membuat kode yang kurang kompleks dan lebih mudah untuk menulis dan memelihara. Dalam kasus Scalar Valued User-Defined , kemampuan untuk menggunakan fungsi ini di mana saja Anda dapat menggunakan skalar dari tipe data yang sama juga merupakan hal yang sangat kuat.Menggabungkan keuntungan ini dengan kemampuan untuk melewati parameter ke dalam database objekmembuat SQL Server User-Defined Function menjadi alat yang powerfull.

Trigger

Trigger adalah program yang dieksekusi oleh DBMS ketika suatu event terjadi. Trigger diaplikasikan pada tabel atau view. Sebuah tabel atau view dapat memiliki lebih dari 1 trigger. Tetapi sebuah trigger hanya digunakan pada 1 tabel atau view.  Trigger dijalankan karena request insert, update, atau delete pada suatu tabel atau view.

3 Tipe trigger:

·         BEFORE (insert, update, delete)

·         INSTEAD OF (insert, update, delete)

·         AFTER (insert, update, delete)

Total ada 9 tipe trigger.

SQL Server hanya mensupport 6 tipe trigger. Hanya tipe AFTER dan INSTEAD OF saja.

Kegunaan TRIGGER

• Menghasilkan nilai yang sudah ditentukan

• Mengupdate View

• Melaksanakan Aksi dengan referensi integritas

Membuat TRIGGER

Klausa FOR memiliki default untuk klausa AFTER.  Sql_statement merupakan Transact-SQL, bisa ada definisi variable, query, control-of-flow (IF, ELSE, WHILE, BEGIN – END).

CREATE TRIGGER [nama_trigger]

ON [table atau view]

{ FOR | AFTER | INSTEAD OF }

{ [DELETE] [,] [INSERT] [,] [UPDATE] }

AS

[sql_statement]