Oracle Pemicu PL/SQL: Sebagai pengganti & Tipe Gabungan

Apa itu Pemicu di PL/SQL?

Pemicu disimpan PL / SQL program yang diaktifkan oleh Oracle mesin otomatis saat Pernyataan DML Seperti halnya operasi insert, update, dan delete, operasi-operasi tersebut dijalankan pada tabel, atau ketika beberapa kejadian terjadi. Kode yang akan dijalankan dalam kasus trigger dapat didefinisikan sesuai kebutuhan. Anda dapat memilih kejadian yang menyebabkan trigger perlu diaktifkan dan waktu eksekusinya. Tujuan dari trigger adalah untuk menjaga integritas informasi dalam database.

Manfaat Pemicu

Berikut ini adalah manfaat pemicu.

• Menghasilkan beberapa nilai kolom turunan secara otomatis
• Menegakkan integritas referensial
• Pencatatan peristiwa dan penyimpanan informasi pada akses tabel
• Audit
• Syncreplikasi tabel yang mengerikan
• Memaksakan otorisasi keamanan
• Mencegah transaksi tidak valid

Jenis Pemicu di Oracle

Pemicu dapat diklasifikasikan berdasarkan parameter berikut.

Klasifikasi berdasarkan waktu

• SEBELUM Pemicu: Pesan tersebut terpicu sebelum peristiwa yang ditentukan terjadi.
• SETELAH Pemicu: Fungsi ini akan dijalankan setelah peristiwa yang ditentukan terjadi.
• BUKAN Pemicu: Tipe khusus. Anda akan mempelajari lebih lanjut di topik-topik selanjutnya. (hanya untuk DML)

Klasifikasi berdasarkan level

• Pemicu tingkat PERNYATAAN: Fungsi ini dijalankan satu kali untuk pernyataan kejadian yang ditentukan.
• Pemicu tingkat BARIS: Fungsi ini dijalankan untuk setiap record yang terpengaruh dalam event yang ditentukan. (hanya untuk DML)

Klasifikasi berdasarkan peristiwa

• Pemicu DML: Fungsi ini dijalankan ketika peristiwa DML ditentukan (INSERT/UPDATE/DELETE).
• Pemicu DDL: Fungsi ini dijalankan ketika peristiwa DDL ditentukan (CREATE/ALTER).
• Pemicu BASIS DATA: Fungsi ini akan aktif ketika peristiwa basis data ditentukan (LOGON/LOGOFF/STARTUP/SHUTDOWN).

Jadi, setiap pemicu merupakan kombinasi dari parameter-parameter di atas.

Cara Membuat Pemicu

Berikut adalah sintaks untuk membuat pemicu. Tangkapan layar di bawah ini menunjukkan sintaks pembuatan pemicu ini. Oracle.

CREATE [ OR REPLACE ] TRIGGER <trigger_name> 

[BEFORE | AFTER | INSTEAD OF ]

[INSERT | UPDATE | DELETE......]

ON<name of underlying object>

[FOR EACH ROW] 

[WHEN<condition for trigger to get execute> ]

DECLARE
<Declaration part>
BEGIN
<Execution part> 
EXCEPTION
<Exception handling part> 
END;

Penjelasan Sintaks:

• Sintaks di atas menunjukkan pernyataan opsional berbeda yang ada dalam pembuatan pemicu.
• SEBELUM/SESUDAH akan menentukan waktu acara.
• MASUKKAN/PERBARUI/LOGON/BUAT/dll. akan menentukan peristiwa yang pemicunya perlu diaktifkan.
• Klausul ON akan menentukan objek tempat kejadian yang disebutkan di atas berlaku. Misalnya, ini akan menjadi nama tabel tempat kejadian DML dapat terjadi dalam kasus pemicu DML.
• Perintah “FOR EACH ROW” akan menentukan pemicu tingkat BARIS.
• Klausul WHEN akan menentukan kondisi tambahan di mana pemicu perlu diaktifkan.
• Bagian deklarasi, bagian eksekusi, dan bagian penanganan pengecualian sama dengan yang lainnya. Blok PL/SQLBagian deklarasi dan penanganan pengecualian Sebagian bersifat opsional.

: BARU dan : Klausa LAMA

Dalam pemicu tingkat baris, pemicu diaktifkan untuk setiap baris terkait. Dan terkadang diperlukan untuk mengetahui nilai sebelum dan sesudah pernyataan DML.

Oracle Telah disediakan dua klausa dalam trigger tingkat baris untuk menyimpan nilai-nilai ini. Kita dapat menggunakan klausa ini untuk merujuk pada nilai lama dan nilai baru di dalam badan trigger.

• :BARU – Variabel ini menyimpan nilai baru untuk kolom-kolom tabel/tampilan dasar selama eksekusi pemicu.
• :TUA – Variabel ini menyimpan nilai lama dari kolom-kolom tabel/tampilan dasar selama eksekusi pemicu.

Klausul ini harus digunakan berdasarkan kejadian DML. Tabel di bawah ini menentukan klausul mana yang valid untuk pernyataan DML mana (INSERT/UPDATE/DELETE).

BUKAN Pemicu

Trigger “INSTEAD OF” adalah jenis trigger khusus. Trigger ini hanya digunakan dalam trigger DML. Trigger ini digunakan ketika suatu peristiwa DML akan terjadi pada tampilan yang kompleks.

Perhatikan contoh di mana sebuah view dibuat dari tiga tabel dasar. Ketika suatu event DML dikeluarkan pada view ini, view tersebut akan menjadi tidak valid karena datanya diambil dari tiga tabel yang berbeda. Jadi dalam kasus ini, trigger INSTEAD OF digunakan. Trigger INSTEAD OF digunakan untuk memodifikasi tabel dasar secara langsung, bukan memodifikasi view untuk event yang diberikan.

Contoh 1: Dalam contoh ini, kita akan membuat tampilan kompleks dari dua tabel dasar, di mana Table_1 adalah tabel karyawan dan Table_2 adalah tabel departemen.

Selanjutnya, kita akan melihat bagaimana trigger INSTEAD OF digunakan untuk mengeluarkan perintah UPDATE pada detail lokasi di tampilan kompleks ini. Kita juga akan melihat bagaimana :NEW dan :OLD berguna dalam trigger. Contoh ini dilakukan dalam langkah-langkah berikut:

• Langkah 1: Membuat tabel 'emp' dan 'dept' dengan kolom yang sesuai
• Langkah 2: Mengisi tabel dengan nilai sampel
• Langkah 3: Membuat tampilan (view) untuk tabel yang telah dibuat di atas
• Langkah 4: Perbarui tampilan sebelum pemicu INSTEAD OF
• Langkah 5: Pembuatan pemicu INSTEAD OF
• Langkah 6: Pembaruan tampilan setelah pemicu INSTEAD OF

Langkah 1) Membuat tabel 'emp' dan 'dept' dengan kolom yang sesuai.

Tangkapan layar di bawah ini menunjukkan pembuatan tabel dasar 'emp' dan 'dept' di Oracle.

CREATE TABLE emp(
emp_no NUMBER,
emp_name VARCHAR2(50),
salary NUMBER,
manager VARCHAR2(50),
dept_no NUMBER);
/

CREATE TABLE dept(
Dept_no NUMBER,
Dept_name VARCHAR2(50),
LOCATION VARCHAR2(50));
/

Code Penjelasan

• Code baris 1-7: Pembuatan tabel 'emp'.
• Code baris 8-12: Pembuatan tabel 'dept'.

Keluaran:

Table Created

Langkah 2) Sekarang, setelah kita membuat tabel, kita akan mengisinya dengan nilai-nilai contoh.

Screenshot di bawah ini menunjukkan contoh baris yang dimasukkan ke dalam tabel 'dept' dan 'emp'.

BEGIN
INSERT INTO DEPT VALUES(10,'HR','USA');
INSERT INTO DEPT VALUES(20,'SALES','UK');
INSERT INTO DEPT VALUES(30,'FINANCIAL','JAPAN');
COMMIT;
END;
/

BEGIN
INSERT INTO EMP VALUES(1000,'XXX',15000,'AAA',30);
INSERT INTO EMP VALUES(1001,'YYY',18000,'AAA',20) ;
INSERT INTO EMP VALUES(1002,'ZZZ',20000,'AAA',10);
COMMIT;
END;
/

Code Penjelasan

• Code baris 13-19: Memasukkan data ke dalam tabel 'dept'.
• Code baris 20-26: Memasukkan data ke dalam tabel 'emp'.

Keluaran:

PL/SQL procedure completed

Langkah 3) Membuat tampilan (view) untuk tabel yang telah dibuat di atas.

Tangkapan layar di bawah ini menunjukkan tampilan kompleks yang sedang dibuat dan kemudian dikueri.

CREATE VIEW guru99_emp_view(
Employee_name,dept_name,location) AS
SELECT emp.emp_name,dept.dept_name,dept.location
FROM emp,dept
WHERE emp.dept_no=dept.dept_no;
/

SELECT * FROM guru99_emp_view;

Code Penjelasan

• Code baris 27-32: Pembuatan tampilan 'guru99_emp_view'.
• Code baris 33: Menanyakan guru99_emp_view.

Keluaran:

View created

Langkah 4) Pembaruan tampilan sebelum pemicu INSTEAD OF.

Tangkapan layar di bawah ini menunjukkan upaya pembaruan pada tampilan kompleks dan kesalahan yang dihasilkan.

BEGIN
UPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name='XXX';
COMMIT;
END;
/

Code Penjelasan

• Code baris 34-38: Perbarui lokasi “XXX” menjadi 'FRANCE'. Terjadi pengecualian karena pernyataan DML tidak diizinkan langsung pada tampilan kompleks.

Keluaran:

ORA-01779: cannot modify a column which maps to a non key-preserved table

ORA-06512: at line 2

Langkah 5) Untuk menghindari kesalahan yang terjadi saat memperbarui tampilan pada langkah sebelumnya, pada langkah ini kita akan menggunakan pemicu “INSTEAD OF”.

Screenshot di bawah ini menunjukkan pembuatan trigger INSTEAD OF.

CREATE TRIGGER guru99_view_modify_trg
INSTEAD OF UPDATE
ON guru99_emp_view
FOR EACH ROW
BEGIN
UPDATE dept
SET location=:new.location
WHERE dept_name=:old.dept_name;
END;
/

Code Penjelasan

• Code baris 39: Pembuatan trigger INSTEAD OF untuk event 'UPDATE' pada view 'guru99_emp_view' di level ROW. Trigger ini berisi pernyataan update untuk memperbarui lokasi di tabel dasar 'dept'.
• Code baris 44: Pernyataan pembaruan menggunakan ':NEW' dan ':OLD' untuk menemukan nilai kolom sebelum dan sesudah pembaruan.

Keluaran:

Trigger Created

Langkah 6) Pembaruan tampilan setelah pemicu INSTEAD OF. Sekarang kesalahan tidak akan muncul, karena pemicu “INSTEAD OF” akan menangani operasi pembaruan tampilan kompleks ini. Saat kode dieksekusi, lokasi karyawan XXX akan diperbarui dari “Jepang” menjadi “Prancis”.

Tangkapan layar di bawah ini menunjukkan pembaruan yang berhasil melalui pemicu INSTEAD OF dan tampilan yang diperbarui.

BEGIN
UPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name='XXX';
COMMIT;
END;
/

SELECT * FROM guru99_emp_view;

Code Penjelasan:

• Code baris 49-53: Pembaruan lokasi “XXX” menjadi 'FRANCE'. Ini berhasil karena pemicu 'INSTEAD OF' telah menghentikan pernyataan pembaruan sebenarnya pada tampilan dan melakukan pembaruan tabel dasar.
• Code baris 55: Memverifikasi catatan yang diperbarui.

Keluaran:

PL/SQL procedure successfully completed

Pemicu Majemuk

Trigger gabungan adalah trigger yang memungkinkan Anda menentukan tindakan untuk masing-masing dari empat titik waktu dalam satu badan trigger. Empat titik waktu berbeda yang didukungnya adalah sebagai berikut.

• SEBELUM PERNYATAAN – tingkat
• SEBELUM BARIS – tingkat
• SETELAH BARIS – tingkat
• PERNYATAAN SETELAH – tingkat

Fitur ini menyediakan kemudahan untuk menggabungkan tindakan dengan waktu yang berbeda ke dalam pemicu yang sama.

Tangkapan layar di bawah ini menunjukkan sintaks pemicu gabungan dengan empat bagian pengaturan waktunya.

CREATE [ OR REPLACE ] TRIGGER <trigger_name>
FOR
[INSERT | UPDATE | DELETE.......]
ON <name of underlying object>
<Declarative part>
BEFORE STATEMENT IS
BEGIN
<Execution part>;
END BEFORE STATEMENT;

BEFORE EACH ROW IS
BEGIN
<Execution part>;
END EACH ROW;

AFTER EACH ROW IS
BEGIN
<Execution part>;
END AFTER EACH ROW;

AFTER STATEMENT IS
BEGIN
<Execution part>;
END AFTER STATEMENT;
END;

Penjelasan Sintaks:

• Sintaks di atas menunjukkan pembuatan pemicu 'KOMPONEN'.
• Bagian deklaratif bersifat umum untuk semua blok eksekusi dalam badan pemicu.
• Keempat blok pewaktu ini dapat berada dalam urutan apa pun. Tidak wajib untuk memiliki keempat blok pewaktu tersebut. Kita dapat membuat pemicu gabungan (COMPOUND trigger) hanya untuk pewaktu yang dibutuhkan.

Contoh 1: Dalam contoh ini, kita akan membuat trigger untuk mengisi otomatis kolom gaji dengan nilai default 5000.

Tangkapan layar di bawah ini menunjukkan contoh pemicu gabungan dan keluarannya.

CREATE TRIGGER emp_trig
FOR INSERT
ON emp
COMPOUND TRIGGER
BEFORE EACH ROW IS
BEGIN
:new.salary:=5000;
END BEFORE EACH ROW;
END emp_trig;
/

BEGIN
INSERT INTO EMP VALUES(1004,'CCC',15000,'AAA',30);
COMMIT;
END;
/

SELECT * FROM emp WHERE emp_no=1004;

Code Penjelasan:

• Code baris 2-10: Pembuatan trigger gabungan. Trigger ini dibuat untuk pengaturan waktu SEBELUM level BARIS untuk mengisi gaji dengan nilai default 5000. Ini akan mengubah gaji menjadi nilai default '5000' sebelum memasukkan data ke dalam tabel.
• Code baris 11-14: Masukkan data ke dalam tabel 'emp'.
• Code baris 16: Memverifikasi data yang dimasukkan.

Keluaran:

Trigger created

PL/SQL procedure successfully completed.

Mengaktifkan dan Menonaktifkan Pemicu

Trigger dapat diaktifkan atau dinonaktifkan. Untuk mengaktifkan atau menonaktifkan trigger, pernyataan ALTER (DDL) perlu diberikan untuk trigger yang menonaktifkan atau mengaktifkannya.

Berikut adalah sintaks untuk mengaktifkan/menonaktifkan pemicu.

ALTER TRIGGER <trigger_name> [ENABLE|DISABLE];
ALTER TABLE <table_name> [ENABLE|DISABLE] ALL TRIGGERS;

Penjelasan Sintaks:

• Sintaks pertama menunjukkan cara mengaktifkan/menonaktifkan satu pemicu.
• Pernyataan kedua menunjukkan cara mengaktifkan/menonaktifkan semua pemicu pada tabel tertentu.

Pertanyaan Umum Demo Slot