Oracle Tutorial Pemicu PL/SQL: Daripada Menggabungkan [Contoh]

Oleh: Fiona Coklat Fiona Coklat
Hours Updated

Apa itu Pemicu di PL/SQL?

PEMICU adalah program tersimpan yang dijalankan Oracle mesin secara otomatis ketika Pernyataan DML seperti menyisipkan, memperbarui, menghapus dijalankan pada tabel atau beberapa peristiwa terjadi. Kode yang akan dieksekusi jika ada pemicu dapat ditentukan sesuai kebutuhan. Anda dapat memilih peristiwa yang memicu pemicunya dan waktu eksekusinya. Tujuan dari trigger adalah untuk menjaga integritas informasi pada 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: Ini diaktifkan sebelum peristiwa yang ditentukan terjadi.
  • AFTER Trigger: Ini dipicu setelah peristiwa yang ditentukan terjadi.
  • BUKAN Pemicu: Tipe khusus. Anda akan belajar lebih banyak tentang topik selanjutnya. (hanya untuk DML)
  • Klasifikasi berdasarkan tingkat
  • Pemicu tingkat PERNYATAAN: Ini diaktifkan satu kali untuk pernyataan peristiwa yang ditentukan.
  • Pemicu tingkat ROW: Ini diaktifkan untuk setiap catatan yang terpengaruh dalam peristiwa tertentu. (hanya untuk DML)
  • Klasifikasi berdasarkan Acara
  • Pemicu DML: Ini dipicu ketika peristiwa DML ditentukan (INSERT/UPDATE/DELETE)
  • Pemicu DDL: Dipicu ketika peristiwa DDL ditentukan (CREATE/ALTER)
  • Pemicu DATABASE: Ini diaktifkan ketika peristiwa database ditentukan (LOGON/LOGOFF/STARTUP/SHUTDOWN)

Jadi setiap pemicu adalah kombinasi dari parameter di atas.

Cara Membuat Pemicu

Di bawah ini adalah sintaks untuk membuat pemicu.

Buat Pemicu

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.
  • BEFORE/ AFTER akan menentukan waktu acara.
  • MASUKKAN/PERBARUI/LOGON/BUAT/dll. akan menentukan peristiwa yang pemicunya perlu diaktifkan.
  • Klausa ON akan menentukan objek mana yang valid untuk peristiwa yang disebutkan di atas. Misalnya, ini akan menjadi nama tabel tempat peristiwa DML dapat terjadi jika terjadi Pemicu DML.
  • Perintah “UNTUK SETIAP ROW” akan menentukan pemicu level ROW.
  • Klausa WHEN akan menentukan kondisi tambahan di mana pemicu perlu diaktifkan.
  • Bagian deklarasi, bagian eksekusi, bagian penanganan pengecualian sama dengan bagian lainnya Blok PL/SQL. Bagian deklarasi dan bagian penanganan pengecualian bersifat opsional.

ARTIKEL TERKAIT

: 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 menyediakan dua klausa di pemicu tingkat RECORD untuk menampung nilai-nilai ini. Kita dapat menggunakan klausa ini untuk merujuk pada nilai lama dan baru di dalam badan pemicu.

  • :BARU – Ini menyimpan nilai baru untuk kolom tabel/tampilan dasar selama eksekusi pemicu
  • :OLD – Ini menyimpan nilai lama kolom tabel/tampilan dasar selama eksekusi pemicu

Klausa ini harus digunakan berdasarkan peristiwa DML. Tabel di bawah ini akan menentukan klausa mana yang valid untuk pernyataan DML mana (INSERT/UPDATE/DELETE).

MEMASUKKAN UPDATE DELETE
:BARU SAH SAH TIDAK SAH. Tidak ada nilai baru dalam kasus penghapusan.
:TUA TIDAK SAH. Tidak ada nilai lama dalam kotak sisipan SAH SAH

BUKAN Pemicu

“INSTEAD OF trigger” adalah jenis trigger khusus. Jenis ini hanya digunakan dalam trigger DML. Jenis ini digunakan saat peristiwa DML apa pun akan terjadi pada tampilan kompleks.

Perhatikan contoh di mana tampilan dibuat dari 3 tabel dasar. Jika ada kejadian DML yang dikeluarkan melalui tampilan ini, hal tersebut akan menjadi tidak valid karena data diambil dari 3 tabel berbeda. Jadi dalam hal ini BUKAN trigger yang digunakan. Pemicu INSTEAD OF digunakan untuk mengubah tabel dasar secara langsung alih-alih mengubah tampilan untuk peristiwa tertentu.

Contoh 1: Dalam contoh ini, kita akan membuat tampilan kompleks dari dua tabel dasar.

  • Tabel_1 adalah tabel kosong dan
  • Tabel_2 adalah tabel departemen.

Kemudian kita akan melihat bagaimana pemicu INSTEAD OF digunakan untuk mengeluarkan pernyataan detail lokasi UPDATE pada tampilan kompleks ini. Kita juga akan melihat bagaimana :NEW dan :OLD berguna dalam pemicu.

  • Langkah 1: Membuat tabel 'emp' dan 'dept' dengan kolom yang sesuai
  • Langkah 2: Mengisi tabel dengan nilai sampel
  • Langkah 3: Membuat tampilan untuk tabel yang dibuat di atas
  • Langkah 4: Perbarui tampilan sebelum pemicu
  • Langkah 5: Pembuatan pemicu alih-alih
  • Langkah 6: Perbarui tampilan setelah pemicu

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

BUKAN Pemicu

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));
/

Penjelasan Kode

  • Baris kode 1-7: Pembuatan tabel 'emp'.
  • Baris kode 8-12: Pembuatan tabel 'departemen'.

Keluaran

Tabel Dibuat

Langkah 2) Sekarang karena kita telah membuat tabelnya, kita akan mengisi tabel ini dengan nilai sampel dan Pembuatan Tampilan untuk tabel di atas.

BUKAN Pemicu

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,'XXX5,15000,'AAA',30);
INSERT INTO EMP VALUES(1001,‘YYY5,18000,‘AAA’,20) ;
INSERT INTO EMP VALUES(1002,‘ZZZ5,20000,‘AAA',10); 
COMMIT;
END;
/

Penjelasan Kode

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

Keluaran

Prosedur PL/SQL selesai

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

BUKAN Pemicu

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;

Penjelasan Kode

  • Baris kode 27-32: Pembuatan tampilan 'guru99_emp_view'.
  • Baris kode 33: Menanyakan guru99_emp_view.

Keluaran

Tampilan dibuat

NAMA KARYAWAN DEPT_NAME LOKASI
Zzz HR Amerika Serikat
YYY PENJUALAN UK
XXX KEUANGAN JEPANG

Langkah 4) Pembaruan tampilan sebelum pemicu.

BUKAN Pemicu

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

Penjelasan Kode

  • Baris kode 34-38: Perbarui lokasi “XXX” menjadi 'FRANCE'. Hal ini menimbulkan pengecualian karena Pernyataan DML tidak diizinkan dalam tampilan kompleks.

Keluaran

ORA-01779: tidak dapat mengubah kolom yang dipetakan ke tabel yang tidak mempertahankan kunci

ORA-06512: di jalur 2

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

BUKAN Pemicu

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;
/

Penjelasan Kode

  • Baris kode 39: Pembuatan INSTEAD OF trigger untuk acara 'UPDATE' pada tampilan 'guru99_emp_view' di tingkat ROW. Ini berisi pernyataan pembaruan untuk memperbarui lokasi di tabel dasar 'dept'.
  • Baris kode 44: Pernyataan pembaruan menggunakan ':NEW' dan ': OLD' untuk menemukan nilai kolom sebelum dan sesudah pembaruan.

Keluaran

Pemicu Dibuat

Langkah 6) Pembaruan tampilan setelah pemicu alih-alih. Sekarang kesalahan tidak akan muncul karena "pemicu alih-alih" akan menangani operasi pembaruan tampilan kompleks ini. Dan saat kode telah dijalankan, lokasi karyawan XXX akan diperbarui menjadi "Prancis" dari "Jepang".

BUKAN Pemicu

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

SELECT * FROM guru99_emp_view;

Penjelasan Kode:

  • Baris kode 49-53: Pembaruan lokasi “XXX” ke 'FRANCE'. Ini berhasil karena pemicu 'INSTEAD OF' telah menghentikan pernyataan pembaruan aktual yang terlihat dan melakukan pembaruan tabel dasar.
  • Baris kode 55: Memverifikasi catatan yang diperbarui.

Keluaran:

Prosedur PL/SQL berhasil diselesaikan

NAMA KARYAWAN DEPT_NAME LOKASI
Zzz HR Amerika Serikat
YYY PENJUALAN UK
XXX KEUANGAN PRANCIS

Pemicu Majemuk

Pemicu gabungan adalah pemicu yang memungkinkan Anda menentukan tindakan untuk masing-masing dari empat titik waktu dalam satu badan pemicu. Empat titik waktu berbeda yang didukungnya adalah seperti di bawah ini.

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

Ini menyediakan fasilitas untuk menggabungkan tindakan untuk waktu yang berbeda ke dalam pemicu yang sama.

Pemicu Majemuk

CREATE [ OR REPLACE ] TRIGGER <trigger_name> 
FOR
[INSERT | UPDATE | DELET.......]
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 'COMPOUND'.
  • Bagian deklaratif umum untuk semua blok eksekusi di badan pemicu.
  • Keempat blok waktu ini dapat berada dalam urutan apa pun. Tidak wajib untuk memiliki keempat blok waktu ini. Kita dapat membuat pemicu COMPOUND hanya untuk timing yang diperlukan.

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

Pemicu Majemuk

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;

Penjelasan Kode:

  • Baris kode 2-10: Pembuatan pemicu gabungan. Ini dibuat untuk level timing SEBELUM ROW untuk mengisi gaji dengan nilai default 5000. Ini akan mengubah gaji ke nilai default '5000' sebelum memasukkan catatan ke dalam tabel.
  • Baris kode 11-14: Masukkan catatan ke dalam tabel 'emp'.
  • Baris kode 16: Memverifikasi catatan yang dimasukkan.

Keluaran:

Pemicu dibuat

Prosedur PL/SQL berhasil diselesaikan.

EMP_NAME EMP_TIDAK GAJI MANAGER DEPT_NO
CCC 1004 5000 AAA 30

Mengaktifkan dan Menonaktifkan Pemicu

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

Di bawah ini 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 pemicu tunggal.
  • Pernyataan kedua menunjukkan cara mengaktifkan/menonaktifkan semua pemicu pada tabel tertentu.

Kesimpulan

Dalam bab ini, kita telah mempelajari tentang trigger PL/SQL dan kelebihannya. Kita juga telah mempelajari berbagai klasifikasi dan mendiskusikan BUKAN pemicu dan pemicu KOMPAK.

Anda mungkin ingin: