Oracle PL / SQL Tetikleyici Eğitimi: Bileşik yerine (Örnek)

İçindekiler:

Anonim

PL / SQL'de Tetikleme nedir?

TETİKLEYİCİLER , tablo üzerinde ekleme, güncelleme, silme gibi DML İfadeleri yürütüldüğünde veya bazı olaylar meydana geldiğinde Oracle motoru tarafından otomatik olarak çalıştırılan depolanmış programlardır. Tetikleme durumunda çalıştırılacak kod ihtiyaca göre tanımlanabilir. Tetikleyicinin tetiklenmesi gereken olayı ve yürütme zamanlamasını seçebilirsiniz. Tetiklemenin amacı, veri tabanındaki bilgilerin bütünlüğünü korumaktır.

Bu eğitimde öğreneceksiniz-

  • Tetikleyicilerin Yararları
  • Oracle'da Tetikleyici Türleri
  • Tetikleyici Nasıl Oluşturulur
  • : YENİ ve: ESKİ Fıkra
  • Tetik YERİNE
  • Bileşik Tetikleyici

Tetikleyicilerin Yararları

Tetikleyicilerin faydaları aşağıdadır.

  • Bazı türetilmiş sütun değerlerini otomatik olarak oluşturma
  • Bilgi tutarlılığını zorunlu kılma
  • Olay günlüğü ve tablo erişimi ile ilgili bilgilerin depolanması
  • Denetleme
  • Tabloların eşzamanlı çoğaltılması
  • Güvenlik yetkilerinin dayatılması
  • Geçersiz işlemlerin önlenmesi

Oracle'da Tetikleyici Türleri

Tetikleyiciler, aşağıdaki parametrelere göre sınıflandırılabilir.

  • Zamanlamaya göre sınıflandırma
    • Tetiklemeden ÖNCE: Belirtilen olay gerçekleşmeden önce ateşlenir.
    • Tetiklemeden Sonra: Belirtilen olay meydana geldikten sonra ateşlenir.
    • Tetik YERİNE: Özel bir tip. Diğer konular hakkında daha fazla bilgi edineceksiniz. (yalnızca DML için)
  • Sınıflandırma dayalı seviyede
    • STATEMENT level Trigger: Belirtilen olay ifadesi için bir kez tetiklenir.
    • ROW seviyesi Tetikleyici: Belirtilen olaydan etkilenen her kayıt için ateşlenir. (yalnızca DML için)
  • Etkinliğe Göre Sınıflandırma
    • DML Tetikleyicisi: DML olayı belirtildiğinde tetiklenir (INSERT / UPDATE / DELETE)
    • DDL Tetikleyici: DDL olayı belirtildiğinde ateşlenir (CREATE / ALTER)
    • VERİTABANI Tetikleyicisi: Veritabanı olayı belirtildiğinde tetiklenir (LOGON / LOGOFF / STARTUP / SHUTDOWN)

Dolayısıyla, her tetikleyici yukarıdaki parametrelerin birleşimidir.

Tetikleyici Nasıl Oluşturulur

Aşağıda bir tetikleyici oluşturmak için söz dizimi verilmiştir.

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

Sözdizimi Açıklaması:

  • Yukarıdaki sözdizimi, tetik oluşturmada mevcut olan farklı isteğe bağlı ifadeleri gösterir.
  • ÖNCE / SONRA olay zamanlamalarını belirleyecektir.
  • INSERT / UPDATE / LOGON / CREATE / vb. tetikleyicinin tetiklenmesi gereken olayı belirtir.
  • ON cümlesi, yukarıda bahsedilen olayın hangi nesne üzerinde geçerli olduğunu belirleyecektir. Örneğin bu, DML Tetiklemesi durumunda DML olayının meydana gelebileceği tablo adı olacaktır.
  • "HER SATIR İÇİN" komutu, SATIR seviyesi tetikleyicisini belirleyecektir.
  • WHEN cümlesi, tetikleyicinin devreye girmesi gereken ek koşulu belirtir.
  • Bildirim bölümü, yürütme bölümü, istisna işleme bölümü diğer PL / SQL blokları ile aynıdır. Bildirim bölümü ve istisna işleme bölümü isteğe bağlıdır.

: YENİ ve: ESKİ Fıkra

Bir satır seviyesi tetikleyicide, tetikleyici her ilgili satır için etkinleşir. Ve bazen DML ifadesinden önceki ve sonraki değeri bilmek gerekir.

Oracle, KAYIT düzeyinde tetikleyicide bu değerleri tutmak için iki madde sağlamıştır. Tetikleyici gövdenin içindeki eski ve yeni değerlere atıfta bulunmak için bu maddeleri kullanabiliriz.

  • : YENİ - Tetikleyici yürütme sırasında temel tablonun / görünümün sütunları için yeni bir değer tutar
  • : ESKİ - Tetikleyici yürütme sırasında temel tablonun / görünümün sütunlarının eski değerini tutar

Bu madde, DML olayına göre kullanılmalıdır. Aşağıdaki tablo, hangi maddenin hangi DML ifadesi için geçerli olduğunu belirtecektir (INSERT / UPDATE / DELETE).

EKLE GÜNCELLEME SİL
:YENİ GEÇERLİ GEÇERLİ GEÇERSİZ. Silme durumunda yeni değer yoktur.
:ESKİ GEÇERSİZ. Ekleme durumunda eski değer yok GEÇERLİ GEÇERLİ

Tetik YERİNE

"INSTEAD OF trigger" özel tetik türüdür. Yalnızca DML tetikleyicilerinde kullanılır. Karmaşık görünümde herhangi bir DML olayı meydana geldiğinde kullanılır.

Bir görünümün 3 temel tablodan yapıldığı bir örnek düşünün. Bu görünüm üzerinden herhangi bir DML olayı yayınlandığında, veriler 3 farklı tablodan alındığı için bu geçersiz olacaktır. Yani bu INSTEAD OF tetikleyicisinde kullanılır. INSTEAD OF tetikleyicisi, verilen olay için görünümü değiştirmek yerine temel tabloları doğrudan değiştirmek için kullanılır.

Örnek 1 : Bu örnekte, iki temel tablodan karmaşık bir görünüm oluşturacağız.

  • Table_1 emp tablosu ve
  • Tablo_2, departman tablosudur.

Ardından, INSTEAD OF tetikleyicisinin bu karmaşık görünümdeki konum ayrıntı ifadesini UPDATE yayınlamak için nasıl kullanıldığını göreceğiz. Ayrıca: YENİ ve: ESKİ'nin tetikleyicilerde nasıl yararlı olduğunu göreceğiz.

  • Adım 1: Uygun sütunlarla 'emp' ve 'dept' tablolarını oluşturma
  • Adım 2: Tabloyu örnek değerlerle doldurma
  • 3. Adım: Yukarıda oluşturulan tablo için görünüm oluşturma
  • 4. Adım: Tetikleyiciden önce görünümün güncellenmesi
  • Adım 5: Tetikleyici yerine tetikleyicinin oluşturulması
  • 6. Adım: Tetikleyici yerine sonra görünümün güncellenmesi

Adım 1) Uygun sütunlarla 'emp' ve 'dept' tablosu oluşturma

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

Kod Açıklama

  • Kod satırı 1-7 : Tablo 'emp' oluşturma.
  • Kod satırı 8-12 : Tablo 'bölüm' oluşturma.

Çıktı

Tablo Oluşturuldu

Adım 2) Şimdi tabloyu oluşturduğumuz için, bu tabloyu örnek değerler ve yukarıdaki tablolar için Görünümlerin Oluşturulması ile dolduracağız. 

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

Kod Açıklama

  • Kod satırı 13-19 : 'Dept' tablosuna veri ekleme.
  • Kod satırı 20-26: 'emp' tablosuna veri ekleme.

Çıktı

PL / SQL prosedürü tamamlandı

Adım 3) Yukarıda oluşturulan tablo için bir görünüm oluşturmak. 

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;

Kod Açıklama

  • Kod satırı 27-32: 'guru99_emp_view' görünümünün oluşturulması.
  • Kod satırı 33: guru99_emp_view sorgulanıyor.

Çıktı

Oluşturulanları görüntüle

İŞÇİ ADI DEPT_NAME YER
ZZZ İK Amerika Birleşik Devletleri
YYY SATIŞ İngiltere
XXX PARASAL JAPONYA

Adım 4) Tetikleme yerine önce görünümün güncellenmesi.

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

Kod Açıklama

  • Kod satırı 34-38: "XXX" konumunu "FRANSA" olarak güncelleyin. Karmaşık görünümde DML ifadelerine izin verilmediğinden istisnayı gündeme getirdi.

Çıktı

ORA-01779: anahtarı korunmayan bir tabloyla eşleşen bir sütun değiştirilemez

ORA-06512: 2. satırda

Adım 5) Önceki adımda güncelleme görünümü sırasında hatayla karşılaşmamak için, bu adımda "tetikleyici" yerine "tetikleyici" kullanacağız.

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

Kod Açıklama

  • Kod satırı 39: SATIR düzeyinde "guru99_emp_view" görünümünde "GÜNCELLEME" olayı için INSTEAD OF tetikleyicisinin oluşturulması. Temel tablodaki 'dept' konumunu güncellemek için güncelleme ifadesini içerir.
  • Kod satırı 44: Güncelleme ifadesi, güncellemeden önceki ve sonraki sütunların değerini bulmak için ': NEW' ve ': OLD' kullanır.

Çıktı

Tetikleyici Oluşturuldu

Adım 6) Tetikleme yerine sonra görünümün güncellenmesi. Artık "tetikleyici yerine" bu karmaşık görünümün güncelleme işlemini gerçekleştireceği için hata gelmeyecektir. Kod yürütüldüğünde, XXX çalışanının konumu "Japonya" dan "Fransa" olarak güncellenecektir.

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

Kod Açıklaması:

  • Kod satırı 49-53: "XXX" konumunun "FRANSA" olarak güncellenmesi. Başarılı, çünkü 'INSTEAD OF' tetikleyicisi görünümdeki gerçek güncelleme ifadesini durdurdu ve temel tablo güncellemesini gerçekleştirdi.
  • Kod satırı 55: Güncellenen kaydı doğrulama.

Çıktı:

PL / SQL prosedürü başarıyla tamamlandı

İŞÇİ ADI DEPT_NAME YER
ZZZ İK Amerika Birleşik Devletleri
YYY SATIŞ İngiltere
XXX PARASAL FRANSA

Bileşik Tetikleyici

Bileşik tetik, tek tetik gövdesindeki dört zamanlama noktasının her biri için eylemler belirlemenizi sağlayan bir tetikleyicidir. Desteklediği dört farklı zamanlama noktası aşağıdaki gibidir.

  • BİLDİRİMDEN ÖNCE - seviye
  • SATIRDAN ÖNCE - seviye
  • SATIR SONRASI - seviye
  • AFTER STATEMENT - seviye

Tesisin farklı zamanlama için eylemleri aynı tetikleyicide birleştirmesini sağlar.

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;

Sözdizimi Açıklaması:

  • Yukarıdaki sözdizimi, "BİLEŞİK" tetikleyicinin oluşturulmasını gösterir.
  • Bildirim bölümü, tetik gövdesindeki tüm yürütme bloğu için ortaktır.
  • Bu 4 zamanlama bloğu herhangi bir sırada olabilir. Bu 4 zamanlama bloğunun hepsine sahip olmak zorunlu değildir. Yalnızca gerekli zamanlamalar için BİLEŞİK tetikleyici oluşturabiliriz.

Örnek 1 : Bu örnekte, maaş sütununu varsayılan değer olan 5000 ile otomatik olarak doldurmak için bir tetikleyici oluşturacağız.

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;

Kod Açıklaması:

  • Kod satırı 2-10 : Bileşik tetikleyicinin oluşturulması. Maaşın varsayılan değer olan 5000 ile doldurulması için SIRADAN ÖNCE zamanlama için oluşturulmuştur. Bu, kaydı tabloya eklemeden önce maaşı varsayılan değer olan "5000" olarak değiştirecektir.
  • Kod satırı 11-14 : Kaydı 'emp' tablosuna ekleyin.
  • Kod satırı 16 : Eklenen kaydı doğrulama.

Çıktı:

Tetik oluşturuldu

PL / SQL prosedürü başarıyla tamamlandı.

EMP_NAME EMP_NO MAAŞ YÖNETİCİ DEPT_NO
CCC 1004 5000 AAA 30

Tetikleyicileri Etkinleştirme ve Devre Dışı Bırakma

Tetikleyiciler etkinleştirilebilir veya devre dışı bırakılabilir. Tetikleyiciyi etkinleştirmek veya devre dışı bırakmak için, onu devre dışı bırakan veya etkinleştiren tetikleyici için bir ALTER (DDL) ifadesi verilmesi gerekir.

Tetikleyicileri etkinleştirmek / devre dışı bırakmak için sözdizimi aşağıdadır.

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

Sözdizimi Açıklaması:

  • İlk sözdizimi, tek tetikleyicinin nasıl etkinleştirileceğini / devre dışı bırakılacağını gösterir.
  • İkinci ifade, belirli bir tablodaki tüm tetikleyicilerin nasıl etkinleştirileceğini / devre dışı bırakılacağını gösterir.

Özet

Bu bölümde, PL / SQL tetikleyicileri ve avantajları hakkında bilgi sahibi olduk. Ayrıca farklı sınıflandırmaları öğrendik ve INSTEAD OF trigger ve COMPOUND tetikleyicisini tartıştık.