Tutorial Pemicu Oracle PL / SQL: Sebaliknya, Kompaun (Contoh)

Isi kandungan:

Anonim

Apakah Pencetus dalam PL / SQL?

TRIGGERS adalah program tersimpan yang diaktifkan oleh mesin Oracle secara automatik apabila Penyataan DML seperti memasukkan, mengemas kini, menghapus dijalankan di atas meja atau beberapa peristiwa berlaku. Kod yang akan dikeluarkan jika terjadi pemicu dapat ditentukan sesuai dengan keperluan. Anda boleh memilih peristiwa di mana pemicu perlu dipecat dan masa pelaksanaannya. Tujuan pencetus adalah untuk menjaga integriti maklumat pada pangkalan data.

Dalam tutorial ini, anda akan belajar-

  • Kebaikan Pencetus
  • Jenis Pencetus di Oracle
  • Cara Membuat Pencetus
  • : BARU dan: Klausa LAMA
  • INSTEAD OF Pencetus
  • Pencetus Kompaun

Kebaikan Pencetus

Berikut adalah faedah pencetus.

  • Menjana beberapa nilai lajur yang diperoleh secara automatik
  • Menguatkan integriti rujukan
  • Pembalakan acara dan menyimpan maklumat mengenai akses meja
  • Pengauditan
  • Replikasi jadual segerak
  • Mengenakan kebenaran keselamatan
  • Mencegah transaksi tidak sah

Jenis Pencetus di Oracle

Pencetus dapat dikelaskan berdasarkan parameter berikut.

  • Pengelasan berdasarkan jangka masa
    • SEBELUM Pencetus: Ia menyala sebelum peristiwa yang ditentukan telah berlaku.
    • SETELAH Pencetus: Ia menyala setelah peristiwa yang ditentukan telah berlaku.
    • INSTEAD OF Trigger: Jenis khas. Anda akan mengetahui lebih lanjut mengenai topik selanjutnya. (hanya untuk DML)
  • Pengelasan berdasarkan tahap
    • Pencetus tahap PERNYATAAN: Ia berlaku sekali untuk penyataan peristiwa yang ditentukan.
    • Pencetus tahap ROW: Ia berlaku untuk setiap rekod yang terjejas dalam peristiwa yang ditentukan. (hanya untuk DML)
  • Pengelasan berdasarkan Acara
    • Pencetus DML: Ia menyala apabila peristiwa DML ditentukan (MASUKKAN / KEMASKINI / HAPUS)
    • Pencetus DDL: Ia menyala apabila peristiwa DDL ditentukan (BUAT / ALTER)
    • Pencetus DATABASE: Ia menyala apabila peristiwa pangkalan data ditentukan (LOGON / LOGOFF / STARTUP / SHUTDOWN)

Jadi setiap pencetus adalah gabungan parameter di atas.

Cara Membuat Pencetus

Berikut adalah sintaks untuk mencipta pencetus.

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

Penjelasan Sintaks:

  • Sintaks di atas menunjukkan pernyataan pilihan berbeza yang terdapat dalam pencetus penciptaan.
  • SEBELUM / SELEPAS akan menentukan masa acara.
  • MASUK / KEMASKINI / LOGON / BUAT / dll. akan menentukan peristiwa di mana pencetus perlu dipecat.
  • Klausa ON akan menentukan objek mana yang disebutkan di atas berlaku. Sebagai contoh, ini akan menjadi nama jadual di mana peristiwa DML mungkin berlaku dalam kes DML Trigger.
  • Perintah "UNTUK SETIAP ROW" akan menentukan pencetus tahap ROW.
  • KETIKA klausa akan menentukan syarat tambahan di mana pemicu perlu diaktifkan.
  • Bahagian deklarasi, bahagian pelaksanaan, bahagian pengendalian pengecualian sama dengan blok PL / SQL yang lain. Bahagian deklarasi dan bahagian pengendalian pengecualian adalah pilihan.

: BARU dan: Klausa LAMA

Dalam pemicu tahap baris, pemicu menyala untuk setiap baris yang berkaitan. Dan kadang-kadang diperlukan untuk mengetahui nilai sebelum dan selepas penyataan DML.

Oracle telah menyediakan dua klausa dalam pencetus tahap REKOD untuk mengekalkan nilai-nilai ini. Kita boleh menggunakan klausa ini untuk merujuk kepada nilai lama dan baru di dalam badan pencetus.

  • : BARU - Ini menyimpan nilai baru untuk lajur dasar / paparan semasa pelaksanaan pemicu
  • : LAMA - Ia menyimpan nilai lama dari lajur dasar / paparan semasa pelaksanaan pencetus

Klausa ini harus digunakan berdasarkan peristiwa DML. Jadual di bawah akan menentukan klausa mana yang sah untuk penyataan DML (INSERT / UPDATE / DELETE).

MASUKKAN KEMASKINI HAPUS
:BARU SALAH SALAH DILARANG. Tidak ada nilai baru dalam kes hapus.
:TUA DILARANG. Tidak ada nilai lama dalam sarung sisipan SALAH SALAH

INSTEAD OF Pencetus

"INSTEAD OF trigger" adalah jenis pencetus khas. Ia digunakan hanya dalam pencetus DML. Ini digunakan ketika ada peristiwa DML yang akan terjadi pada pandangan kompleks.

Pertimbangkan contoh di mana pandangan dibuat dari 3 jadual asas. Apabila sebarang acara DML dikeluarkan melalui paparan ini, itu akan menjadi tidak sah kerana data diambil dari 3 jadual yang berbeza. Jadi di INSTEAD OF trigger ini digunakan. Pencetus INSTEAD OF digunakan untuk mengubah jadual asas secara langsung dan bukannya mengubah pandangan untuk peristiwa yang diberikan.

Contoh 1 : Dalam contoh ini, kita akan membuat pandangan kompleks dari dua jadual asas.

  • Table_1 adalah jadual emp dan
  • Jadual_2 adalah jadual jabatan.

Kemudian kita akan melihat bagaimana pemicu INSTEAD OF digunakan untuk mengeluarkan KEMASKINI pernyataan terperinci lokasi pada pandangan kompleks ini. Kami juga akan melihat bagaimana: BARU dan: LAMA berguna dalam pencetus.

  • Langkah 1: Membuat jadual 'emp' dan 'dept' dengan lajur yang sesuai
  • Langkah 2: Mengisi jadual dengan nilai sampel
  • Langkah 3: Membuat paparan untuk jadual yang dibuat di atas
  • Langkah 4: Kemas kini paparan sebelum bukan pencetus
  • Langkah 5: Penciptaan bukan pencetus
  • Langkah 6: Kemas kini pandangan selepas bukan pencetus

Langkah 1) Membuat jadual 'emp' dan 'dept' dengan lajur 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 Kod

  • Baris kod 1-7 : Penciptaan 'emp' jadual.
  • Baris kod 8-12 : Pembuatan jadual 'dept'.

Pengeluaran

Jadual Dibuat

Langkah 2) Sekarang kerana kami telah membuat jadual, kami akan mengisi jadual ini dengan nilai sampel dan Penciptaan Pandangan untuk jadual 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 Kod

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

Pengeluaran

Prosedur PL / SQL selesai

Langkah 3) Membuat paparan untuk jadual 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 Kod

  • Baris kod 27-32: Penciptaan pandangan 'guru99_emp_view'.
  • Baris kod 33: Menanya guru99_emp_view.

Pengeluaran

Paparan dibuat

NAMA PEKERJA DEPT_NAME LOKASI
ZZZ HR USA
YYY JUALAN UK
XXX KEWANGAN JEPUN

Langkah 4) Kemas kini paparan sebelum dan bukannya pencetus.

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

Penjelasan Kod

  • Baris kod 34-38: Kemas kini lokasi "XXX" ke 'FRANCE'. Ini menimbulkan pengecualian kerana pernyataan DML tidak dibenarkan dalam pandangan kompleks.

Pengeluaran

ORA-01779: tidak dapat mengubah lajur yang memetakan ke jadual tanpa kunci

ORA-06512: di baris 2

Langkah 5) Untuk mengelakkan terjadinya kesalahan semasa memperbaharui paparan pada langkah sebelumnya, pada langkah ini kita akan menggunakan "bukannya 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 Kod

  • Baris kod 39: Penciptaan INSTEAD OF trigger untuk acara 'UPDATE' pada paparan 'guru99_emp_view' di peringkat ROW. Ini mengandungi pernyataan kemas kini untuk mengemas kini lokasi di tabel dasar 'dept'.
  • Baris kod 44: Pernyataan kemas kini menggunakan ': BARU' dan ': LAMA' untuk mencari nilai lajur sebelum dan selepas kemas kini.

Pengeluaran

Pencetus Dicipta

Langkah 6) Kemas kini pandangan selepas bukan pencetus Sekarang kesalahan tidak akan datang kerana "bukannya pencetus" akan mengendalikan operasi kemas kini dari pandangan kompleks ini. Dan apabila kod telah dilaksanakan lokasi pekerja XXX akan dikemas kini menjadi "Perancis" dari "Jepun."

BEGINUPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name='XXX';COMMIT;END;/
SELECT * FROM guru99_emp_view;

Penjelasan Kod:

  • Baris kod 49-53: Kemas kini lokasi "XXX" ke 'FRANCE'. Ia berjaya kerana pencetus 'INSTEAD OF' telah menghentikan penyataan kemas kini sebenar yang dilihat dan melakukan kemas kini jadual asas.
  • Baris kod 55: Mengesahkan rekod yang dikemas kini.

Pengeluaran:

Prosedur PL / SQL berjaya diselesaikan

NAMA PEKERJA DEPT_NAME LOKASI
ZZZ HR USA
YYY JUALAN UK
XXX KEWANGAN PERANCIS

Pencetus Kompaun

Pencetus Kompaun adalah pemicu yang membolehkan anda menentukan tindakan untuk setiap empat titik waktu dalam badan pencetus tunggal. Empat titik masa yang berbeza yang disokongnya adalah seperti di bawah.

  • SEBELUM KENYATAAN - tahap
  • SEBELUM ROW - tahap
  • SELEPAS ROW - tahap
  • SELEPAS PERNYATAAN - tahap

Ini menyediakan kemudahan untuk menggabungkan tindakan untuk masa yang berbeza menjadi pencetus 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 penciptaan pencetus 'COMPOUND'.
  • Bahagian deklarasi adalah perkara biasa untuk semua blok pelaksanaan di badan pencetus.
  • 4 blok masa ini boleh mengikut urutan apa pun. Tidak wajib memiliki semua 4 blok masa ini. Kita boleh membuat pencetus KOMPOUND hanya untuk jangka masa yang diperlukan.

Contoh 1 : Dalam contoh ini, kita akan membuat pemicu untuk mengisi ruangan gaji secara automatik dengan nilai lalai 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 Kod:

  • Garis kod 2-10 : Penciptaan pencetus kompaun. Ia dibuat untuk masa SEBELUM ROW- level untuk mengisi gaji dengan nilai lalai 5000. Ini akan mengubah gaji menjadi nilai lalai '5000' sebelum memasukkan rekod ke dalam jadual.
  • Baris kod 11-14 : Masukkan rekod ke dalam jadual 'emp'.
  • Baris kod 16 : Mengesahkan rekod yang dimasukkan.

Pengeluaran:

Pencetus dibuat

Prosedur PL / SQL berjaya diselesaikan.

EMP_NAME EMP_NO GAJI PENGURUS DEPT_NO
CCC 1004 5000 AAA 30

Mengaktifkan dan Melumpuhkan Pencetus

Pencetus boleh diaktifkan atau dilumpuhkan. Untuk mengaktifkan atau mematikan pencetus, pernyataan ALTER (DDL) perlu diberikan untuk pencetus yang melumpuhkan atau mengaktifkannya.

Berikut adalah sintaks untuk mengaktifkan / mematikan pencetus.

ALTER TRIGGER 
 [ENABLE|DISABLE];ALTER TABLE 
 [ENABLE|DISABLE] ALL TRIGGERS;

Penjelasan Sintaks:

  • Sintaks pertama menunjukkan cara mengaktifkan / mematikan pencetus tunggal.
  • Pernyataan kedua menunjukkan cara mengaktifkan / mematikan semua pencetus pada jadual tertentu.

Ringkasan

Dalam bab ini, kita telah belajar mengenai pencetus PL / SQL dan kelebihannya. Kami juga telah mempelajari klasifikasi yang berbeza dan membincangkan pemicu INSTEAD OF trigger dan COMPOUND.