TRIGGER, PROCEDURE, FUNCTION

Trigger

Trigger adalah blok PL/SQL yang disimpan dalam database dan akan diaktivasi ketika kita melakukan statement-statement SQL (DELETE, UPDATE, dan INSERT) pada sebuah tabel. Aktivasi trigger didasarkan pada event yang terjadi di dalam tabel tersebut sehingga trigger dapat membantu dalam menjaga integritas dan konsistensi data. Implementasi trigger yang sering ditemui dalam dunia nyata adalah untuk mengeset dan mengubah nilai kolom dalam suatu tabel sehingga validasi nilai dari tabel tersebut akan terjaga. Adanya trigger dalam database akan meringankan kita dalam pembuatan aplikasi karena di dalam aplikasi yang kita buat, kita tidak perlu lagi untuk melakukan validasi data.

Syntax:

CREATE [or replace] TRIGGER nama_trigger

( BEFORE|AFTER ) ( INSERT|UPDATE [OF nama_kolom,..]|DELETE )ON nama_tabel

[FOR EACH ROW] [WHEN (kondisi)]

DECLARE
Deklarasi tipe data

BEGIN
Trigger body

END;
/

Anonymous Block

Block PL/SQL tak bernama (anonymous) menyimpannya dalam Oracle Precompiler.
Anonymous block tidak dibuat (CREATE) dan tak disimpan pada database, biasanya dijalankan dari dalam aplikasi, pada run-time aplikasi akan mengirimkan anonymous PL/SQL block ini kepada database oracle, dimana anonymous block tsb di compile dan dieksekusi.

Syntax:

DECLARE
Deklarasi variabel

BEGIN
Program prosedural

EXCEPTION
Penangananan eksepsi (ERROR)

END;
/

SUBPROGRAM

PL/SQL Block yang dipanggil dengan sekumpulan parameter. PL/SQL memiliki dua jenis subprogram yaitu: procedure dan function. Dimana secara umum procedure

Syntax:

Prosedur

CREATE OR REPLACE PROCEDURE AS

Deklarasi variabel

BEGIN
Prosedural & sql

EXCEPTION
Penanganan eksepsi (ERROR)

END ;
/

Fungsi

CREATE OR REPLACE FUNCTION RETURN

AS
 Deklarasi variabel

BEGIN
procedural and SQL

EXCEPTION
penanganan eksepsi (ERROR)

RETURN();
kalusa return ini wajib di deklarasikan

END;
/

Contoh Kasus - Integritas Data

Berikut ini adalah jenis-jenis integritas data yang disertai dengan contoh kasusnya :

• Create Domain,  yaitu membuat tipe data baru. Fungsi ini biasa digunakan untuk menangani data yang membutuhkan aturan bisnis sendiri,
• Contohnya : membuat tipe data “gender” untuk yang isinya hanya boleh berisi pria atau wanita ->
• Maka sintaksnya dalam SQL adalah :
  create domain gender char(2) constraint gender_domain check ( value in(‘pria’,'wanita’));
• Not NULL, yaitu menjaga agar suatu data tidak kosong dengan kata lain harus ada value-nya.
• Contohnya nama matakuliah pada tabel mata kuliah tidak boleh kosong.
• Maka sintaksnya dalam pl / sql :
• nama_matkul varchar2(15),
• constraint nn_ matkul not null (nama_matkul)
• Unique, yaitu membuat agar suatu data tidak memiliki nilai yang sama dengan record lainnya dalam tabel yang sama.
• Contohnya email pada tabel akun_facebook hanya boleh satu email untuk satu akun.
• Sintaksnya :
• email varchar2(15),
• constraint u_mail unique (email)
• Primary key, merupakan gabungan antara not null dan unique.
• Misalnya NIM pada tabel mahasiswa.
• nim char(7) not null,
• constraint pk_ nim primary key (nim)
• Check, memeriksa data dengan aturan bisnisinya sendiri tanpa membuat tipe data baru.
• Contohnya sebelum memasukkan nilai ujian pada tabel nilai, data diperiksa terlebih dahulu. Data tidak boleh masuk jika bernilai negatif.
• nilai number,
• constraint chk_nilai check (nilai > 0)
• Referential Integrity, adalah integritas pada relasi antar tabel. Contohnya jika kolom NIM mahasiswa pada tabel nilai mengacu ke kolom NIM pada tabel mahasiswa, dan terjadi perubahan (delete atau update) pada NIM di tabel mahasiswa, maka ada beberapa pilihan yang dapat dilakukan terhadap NIM yang di tabel nilai, diantaranya adalah :
• Delete cascade, hapus record pada kedua tabel yang bersangkutan
• Delete set null, hapus record di tabel asal dan value record pada tabel yang mengacu di jadikan NULL
• Update cascade, perbarui isi record pada kedua tabel
• nim char(7),
• constraint fk_nim foreign key (nim) references mahasiswa (nim) on delete cascade / on delete set   null /  on update cascade 

Tipe-tipe Integritas Data

Bagian ini menggambarkan aturan yang dapat diterapkan pada kolom tabel ayang menekankan perbedaan tipe data pada integritas data.

1. Null Rule

Aturan null  adalah definisi aturan pada single column yang membolehkan atau tidak membolehkan inserts atau updates untuk pengisian rows kosong (the absence of a value) pada kolom ini.

2. Unique Column Values

Aturan nilai unique didefinisan pada sebuah column (or set of columns) yang membolehkan insert or update hanya pada row jika itu berisi  sebuah nilai unique dalam sebuah kolom (or set of columns).

3. Primary Key Values

Aturan nilai primary key didefinisikan pada sebuah key (a column or set of columns) tertentu bahwasetiap each row dalam table dapat mengidentifikasi keunikan dengan nilai kunci tersebut

4. Referential Integrity Rules

Aturan referential integrity adalah definsi aturan pada sebuah kunci key (a column or set of columns) dalam sebuah table yang menjamin bahwa data dalam kunci cocok dengan nilai dalam sebuah relasi table (the referenced value).

Aturan-Aturan Referential Integrity

Referential integrity mengacu pada kaitan antar row/record dari 2 tabel yang berhubungan melalui Foreign Key yang bersangkutan.

Pada insert, record harus dimasukkan di tabel utama dahulu, kemudian baru di tabel kedua. Pada delete, record harus dihapus di tabel kedua dahulu, kemudian baru di tabel utama. Field yang dihubungkan dari tabel utama haruslah berupa Primary Key.
Kedua field yang saling behubungkan harus memiliki Tipe data dan lebar data yang sama. Tidak dibenarkan penghapusan record pada tabel utama yang telah dihubungkan dengan tabel yang direlasikan.

Macam action dalam Referential Integrity:

NO ACTION atau RESTRICT : update atau delete tidak dilakukan. Ini merupakan pilihan default.

CASCADE : nilai kolom di tabel kedua disesuaikan dengan nilai kolom di tabel utama
SET NULL : nilai kolom di tabel kedua dijadikan NULL

SET DEFAULT : nilai kolom di tabel kedua dijadikan nilai DEFAULT (nilai DEFAULT harus ditentukan pada waktu pembuatan tabel).

Penggolongan Integritas Data

Integritas data dapat dibedakan menjadi beberapa jenis sebagai berikut:

a. Integritas Entiti

Integritas entiti mendefinisikan sebuah baris sebagai sebuah entiti unik pada suatu tabel. Entity integritas memaksa integritas dari column atau primary key dari suatu tabel (melalui index, unique, constraints, primary key). 

Primary  key  tidak  boleh null.

Definisi kunci primer merupakan salah satu aturan yang mengatur integritas entiti, yaitu :

·       Kunci primer harus ada dan mewakili setiap kejadian pada entiti

·       Kunci primer harus unik untuk setiap kejadian dalam entiti, atau juga melalui Definisi index

Integritas ini merupakan aturan yang menyatakan bahwa setiap table harus memiliki primary key dan setiap kolom primary key tersebut haruslah unique dan not null.

Integritas Entitas terutama meliputi bentuk :

Domain

Misalkan, dalam penerapan model basis data relasional, tidak ada suatu atribut primary key yang memiliki nilai NULL, artinya bahwa setiap atribut primary key harus memiliki nilai tertentu.

Key

Fungsi key pada suatu relasi dapat besifat UNIQUE (hanya satu tuple yang diperbolehkan terkait dengan satu nilai kunci), dan NON UNIQUE (beberapa tuple diperbolehkan untuk terkait dengan satu nilai kunci yang sama).

Dalam tabel setiap baris harus mempunyai identitas yang unik yang disebut dengan primary key. Hubungan antara primary key dan foreign key menyatakan apakah sebuah baris table dapat diubah atau di hapus.

Perintah yang di gunakan dalam sql :

·       PRIMARY KEY

·       UNIQUE KEY

·       UNIQUE INDEX

·       IDENTITY

a.  Integritas entititas mendefinisikan sebuah baris sebagai sebuah entititas yang unik untuk suatu tabel

b.  Integritas entititas memaksa integritas dari coloumn atau primary key dari suatu tabel melalui index,unique,constrain,primary key,dimana primary key tidak boleh null

c.  Dalam Inegritas entititas tidak ada baris yang duplikat di dalam suatu tabel.

contoh : create tabel pembelian ( ID pembelian smallint,ID model smallint,Deskripsi model varchar(40).Primary key (ID pembelian),unique (ID pembelian,ID model).

Mendefinisikan sebuah baris sebagai entitas yang untuk untuk sebuah tabel.

b. Integritas Domain

Integritas domain mengacu pada rentang nilai data yang valid pada sebuah kolom. Integritas domain bisa dilakukan dengan membuat paksaan pada nilai atribut, baik untuk tipe data, format data ataupun rentang data yang diijinkan untuk tersimpan pada sebuah kolom. Dan dalam implementasinya bisa dilakukan dengan membuat sebuah rule atau memberikan paksaan untuk memvalidasi data yang akan disimpan.

Menyatakan bahwa nilai data sesuai dengan kenyataannya. System dalam hal ini melakukan CHECK sesuai dengan aturan (rule) yang berlaku.
Batasan dan implementasi :

·      DEFAULT

·      CHECK

·      FOREIGN KEY

·      Tipe data, default, rules

Domain Integritas merupakan validasi dari masukan untuk sebuah kolom. Anda dapat memaksa domain integritas dengan membatasi tipe (melalui data types), format (melalui check constraints dan rules), atau range nilai-nilai yang mungkin (melalui Foreign Key Constraints, Check Constraints, Default Definitions dan rules).

a.  Domain adalah nilai-nilai yang dimungkinkan diasosiasikan dengan setiap atribut.

b.  Integritas domain merupakan validasi dari masukkan sebuah kolom.

c.  Kita dapat memaksa Integritas domain dengan memaksa tipe (melalui data types),format (melalui check constrain dan rules),atau range nilai-nilai yang mungkin(melalui foreign key constrain,check constrain,definitions and rules).

d.  Dengan Integritas domain tidak ada data yang melanggar jangkauan nilai di tiap kolom data.

e.  Pada saat “create tabel”  kita bisa mencegah kolom bernilai null dengan mengunakan “constrain null” yang berlaku untuk kolom

f.  Bila tidak dinyatakan,SQL akan mengasumsikan kondisi nulol diijinkan kecuali suatu kolom dispesifikasikan sebagai bagian dari kunci utama yang dinyatakan dengan primary key.

g,Supaya integritas entititas tetap terjaga maka lebih baik jika constrain “not null” dinyatakan.

Melakukan valisadi dalam masukan yang di tujukam dalam sebuah kolom.

Dimana jenis domainnya adalah :

-          Membatasi tipe

-          Format

-          Serta range nilai – nilai

Integritas ini menetapkan bahwa setiap elemen dari relasi harus sesuai dengan jenis dan batasan atribut elemen tersebut.

c. Integritas Referensial

Integritas referensial menjaga relasi yang telah didefinisikan antar tabel karena terjadi operasi penyisipan, penghapusan dan pemodifikasian pada record dalam suatu relasi. Fokusnya adalah pengaruh terhadap nilai kunci primer atau kunci tamu yang harus selalu dijaga konsistensinya. Sehingga tidak akan pernah terjadi bahwa terdapat kunci pada tabel induk yang berelasi dengan nilai yang kosong dan jika nilai kunci berubah maka seluruh referensi terhadapnya berubah pula secara konsisten. Untuk menjaga integritas referensial bisa digunakan operasi pemicuan (triggering operations) yang akan mengatur akibat umum dari operasi penyisipan, penghapusan, pembaharuan dan operasi pembacaan pada entiti lain atau pada atribute lain dalam entiti yang sama.

Batasan dan implementasi :

·         CHECK

·         FOREIGN KEY

·         TRIGGERS DAN STORED PROCEDURE

Integritas Referensial adalah memberikan jaminan terhadap suatu nilai atribut pada satu relasi, dan juga nilai atribut tersebut terdapat pada relasi yang lain. Integritas Referensial sering disebut juga sebagai foreign key yang terdapat pada suatu relasi. Jika foreign key pada suatu relasi diberlakukan, maka harus diperhatikan beberapa konsekuensi sebagai berikut : setiap foreign key harus cocok dengan satu nilai candidate key dari tuple pada relasi dasarnya, dan foreign key tidak boleh memiliki nilai NULL.

Sebagai contoh dalam penanganan integritas, untuk menangani proses update dan delete pada satu key : Jika primary key bukan sebagai foreign key, maka operasi proses update dan delete dapat dilakukan. Tetapi jika primary key merupakan foreign key, maka operasi proses update dan delete tidak dapat dilakuk  an, atau dapat dilakukan dengan cara melakukan pengaturan nilai foreign key ke nilai NULL atau ke nilai tertentu yang telah disepakati.

a.  Integritas referensial memastikan bahwa seluruh nilai dari foreign key cocok dengan primaruy key yang dihubungkanya.

b.  Integritas referensial adalah dasar relasi antar tabel yaitu antara foreign key dan primary key.

c.  Data pada foreign key harus sesuai dengan primary key artinya tipe data dan ukuran sama dan konsistensi tetap terjaga ketika ada penghapusan,penggantian data,dan penambahan data pada tabel.

Aturan-aturan pada Integritas Referensial :

Aturan-aturan pada integritas referensial yang digunakan pada sebagian besar RDBMS adalah bervariasi, dan di bawah ini terdapat aturan-aturan umum dan mendasar yang bisa digunakan untuk menjaga integritas referensial pada data :

1. Aturan Penyisipan

Aturan penyisipan menentukan kondisi valid saat penyisipan pada entiti anak, apakah terdapat larangan saat menyisipkan pada entiti anak terhadap persekutuan dengan entiti induknya.

Klasifikasi paksaan penyisipan mempunyai 6 tipe :

• Ketergantungan(dependent) : penyisipan pada entiti anak akan diijinkan jika telah cocok (matching) dan nilai data terdapat pada nilai induk.·        
• Otomatis : penyisipan pada entiti anak selalu diijinkan, seandainya tidak ada yang cocok dengan entiti induk maka secara otomatis akan diciptakan penyisipan pada entiti induk.·        
• Kosong (nulify) : penyisipan pada entiti anak selalu diijinkan, seandainya tidak ada yang cocok dengan entiti induk, maka kunci tamu pada entiti induk akan dikosongkan atau diisi dengan null.·        
• Default : penyisipan pada entiti anak selalu diijinkan, seandainya tidak ada yang cocok dengan entiti induk maka kunci tamu pada entiti induk diisi dengan suatu nilai default.·        
• Customized : penyisipan pada entiti anak terjadi hanya jika ada paksaan validasi yang telah dibuat.·        
• Tidak berefek (no effect): penyisipan pada entiti anak selalu diijinkan tanpa perlu ada yang cocok pada entiti induk, jadi tidak diperlukan validasi pengujian.

2. Aturan Penghapusan

Aturan penghapusaan menentukan kondisi valid dimana dapat menghapus entiti induk, apakah terdapat larangan saat menghapus pada entiti induk terhadap persekutuan dengan entiti anaknya.

Klasifikasi paksaan dari penghapusan mempunyai enam tipe :

• Larangan (restrict) : penghapusan pada entiti induk diijinkan jika tidak ada yang cocok (matching) dengan entiti anak, jika hal ini dilanggar maka akan mengakibatkan yang disebut dengan ‘orphaned’ record pada entiti anak, dimana terdapat record pada entiti anak yang kehilangan relasi terhadap entiti induknya.·        
• Bertingkat (cascade) : penghapusan pada entiti induk selalu diijinkan dan secara bertingkat akan melakukan penghapusan pada entiti anak yang cocok dengan yang dihapus pada entiti induk.·       
• Kosong (nulify) : penghapusan pada entiti induk selalu diijinkan dan kunci tamu pada entiti anak yang cocok dengan yang dihapus akan diisi dengan null.·       
• Default : penghapusan pada entiti induk selalu diijinkan dan kunci tamu pada entiti anak yang cocok dengan yang dihapus akan diisi dengan suatu nilai default.·        
• Customized : penghapusan pada entiti induk terjadi hanya jika paksaan validasi yang telah dibuat.·      
• Tak berubah (no effect), penghapusan pada entiti induk selalu diijinkan dan tidak diperlukan pengujian validasi pada entiti anak.

3. Hal-hal lain yang berkaitan dengan integritas referensial

Pada bagian sebelumnya telah dibahas tentang dua buah aturan yang berhubungan dengan integritas referensial, tetapi ada beberapa hal yang perlu diperhatikan seperti :

• Hindarilah menggunakan penyisipan dan penghapusan menggunakan aturan kosong(nulify), sebaiknya gunakanlah sebuah tanda dengan memakai aturan default. Nilai null adalah nilai khusus yang menunjukkan “nilai tidak diketahui” atau “tidak dapat dipakai”. Nilai NULL tidak sama dengan nilai nol atau blank. Kode instruktur yang yang berisi NULL tidak sama dengan kode instruktur yang berisi nol (kode instruktur = 0). Kode instruktur yang berisi null mungkin berarti kode tersebut tidak teridentifikasi.·        
• Jangan pernah mendefinisikan satu aturan penyisipan dan penghapusan yang kosong (nulify) bila kunci tamu juga menjadi bagian dari kunci primer pada entiti anak. Pada keadaan khusus aturan ini terjadi pada subtipe, dimana kunci primer dan kunci tamu adalah satu dan sama.·        
• Selalu definisikan aturan penyisipan untuk satu supertipe dan subtipe relationship sebagai satu versi aturan otomatis atau tergantung (dependent).·        
• Definisikan aturan penghapusan untuk relationship sebagai bertingkat (cascade).·
• Integritas yang didefinisikan sendiri oleh user mengijinkan user untuk mendefinisikan business rules spesifik yang digunakan untuk mendukung ketiga aturan integritas data sebelumnya.

d. Integritas User Defined

User–defined integritas mengizinkan kita untuk menentukan spesific business rules sendiri yang tidak sama pada kategori integrity yang lain.

Untuk jenis yang satu ini, menetapkan aturan sesuai keinginan user sendiri, misal untuk umur harus diantara 0 sampai 17, selain itu tidak diperbolehkan.

Sesuai dengan aturan logika dan bisnis yang ada maka pemakai dapat menetapkan batasan dan aturan dari data yang disimpan.

Batasan dan implementasi :

·         Batasan melalui tabel dan kolom pada saat pembuatan tabel.

·         FOREIGN KEY

·         TRIGGERS DAN STORED PROCEDURE

Kemampuan untuk menghindari operasi-operasi yang tidak valid, yang dibawa oleh data. Sebagai contoh, perkalian antara gaji seorang staf dengan biaya sewa, meskipun kedua atribut tersebut mempunyai tipe data numeric yang sama.

Secara umum integritas konstrain dapat berfungsi sebagai unsur-unsur penting untuk mempertahankan keutuhan basis data, namun disadari bahwa unsur ini tidak semuanya memberikan kemudahan mekanisme untuk dilakukan test. Sehingga integritas yang dilakukan sejauh ini hanyalah pada kondisi yang dapat dilakukan test untuk meminimalkan overhead.