TRIGGER dalam SQL

Trigger merupakan store procedure yang dijalankan secara automatis saat user melakukan modifikasi data pada tabel. Modifikasi data yang dilakukan pada tabel yaitu berupa perintah INSERT, UPDATE, dan DELETE.

INSERT , UPDATE dan DELETE bisa digabung jadi satu trigger yang dinamakan Multiple Trigger.

Bentuk dasar perintahnya :

CREATE TRIGGER nama_trigger ON nama_tabel FOR INSERT, UPDATE, DELETE AS isi statement-statement Anda disini. GO

Saya akan memberi contoh sederhana untuk mudah memahami bagaimana cara kerja trigger ini.

Misalkan kita punya data di table DaftarSiswa

DaftarSiswa
Kode	Status
1001
1002
1003

Di tabel yang lain kita juga punya tabel Daftar Nilai :

DaftarNilai
Kode	Nilai

Daftar nilai ini belum ada isinya yang nantinya akan kita isi dengan perintah INSERT.

Kolom Status pada tabel DaftarSiswa akan kita isi dengan “Lulus” dan “Tidak Lulus” secara automatis saat kita melakukan INSERT dan UPDATE pada tabel DaftarNilai.

Cara membuat dan mengisi kedua tabel diatas :

CREATE TABLE daftarSiswa (kode char(4), status char(10))

CREATE TABLE daftarnilai (kode char(4), nilai float)

Pada tabel DaftarSiswa diisi dengan perintah berikut :

INSERT INTO daftarSiswa VALUES(’1001′,”)

INSERT INTO daftarSiswa VALUES(’1002′,”)

INSERT INTO daftarSiswa VALUES(’1003′,”)

Setelah semuanya dijalankan di Query Analyzer, berikutnya kita akan membuat suatu trigger di tabel daftarNilai.

CREATE TRIGGER tr_status      ON daftarnilai FOR INSERT, UPDATE AS DECLARE @kode char(4) DECLARE @nilai float SELECT @kode = kode, @nilai = nilai FROM daftarNilai IF @nilai >= 60 UPDATE daftarSiswa SET status = ‘Lulus’ WHERE kode=@kode ELSE UPDATE daftarSiswa SET status = ‘Tidak Lulus’ WHERE kode=@kode go

Untuk menganalisa lihat isi kedua tabel diatas dengan perintah

SELECT* FROM daftarSiswa

SELECT* FROM daftarNilai

Setelah Anda melihat hasilnya kemudian jalankan perintah dibawah ini :

INSERT INTO daftarNilai values(’1001′,70)

INSERT INTO daftarNilai values(’1002′,50)

INSERT INTO daftarNilai values(’1003′,80)

Setelah dijalankan Anda bisa lihat kembali isi tabel daftarSiswa dan daftarNilai.

DaftarSiswa
Kode	Status
1001	Lulus
1002	Tidak Lulus
1003	Lulus

DaftarNilai
Kode	Nilai
1001	70
1002	50
1003	80

Disini terlihat saat kita melakukan perintah INSERT di tabel daftarNilai secara otomatis program trigger melakukan pengisian pada tabel daftarSiwa pada kolom “status”

Cukup sampai disini dulu penjelasan saya mengenai TRIGGER.

CURSOR EKSPLISIT dan IMPLISIT

Cursor adalah suatu variable dalam blok PL/SQL yang tidak mengijinkan seorang programer membuat query yang menghasilkan lebih dari satu baris untuk ditampung di dalam nya. Cursor ada 2 macam yaitu :
a. Cursor Eksplisit : Cursor yang harus dideklarasikan terlebih dahulu sebelum digunakan, sedangkan
b. Cursor Implisit : Cursor yang tidak perlu dideklarasikan dulu pada declare section. contoh cursor implisit diasosiasikan dengan perintah SELECT, INSERT, DELETE, dan UPDATE.

Bentuk Umum Sintaksnya :

DOFC (DECLARE, OPEN, FETCH, CLOSE)

Declare
cursor [nama_cursor_bebas] is
select [daftar_kolom]
from [daftar_tabel]
where [syarat];
begin
open [nama_cursor_bebas];
loop
fetch [nama_cursor_bebas] into [daftar_variabel];
exit when [nama_cursor_bebas] %notfound;
[aksi_lain]
end loop;
close [nama_cursor_bebas];
end;
/

Contoh dengan menggunakan Cursor Eksplisit:
1. Memunculkan Nama Depan, Email, No.Telepon dari sebuah karyawan yang berada di tabel HR

declare
nama_cari employees.first_name%type;
nama_depan employees.first_name%type;
email employees.email%type;
nomor_telepon employees.phone_number%type;


cursor jojo is

select first_name, email, phone_number
from employees
where first_name=nama_cari;

begin
nama_cari:='&masukkan_nama';

open jojo;

loop
fetch jojo into nama_depan,email,nomor_telepon;
exit when jojo%notfound;
dbms_output.put_line('Nama : '||nama_depan);
dbms_output.put_line('Email : '||email||'');
dbms_output.put_line('Nomor Telepon : '||nomor_telepon);

end loop;

close jojo;
end;


2. Contoh dengan menggunakan Cursor Implisit :
Memunculkan Nama Depan, Pekerjaan, Gaji dari sebuah karyawan yang berada di tabel HR

eclare
nama_cari employees.first_name%type;
nama_depan employees.first_name%type;
pekerjaan jobs.job_title%type;
gaji employees.salary%type;

begin
nama_cari:='&masukkan_nama';
select first_name, job_title,salary
into nama_depan,pekerjaan,gaji
from employees e join jobs j
on(e.job_id=j.job_id)
where first_name=nama_cari;
dbms_output.put_line('Nama '||nama_depan|| ' Mempunyai Pekerjaan ' ||pekerjaan||' Dengan Gaji USD '||gaji);

end;
/

Procedure dan Function

CREATE OR REPLACE PROCEDURE procedure_name

(parameter1 mode1 datatype1,

parameter2 mode2 datatype2,. . .)

IS

Daftar variabel lokal jika ada

BEGIN

…

END;

/

Note:

Tidak wajib ada, sesuaikan dengan kebutuhan

Daftar parameter tidak wajib ada, sesuaikan dengan kubutuhan

Pilihan mode: IN atau OUT atau IN OUT, defaultnya adalah IN

Selain IS boleh juga pake AS.

CONTOH dengan Parameter:

create or replace procedure proc1 (x in number)

 is

BEGIN

FOR i  in reverse 1..x LOOP

If  mod(i,2) = 1 then

DBMS_OUTPUT.PUT_LINE( 'Tes' || i );

exit when x = 1;

End if;

End loop;

END;

/

exec  proc1 (5);

output: tes 5

             tes 3

             tes 1

CONTOH tanpa Parameter:

CREATE OR REPLACE PROCEDURE add_dept IS

dept_id departments.department_id%TYPE;

dept_name departments.department_name%TYPE;

BEGIN

dept_id:=280;

dept_name:='ST-Curriculum';

INSERT INTO departments (department_id,department_name)

VALUES(dept_id,dept_name);

DBMS_OUTPUT.PUT_LINE(' Berhasil menginsert'||

SQL%ROWCOUNT ||' baris ');

END;

/

exec add_dept(...);

SYSNTAX FUNCTION

CREATE OR REPLACE FUNCTION function_name

(argument1 mode1 datatype1,

argument2 mode2 datatype2,

. . .) RETURN datatype

IS

Daftar variabel lokal jika ada

BEGIN

…

RETURN …. ;

END;

/

Note:

Tidak wajib ada, sesuaikan dengan kebutuhan

Daftar argumen tidak wajib ada, sesuaikan dengan kubutuhan

Pilihan mode: IN atau OUT atau IN OUT, defaultnya adalah IN

Selain IS boleh juga pake AS .

CONTOH Function:

create or replace function bilangan(x in number) return varchar is

begin

if (x>0) and (x mod 2 = 1) then

return 'ini angka ganjil positif';

elsif (x>0) and (x mod 2 = 0) then

return 'ini angka genap positif';

elsif (x<0) then

return 'ini angka negatif';

elsif (x=0) then

return 'ini angka nol';

end if;

end;

/

select bilangan(25) from dual;

(inputan angka 25 bisa diganti inputan angka yang lain)

output ganjil positif;

Contoh dengan Parameter:

CREATE OR REPLACE FUNCTION check_sal(empno employees.employee_id%TYPE)

RETURN Varchar IS

dept_id employees.department_id%TYPE;

sal employees.salary%TYPE;

avg_sal employees.salary%TYPE;

BEGIN

SELECT salary,department_id INTO sal,dept_id

FROM employees WHERE employee_id=empno;

SELECT avg(salary) INTO avg_sal FROM employees

WHERE department_id=dept_id;

IF sal > avg_sal THEN

RETURN 'Gaji Di Atas Rata-Rata';

ELSE

RETURN 'Gaji Di Bawah Rata-Rata';

END IF;

EXCEPTION

WHEN no_data_found THEN

RETURN 'ID Karyawan tidak ditemukan';

END;

/

Tag yang berhubungan : DML-data manipulation language

DML

           DML (Data Manipulation Language) yaitu bahasa / perintah sql yang digunakan untuk memanipulasi data seperti menampilkan data, menambah / mengisi data, mengubah data dan menghapus data. Yang termasuk dalam perintah ini adalah SELECT, INSERT, UPDATE, dan DELETE.

INSERT

Perintah INSERT digunakan untuk menambahkan / menginputkan data ke dalam tabel. Sintak secara umum adalah seperti berikut:

INSERT INTO Nama_Tabel(Daftar_Kolom) VALUES (Daftar_Nilai) Yang perlu diperhatikan dalam perintah insert adalah sebagai berikut:
a. Jika tipe data yang akan diinputkan berupa string / karakter (Char,Varchar, Text) maka gunakan tanda    petik tunggal untuk mengapit data tersebut.
b. Jika tipe data numeric / angka (Int, Numeric, Decimal) maka tidak boleh menggunakan tanda petik tunggal.
c. Jika tipe data Datetime maka gunakan petik tunggal dengan format ‘mmdd-yyyy’ (untuk setting tanggal Inggris/Amerika) atau ‘yyyy-mm-dd’.
d. Jika data autonumber maka data tidak perlu diisi.

Ada beberapa cara penulisan sintak insert, yaitu:
a. Tanpa menyebutkan daftar kolom, dengan catatan data yang diinputkan harus terurut sesuai dengan urutan  kolom dan semua data harus diisi. kecuali untuk kolom autonumber data tidak diisikan sebab data akan diisi

oleh system secara otomatis. Contoh INSERT tanpa menyebutkan daftar kolom:
INSERT INTO Jurusan VALUES ('S1TI','Teknik Informatika Strata1','Ir. Abbas Ali Pangera')
b. Menyebuatkan daftar kolom yang akan diisi saja datanya dengan catatan jumlah kolom harus sama dengan jumlah data yang akan diisikan.
Contoh:
INSERT INTO Kelas (Kode_Kelas, Nama_Kelas) VALUES ('S1TI3E','
S1 TI Semester 3')


SELECT
Perintah SELECT digunakan untuk menampilkan data dalam tabel. Secara umun sintaknya adalah sebagai berikut:
SELECT Daftar_Kolom FROM Nama_Tabel WHERE Kondisi ORDER
BY Kolom
Contoh :
a. Menampilkan Data Mahasiswa SELECT * FROM Mahasiswa
Ket : Tanda asterisk ( * ) menandakan bahwa semua kolom akan ditampilkan
b. Menampilkan Nim dan Nama Mahasiswa SELECT NIM, Nama FROM Mahasiswa

SELECT Dengan Klausa WHERE
WHERE digunakan untuk membatasi hasil SELECT yang ditampilkan
sesuai kondisi yang ditentukan. Secara umum sintaknya adalah sebagai berikut:
SELECT Daftar_Kolom FROM Nama_Tabel WHERE Kondisi.


OPERATOR      ARTINYA
        =               Sama dengan
        >                Lebih dari
        <                Kurang dari

        <=             Lebih dari atau sama dengan
       >=              Kurang dari atau sama dengan
       <>              Tidak sama dengan
Contoh :
SELECT * FROM Mahasiswa WHERE NIM =’06.11.0123’


Operator logika

OR
Akan menampilkan data jika salah satu atau lebih syarat terpenuhi.
Contoh :
SELECT * FROM Mahasiswa WHERE NIM = ’06.11.0123’ OR Agama = ‘Islam’

AND
Akan menampilkan data jika semua syarat terpenuhi.
Contoh :
SELECT * FROM Mahasiswa WHERE NIM = ’06.11.0123’ AND Agama = ‘Islam’

NOT
Akan menampilkan data yang sebaliknya (negasinya)
Contoh :
SELECT * FROM Mahasiswa WHERE NOT NIM = ’06.11.0123'

Tag yang berhubungan : Pengenalan DBMS

Pengenalan DBMS

Basis Data (Database) adalah suatu kumpulan informasi yag disimpan dalam komputer secara sistematik, sehingga dapat depirksa menggunakan suatu program komputer untuk memperoleh datan dari basis data tersebut. Untuk mengelola database diperlukansuatu perangkat lunak yang disebut sebagai DBMS (Database Management System) 

DBMS DAN KEUNTUNGANNYA

Secara umum DBMS diartikan sebagai suatu program komputer ang digunakan untuk memasukan, mengubah, menghapus, memanipulasi, dan memperoleh data / informasi dengan praktis dan efisien.

Keunggulan DBMS :
1. Kepraktisan
2. Kecepatan
3. Mengurangi kejemuan
4. Kekinian
Komponen Utama DBMS

Komponen utama DBMS dapat dibagi menjadi empat macam :
1. Perangkat keras,
2. Data, Data dalam basis data mempunyai sifat terpadu (integrated) dan berbagi (shared)

3. Perangkat Lunak, dan
4. Pengguna.

MODEL BASIS DATA

1. Model Hirarkis / Model Pohon
2. Model Jaringan
3. Model Relasional

Model Relasional merupakan model yang paling sederhana sehingga mudah digunakan dan dipahami oleh pengguna, serta merupakan model yang paling populer saat ini.
Model ini menggunakan sekumpulan tabel berdimensi dua ( yang disebut relasi atau tabel ), dengan masing-masing relasi tersusun atas tupel atau baris dan atribut.
DBMS yang bermodelkan relasional biasa disebut RDBMS (Relational Data Base Management System).
Ada Beberapa Sifat yang melekat pada suatu relasi :1. Tak ada tupel (baris) yang kembar)
2. Urutan tupel tidaklah penting
3. Setiap atribut memiliki nama yang unik
4. Letak atribut bebas ( urutan atribut tidak penting)
5. Setiap atribut memiliki nilai tunggal dan jenisnya sama untuk semua tupel.

Pada model relasional, jumlah tupel suatu relasi disebut kardinalitas dan jumlah atribut suatu relasi disebut derajat (degree) atau terkadang disebut arity. Relasi yang berderajat saru (hanya memiliki satu atribut) disebut unary. Relasi yang berderajat dua disebut binary dan relasi yang berderajat tiga disebut ternary. Relasi yang berderajat n disebut n-ary. Istilah lainnya yang terdapat pada model relasional adalah domain. Domain adalah himpunan nilai yang berlaku bagi suatu atribut.

Contoh produk DBMS terkenal yang menggunakan model relasional antara lain adalah :1. DB2 (IBM)
2. Rdb/VMS (Digital Equipment Corporation)
3. Oracle (Oracle Corporation)
4. Informix (Informix Corporation)
5. Ingres (ASK Group Inc)
6. Sybase (Sybase Inc)

Di lingkungan PC, produk-produk berbasis relasional yang cukup terkenal antara lain adalah :
1. Keluarga R:Base (Microrim Corp) antara lain berupa R:Base 5000
2. Keluarga dBase (Ashton-Tate, sekarang bagian dari Borland International), antara lain dbase III Plus, dBase IV, serta Visual dBase
3. Microsoft SQL ( Microsoft Corporation)
4. Visual FoxPro (Microsoft Corporation)

MACAM-MACAM PERINTAH DATA BASE

1. Bahasa Definisi Data (Data Definition Language/ DDL)DDL adalah perintah-perintah yang biasa digunakan ileh administrator basis data (DBA) utnuk mendefinisikan skema ke DBMS. Skema adalah deskripsi lengkap tentang struktur medan, rekaman, dan hubungan data pada basis data
Index merupakan suatu mekanisme yang lazim digunakan pada basis data, yang memungkinkan pengambilan data dapat dilakukan dengan cepat.

2. Bahasa Manipulasi Data (Data Manipulation laguage/ DML)DML adalah perintah-perintah yang digunakan untuk mengubah , mamnipulasi dan mengambil data pada basis data. Tindakan seperti menghapus, mengubah, dan mengambil data menjadi bagian dari DML. DML pada dasarnya dibagi menjadi dua :
- Prosedural, yang menuntut pengguna menentukan data apa saja yang diperlukan dan bagaimana cara mendapatkannya.
- Nonprosedural, yang menuntut pengguna menentukan data apa saja yang diperlukan, tetapi tidak perlu menyebutkan cara mendapatkannya.

3. DQL ( Data Query Language)Query sesungguhnya berarti pertanyaan atau permintaan. Istilah ini tetap dipertahankan dalam bentuk asli, karena telah populer di kalangan pengguna DBMS di Indonesia.

Tag yang berhubungan : DML-data manipulation language