Tutorial Pemicu PL / SQL Oracle: Alih-alih, Senyawa (Contoh)

Daftar Isi:

Anonim

Apa Trigger di PL / SQL?

PEMICU adalah program yang disimpan yang dijalankan oleh mesin Oracle secara otomatis ketika Pernyataan DML seperti penyisipan, pembaruan, penghapusan dijalankan di atas meja atau beberapa peristiwa terjadi. Kode yang akan dieksekusi jika ada pemicu dapat ditentukan sesuai kebutuhan. Anda dapat memilih peristiwa di mana pemicu perlu diaktifkan dan waktu eksekusi. Tujuan trigger adalah untuk menjaga integritas informasi pada database.

Dalam tutorial ini, Anda akan belajar-

  • Manfaat Pemicu
  • Jenis Pemicu di Oracle
  • Cara Membuat Pemicu
  • : BARU dan: Klausa LAMA
  • BUKAN Trigger
  • Pemicu Senyawa

Manfaat Pemicu

Berikut manfaat trigger.

  • Menghasilkan beberapa nilai kolom turunan secara otomatis
  • Menerapkan integritas referensial
  • Event logging dan menyimpan informasi pada akses tabel
  • Mengaudit
  • Replikasi tabel secara sinkron
  • Menerapkan otorisasi keamanan
  • Mencegah transaksi tidak valid

Jenis Pemicu di Oracle

Pemicu dapat diklasifikasikan berdasarkan parameter berikut.

  • Klasifikasi berdasarkan waktu
    • BEFORE Trigger: Ini disulut sebelum peristiwa tertentu terjadi.
    • AFTER Trigger: Ini diaktifkan setelah peristiwa tertentu telah terjadi.
    • BUKAN Trigger: Tipe khusus. Anda akan mempelajari lebih lanjut tentang topik selanjutnya. (hanya untuk DML)
  • Klasifikasi berdasarkan level
    • PERNYATAAN level Pemicu: Ini diaktifkan satu kali untuk pernyataan acara tertentu.
    • Pemicu level ROW: Ini diaktifkan untuk setiap rekaman yang terpengaruh dalam acara tertentu. (hanya untuk DML)
  • Klasifikasi berdasarkan Peristiwa
    • Pemicu DML: Ini diaktifkan ketika peristiwa DML ditentukan (INSERT / UPDATE / DELETE)
    • DDL Trigger: Ini aktif ketika peristiwa DDL ditentukan (CREATE / ALTER)
    • Pemicu DATABASE: Ini menyala 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.

CREATE [ OR REPLACE ] TRIGGER 
[BEFORE | AFTER | INSTEAD OF ][INSERT | UPDATE | DELETE… ]ON[FOR EACH ROW][WHEN ]DECLAREBEGINEXCEPTIONEND;

Penjelasan Sintaks:

  • Sintaks di atas menunjukkan pernyataan opsional berbeda yang ada dalam pembuatan pemicu.
  • BEFORE / AFTER akan menentukan pengaturan waktu acara.
  • SISIPKAN / PERBARUI / LOGON / BUAT / dll. akan menentukan peristiwa yang pemicu perlu diaktifkan.
  • Klausa ON akan menentukan objek mana yang valid untuk peristiwa yang disebutkan di atas. Misalnya, ini akan menjadi nama tabel tempat kejadian DML dapat terjadi dalam kasus Pemicu DML.
  • Perintah "UNTUK SETIAP ROW" akan menentukan pemicu level ROW.
  • Klausa WHEN akan menentukan kondisi tambahan di mana pemicu harus diaktifkan.
  • Bagian deklarasi, bagian eksekusi, bagian penanganan pengecualian sama dengan bagian dari blok PL / SQL lainnya. Bagian deklarasi dan bagian penanganan pengecualian adalah 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 menyediakan dua klausul di pemicu tingkat RECORD untuk menyimpan nilai-nilai ini. Kita dapat menggunakan klausa ini untuk merujuk ke nilai lama dan baru di dalam tubuh pemicu.

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

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

MEMASUKKAN MEMPERBARUI MENGHAPUS
:BARU SAH SAH TIDAK VALID. Tidak ada nilai baru dalam kasus hapus.
:TUA TIDAK VALID. Tidak ada nilai lama dalam kasus sisipan SAH SAH

BUKAN Trigger

"BUKAN pemicu" adalah jenis pemicu khusus. Ini hanya digunakan di pemicu DML. Ini digunakan ketika kejadian DML apa pun akan terjadi pada tampilan kompleks.

Pertimbangkan contoh di mana tampilan dibuat dari 3 tabel dasar. Jika ada kejadian DML yang dikeluarkan pada tampilan ini, itu akan menjadi tidak valid karena data diambil dari 3 tabel yang berbeda. Jadi dalam pemicu BUKAN ini digunakan. Pemicu INSTEAD OF digunakan untuk mengubah tabel dasar secara langsung daripada mengubah tampilan untuk acara yang diberikan.

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

  • Table_1 adalah tabel emp dan
  • Table_2 adalah tabel departemen.

Kemudian kita akan melihat bagaimana pemicu BUKAN digunakan untuk mengeluarkan UPDATE pernyataan detail lokasi pada tampilan kompleks ini. Kita juga akan melihat bagaimana: BARU dan: LAMA 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: Pembaruan tampilan sebelum pemicu alih-alih
  • Langkah 5: Pembuatan pemicu pengganti
  • Langkah 6: Pembaruan tampilan setelah alih-alih pemicu

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

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 'dept'.

Keluaran

Tabel Dibuat

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

BEGININSERT INTO DEPT VALUES(10,‘HR’,‘USA’);INSERT INTO DEPT VALUES(20,'SALES','UK’);INSERT INTO DEPT VALUES(30,‘FINANCIAL',‘JAPAN');COMMIT;END;/BEGININSERT 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. 

CREATE VIEW guru99_emp_view(Employee_name:dept_name,location) ASSELECT emp.emp_name,dept.dept_name,dept.locationFROM emp,deptWHERE 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: Membuat kueri guru99_emp_view.

Keluaran

Tampilan dibuat

NAMA KARYAWAN DEPT_NAME LOKASI
ZZZ HR Amerika Serikat
YYY PENJUALAN INGGRIS
XXX KEUANGAN JEPANG

Langkah 4) Pembaruan tampilan sebelum alih-alih pemicu.

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

Penjelasan Kode

  • Baris kode 34-38: Perbarui lokasi "XXX" menjadi 'FRANCE'. Itu memunculkan pengecualian karena pernyataan DML tidak diizinkan dalam tampilan kompleks.

Keluaran

ORA-01779: tidak dapat mengubah kolom yang memetakan ke tabel tanpa kunci

ORA-06512: di baris 2

Langkah 5) Untuk menghindari terjadinya kesalahan selama memperbarui tampilan di langkah sebelumnya, dalam langkah ini kita akan menggunakan "bukan pemicu."

CREATE TRIGGER guru99_view_modify_trgINSTEAD OF UPDATEON guru99_emp_viewFOR EACH ROWBEGINUPDATE deptSET location=:new.locationWHERE dept_name=:old.dept_name;END;/

Penjelasan Kode

  • Baris kode 39: Pembuatan pemicu BUKAN 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 update menggunakan ': NEW' dan ': OLD' untuk mencari nilai kolom sebelum dan sesudah update.

Keluaran

Pemicu Dibuat

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

BEGINUPDATE 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 'BUKAN' telah menghentikan pernyataan pembaruan aktual pada tampilan 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 INGGRIS
XXX KEUANGAN PERANCIS

Pemicu Senyawa

Pemicu gabungan adalah pemicu yang memungkinkan Anda menentukan tindakan untuk masing-masing dari empat titik waktu dalam satu isi pemicu. Empat titik waktu berbeda yang didukungnya adalah sebagai berikut.

  • SEBELUM PERNYATAAN - level
  • SEBELUM BARIS - level
  • SETELAH BARIS - level
  • SETELAH PERNYATAAN - level

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

CREATE [ OR REPLACE ] TRIGGER 
FOR[INSERT | UPDATE | DELET… .]ON ‭ ‬BEFORE STATEMENT ISBEGIN;END BEFORE STATEMENT;BEFORE EACH ROW ISBEGIN;END EACH ROW;AFTER EACH ROW ISBEGIN;END AFTER EACH ROW;AFTER STATEMENT ISBEGIN;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 memiliki semua 4 blok waktu ini. Kami dapat membuat pemicu COMPOUND hanya untuk pengaturan waktu yang diperlukan.

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

CREATE TRIGGER emp_trigFOR INSERTON empCOMPOUND TRIGGERBEFORE EACH ROW ISBEGIN:new.salary:=5000;END BEFORE EACH ROW;END emp_trig;/
BEGININSERT 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 waktu SEBELUM ROW level untuk mengisi gaji dengan nilai default 5000. Ini akan mengubah gaji ke nilai default '5000' sebelum memasukkan record ke dalam tabel.
  • Baris kode 11-14 : Masukkan record ke tabel 'emp'.
  • Baris kode 16 : Memverifikasi rekaman yang dimasukkan.

Keluaran:

Pemicu dibuat

Prosedur PL / SQL berhasil diselesaikan.

EMP_NAME EMP_NO GAJI PENGELOLA DEPT_NO
CCC 1004 5.000 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 
 [ENABLE|DISABLE];ALTER TABLE 
 [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.

Ringkasan

Dalam bab ini, kita telah belajar tentang pemicu PL / SQL dan keuntungannya. Kami juga telah mempelajari klasifikasi yang berbeda dan membahas BUKAN trigger dan COMPOUND trigger.