Stored Procedures, Functions dan Trigger pada SQL Server

Stored Procedures, Functions dan Trigger pada SQL Server

Stored procedure

Stored procedure adalah sekumpulan perintah-perintah SQL yang tersimpan dalam SQL Server. Procedure ini sudah dalam keadaan ter-compile di server & siap dipanggil apabila diperlukan

Stored Procedure merupakan rangkaian program yang dapat disimpan dan dipanggil / dieksekusi oleh program lain atau dieksekusi oleh SQL-prompt. Umumnya suatu stored procedure yang dibuat oleh SQL Server berhubungan dengan database server dan menghasilkan sekumpulan record. Stored procedure ditulis dengan sebuah script. Pemakaian stored procedure akan meningkatkan performa database, meningkatkan sekuritas database, dan memungkinkan membuat program untuk database. Performansi ditingkatkan melalui pemakaian memori lokal, program yang telah dikompilasi terlebih dahulu dan pemakaian cache. Alasan lain adalah kebutuhan akan sebuah operasi yang sama sedangkan ada banyak client yang harus mengakses dengan bahasa pemrograman yang berbeda. Stored procedure yang telah dibuat di server database dapat diakses melalui aplikasi program VB.NET, yaitu dengan membentuk objek command dan mengatur nilai dari CommandType untuk diisi dengan StoredProcedure

Function

Di SQL Server, terdapat fasilitas untuk membuat functions. Dengan functions, kita dapat menghasilkan suatu nilai atau value dari parameter-parameter yang kita kirimkan ke dalam function tersebut.

Berikut contoh sederhana pembuatan functions di SQL Server dan cara menggunakannya. Saya mencoba membuat suatu functions baru (model aritmatika penjumlahan sederhana) dengan nama ‘Test‘. Disini saya menggunakan database ‘tempdb’ yang merupakan database bawaan dari SQL Server.  Contoh script-nya adalah sebagai berikut:

CREATE FUNCTION [dbo].[test] ( @X INT, @Y INT )
RETURNS INT
AS
BEGIN
RETURN @X + @Y
END
GO

Saya menggunakan 2 parameter (@X dan @Y) dengan tipe data integer, dan menghasilkan nilai yang bertipe integer pula. Fungsi ini akan menjumlahkan kedua parameter yang dikirim.

Jalankan script diatas, dan kalo berhasil akan muncul pesan : Command(s) completed successfully, dan hasilnya dapat dilihat di dalam folder Programmability –> Functions –> Scalar-valued Functions.

Untuk menjalankannya, tinggal memanggil fungsi tersebut dengan paramater-parameter yang dikirim :

USE [tempdb]
GO
Select dbo.test(4,5) as Hasil

Trigger

Trigger adalah salah satu teknik yang dipakai untuk memanipulasi database langsung tanpa script dari luar SQL. Perubahan isi tabel terjadi pada saat pengguna melakukan transaksi. Transaksi ini biasanya juga berpengaruh terhadap tabel-tabel lain. 

Sebagai contoh: pada saat transaksi penjualan sistem akan menambah rekaman tabel JUAL sebelum perintah INSERT misalnya mengecek jumlah stok di tabel BARANG, dan sesudahnya mengurangi stok di tabel BARANG. Oleh karena itu anda bisa menempatkan trigger yang merupakan prosedur pemicu untuk melakukan proses sesuai dengan keinginan.

Trigger adalah suatu objek dalam basisdata yang terdapat pada bagian tabel. Trigger untuk menempatkan suatu prosedure berkaitan dengan perubahan pada isi tabel. Cara kerja trigger adalah merespon berkaitan dengan perintah DML (INSERT , UPDATE, DELETE) pada tabel.

Adapun jenis perubahan yang dijalankan dalam trigger adalah:

BEFORE INSERT on TABEL1 : menjalankan proses di dalamnya pada saat sebelum melakukan operasi INSERT di TABEL1

AFTER INSERT on TABEL menjalankan proses di dalamnya pada saat sesudah melakukan operasi INSERT di TABEL1

BEFORE UPDATE on TABEL1 menjalankan proses di dalamnya pada saat sebelum melakukan operasi UPDATE di TABEL1

AFTER UPDATE on TABEL1 menjalankan proses di dalamnya pada saat     sesudah melakukan operasi UPDATE di TABEL1

BEFORE DELETE on TABEL1 : menjalankan proses di dalamnya pada saat sebelum melakukan operasi DELETE di TABEL1

AFTER DELETE on TABEL1 : menjalankan proses di dalamnya pada saat sesudah melakukan operasi DELETE di TABEL1


Syntaxnya :

CREATE TRIGGER nama trigger waktu trigger event trigger
ON nama table FOR EACH ROW statement trigger


Penerapannya :
CREATE TRIGGER sdata_insert BEFORE INSERT ON 'sometable'
FOR EACH ROW
       BEGIN
             SET NEW.guid = UUID();
      END;

Contoh nyatanya adalah Audit Trail :

# Example 2: Automatically keep track of edits to the 'members' table above.
# We define three TRIGGERs on 'member' to keep records of
# INSERT, UPDATE, and DELETE actions.

CREATE TABLE member_audit_trail(
event_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
id SMALLINT UNSIGNED DEFAULT NULL,
old_name VARCHAR(32) DEFAULT NULL,
action ENUM('insert', 'update', 'delete') NOT NULL,
name VARCHAR(32) DEFAULT NULL,
changed DATETIME DEFAULT NULL,
PRIMARY KEY (event_id)
);

DELIMITER //

CREATE TRIGGER member_after_update
AFTER UPDATE ON member
FOR EACH ROW
BEGIN
INSERT INTO member_audit_trail
SET action='update', id=OLD.id, old_name=OLD.name, name=NEW.name,
changed=NOW();

END;//

CREATE TRIGGER member_after_insert
AFTER INSERT ON member
FOR EACH ROW
BEGIN
INSERT INTO member_audit_trail
SET action='insert', id = NEW.id, name = NEW.name, changed=NOW();

END;//

CREATE TRIGGER member_after_delete
AFTER DELETE ON member
FOR EACH ROW
BEGIN
INSERT INTO member_audit_trail
SET action='delete', id=OLD.id, old_name=OLD.name, changed=NOW();

END;//

DELIMITER ;

# The edits we made in Example 1 show up in the audit trail.
SELECT * FROM member_audit_trail;
+----------+------+----------+--------+----------+---------------------+
event_id | id | old_name | action | name | changed
+----------+------+----------+--------+----------+---------------------+
1 | 1 | NULL | insert | newton | 2006-06-12 04:16:05
2 | 2 | NULL | insert | albert | 2006-06-12 04:16:05
3 | 3 | NULL | insert | witten | 2006-06-12 04:16:05
4 | 2 | albert | update | einstein | 2006-06-12 04:16:36
+----------+------+----------+--------+----------+---------------------+

Share on Google Plus

About Wahyu Hardianto