Oracle PL/SQL Tetikleyici Eğitimi: Bileşik Yerine [Örnek]

By: Fiona Brown Fiona Brown
Hours Güncellenmiş

PL/SQL'de Tetikleyici Nedir?

TETİKLER tarafından tetiklenen depolanmış programlardır. Oracle tablo üzerinde ekleme, güncelleme, silme gibi DML ifadeleri yürütüldüğünde veya bazı olaylar meydana geldiğinde motor otomatik olarak çalışır. Tetikleme durumunda çalıştırılacak kod ihtiyaca göre tanımlanabilir. Tetikleyicinin tetiklenmesi gereken olayı ve yürütmenin zamanlamasını seçebilirsiniz. Tetikleyicinin amacı veritabanındaki bilgilerin bütünlüğünü korumaktır.

Tetikleyicilerin Faydaları

Tetikleyicilerin faydaları şunlardır.

  • Bazı türetilmiş sütun değerlerini otomatik olarak oluşturma
  • Referans bütünlüğünü güçlendirme
  • Olay günlüğe kaydetme ve tablo erişimine ilişkin bilgilerin saklanması
  • Denetleme
  • Synctabloların düzenli kopyalanması
  • Güvenlik yetkilerinin uygulanması
  • Geçersiz işlemlerin önlenmesi

Tetikleyici Türleri Oracle

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

  • Sınıflandırma zamanlama
  • TETİKLEMEDEN ÖNCE: Belirtilen olay meydana gelmeden önce tetiklenir.
  • AFTER Trigger: Belirtilen olay meydana geldikten sonra tetiklenir.
  • Tetikleyici Yerine: Özel bir tür. Daha sonraki konular hakkında daha fazla bilgi edineceksiniz. (yalnızca DML için)
  • Sınıflandırma seviye
  • AÇIKLAMA düzeyinde Tetikleyici: Belirtilen olay bildirimi için bir kez tetiklenir.
  • SATIR düzeyinde Tetikleyici: Belirtilen olaydan etkilenen her kayıt için tetiklenir. (yalnızca DML için)
  • Sınıflandırma Etkinlikler
  • DML Tetikleyici: DML olayı belirtildiğinde tetiklenir (INSERT/UPDATE/DELETE)
  • DDL Tetikleyici: DDL olayı belirtildiğinde tetiklenir (CREATE/ALTER)
  • VERİTABANI Tetikleyicisi: Veritabanı olayı belirtildiğinde tetiklenir (LOGON/LOGOFF/STARTUP/SHUTDOWN)

Yani her tetikleyici yukarıdaki parametrelerin birleşimidir.

Tetikleyici Nasıl Oluşturulur

Aşağıda tetikleyici oluşturmaya yönelik sözdizimi verilmiştir.

Tetikleyici oluşturma

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;

Sözdizimi Açıklaması:

  • Yukarıdaki sözdizimi, tetikleyici oluşturmada mevcut olan farklı isteğe bağlı ifadeleri gösterir.
  • ÖNCE/SONRA olay zamanlamasını belirleyecektir.
  • EKLEYİN/GÜNCELLE/OTURUM AÇIN/OLUŞTUR/vb. tetikleyicinin tetiklenmesi gereken olayı belirtecektir.
  • ON cümlesi yukarıda belirtilen olayın hangi nesne üzerinde geçerli olduğunu belirtecektir. Örneğin bu, DML Trigger durumunda DML olayının oluşabileceği tablo adı olacaktır.
  • “HER SATIR İÇİN” komutu SATIR seviyesi tetikleyicisini belirleyecektir.
  • WHEN yan tümcesi, tetikleyicinin tetiklenmesi gereken ek koşulu belirtir.
  • Bildirim kısmı, yürütme kısmı, istisna işleme kısmı diğer kısımla aynıdır PL/SQL blokları. Bildirim kısmı ve istisna işleme kısmı isteğe bağlıdır.

İLGİLİ MAKALELER

:YENİ ve :ESKİ Madde

Satır düzeyindeki bir tetikleyicide tetikleyici, ilgili her satır için etkinleşir. Bazen de DML deyiminden önceki ve sonraki değerin bilinmesi gerekir.

Oracle RECORD düzeyindeki tetikleyicide bu değerleri tutmak için iki cümle sağlanmıştır. Bu cümleleri tetikleyici gövdesindeki eski ve yeni değerlere atıfta bulunmak için kullanabiliriz.

  • :NEW – Tetikleyici yürütme sırasında temel tablonun/görünümün sütunları için yeni bir değer tutar
  • :OLD – Tetikleyicinin yürütülmesi sırasında temel tablonun/görünümün sütunlarının eski değerini tutar

Bu cümle DML olayına göre kullanılmalıdır. Aşağıdaki tabloda hangi cümlenin hangi DML ifadesi için geçerli olduğu belirtilecektir (INSERT/UPDATE/DELETE).

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

Tetikleyici Yerine

“INSTEAD OF tetikleyici” özel bir tetikleyici türüdür. Yalnızca DML tetikleyicilerinde kullanılır. Karmaşık görünümde herhangi bir DML olayının gerçekleşeceği zaman kullanılır.

3 temel tablodan bir görünümün oluşturulduğu bir örneği düşünün. Bu view üzerinden herhangi bir DML olayı yayınlandığında veriler 3 farklı tablodan alındığı için bu geçersiz hale gelecektir. Yani bu INSTEAD OF tetikleyicisi kullanılır. INSTEAD OF tetikleyicisi, verilen olayın görünümünü değiştirmek yerine doğrudan temel tabloları 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.

  • Tablo_1 emp tablosudur ve
  • Tablo_2 departman tablosudur.

Daha sonra INSTEAD OF tetikleyicisinin bu karmaşık görünümde konum ayrıntısı ifadesini UPDATE'i yayınlamak için nasıl kullanıldığını göreceğiz. Ayrıca :NEW ve :OLD'un tetikleyicilerde nasıl yararlı olduğunu da göreceğiz.

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

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

Tetikleyici Yerine

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 'departman' oluşturma.

Çıktı

Tablo Oluşturuldu

) 2 Adım Artık tabloyu oluşturduğumuza göre, bu tabloyu yukarıdaki tablolar için örnek değerlerle ve Görünüm Oluşturma ile dolduracağız.

Tetikleyici Yerine

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

Kod Açıklama

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

Çıktı

PL/SQL prosedürü tamamlandı

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

Tetikleyici Yerine

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;

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

ÇALIŞAN ADI DEPT_NAME KONUM
ZZZ HR ABD
YYY SATIŞ UK
XXX FİNANSAL JAPONYA

) 4 Adım Tetikleyici yerine önceki görünümün güncellenmesi.

Tetikleyici Yerine

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

Kod Açıklama

  • Kod satırı 34-38: “XXX”in konumunu 'FRANSA' olarak güncelleyin. İstisnayı gündeme getirdi çünkü DML ifadeleri karmaşık görünümde izin verilmez.

Çıktı

ORA-01779: anahtarı korunmayan bir tabloya eşlenen bir sütun değiştirilemez

ORA-06512: 2. satırda

) 5 AdımBir önceki adımda görünümü güncellerken hatayla karşılaşmamak için bu adımda “trigger yerine” kullanacağız.

Tetikleyici Yerine

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

Kod Açıklama

  • Kod satırı 39: SATIR düzeyinde 'guru99_emp_view' görünümünde 'UPDATE' olayı için INSTEAD OF tetikleyicisinin oluşturulması. 'Dept' temel tablosundaki konumu güncellemek için update ifadesini içerir.
  • Kod satırı 44: Güncelleme ifadesi, güncellemeden önceki ve sonraki sütunların değerini bulmak için ':YENİ' ve ':ESKİ'yi kullanır.

Çıktı

Tetikleyici Oluşturuldu

) 6 Adım Yerine tetikleyiciden sonra görünümün güncellenmesi. Artık hata gelmeyecek çünkü "yerine tetikleyici" bu karmaşık görünümün güncelleme işlemini yönetecek. Ve kod yürütüldüğünde çalışan XXX'in konumu "Japonya"dan "Fransa"ya güncellenecek.

Tetikleyici Yerine

BEGIN
UPDATE 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”in konumu 'FRANSA' olarak güncellendi. 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ın doğrulanması.

Çıktı:

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

ÇALIŞAN ADI DEPT_NAME KONUM
ZZZ HR ABD
YYY SATIŞ UK
XXX FİNANSAL FRANSA

Bileşik Tetikleyici

Bileşik tetikleyici, tek tetikleyici gövdesindeki dört zamanlama noktasının her biri için eylemleri belirtmenize olanak tanıyan bir tetikleyicidir. Desteklediği dört farklı zamanlama noktası aşağıdaki gibidir.

  • AÇIKLAMADAN ÖNCE – seviye
  • SIRADAN ÖNCE – seviye
  • SATIRDAN SONRA – seviye
  • AÇIKLAMADAN SONRA – seviye

Farklı zamanlamalara ait eylemleri aynı tetikleyicide birleştirme olanağı sağlar.

Bileşik Tetikleyici

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;

Sözdizimi Açıklaması:

  • Yukarıdaki sözdizimi 'BİLEŞİK' tetikleyicisinin oluşturulmasını gösterir.
  • Bildirim bölümü, tetikleyici 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.

Bileşik Tetikleyici

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;

Kod Açıklaması:

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

Çıktı:

Tetikleyici oluşturuldu

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

EMP_NAME EMP_NO MAAŞ MÜDÜRÜ 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 tetikleyiciye ALTER (DDL) ifadesinin verilmesi gerekir.

Aşağıda tetikleyicileri etkinleştirme/devre dışı bırakma sözdizimi verilmiştir.

ALTER TRIGGER <trigger_name> [ENABLE|DISABLE];
ALTER TABLE <table_name> [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 tetikleyicilerini ve avantajlarını öğrendik. Ayrıca farklı sınıflandırmaları da öğrendik ve tetikleyici yerine BİLEŞİK tetikleyiciyi tartıştık.