Oracle PL/SQL Tetikleyici: & Bileşik Türleri Yerine

PL/SQL'de Tetikleyici Nedir?

TETİKLEYİCİLER saklanır PL / SQL tarafından tetiklenen programlar Oracle motor otomatik olarak DML ifadeleri Tetikleyiciler, tabloda ekleme, güncelleme ve silme işlemleri veya bazı olaylar gerçekleştiğinde çalıştırılır. Bir tetikleyici durumunda çalıştırılacak kod, gereksinime göre tanımlanabilir. Tetikleyicinin hangi olayda tetiklenmesi gerektiğini ve yürütme 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.

Zamanlamaya göre sınıflandırma

• Tetiklemeden ÖNCE: Belirtilen olay gerçekleşmeden önce tetiklenir.
• Tetiklemeden Sonra: Belirtilen olay gerçekleştikten sonra tetiklenir.
• Tetik YERİNE: Özel bir tür. İlerleyen konularda daha fazlasını öğreneceksiniz. (Sadece DML için)

Seviyeye göre sınıflandırma

• İFADE düzeyinde Tetikleyici: Belirtilen olay ifadesi için bir kez tetiklenir.
• SATIR seviyesi Tetikleyici: Belirtilen olayda etkilenen her kayıt için tetiklenir. (yalnızca DML için)

Olaya göre sınıflandırma

• DML Tetikleyicisi: DML olayı (INSERT/UPDATE/DELETE) belirtildiğinde tetiklenir.
• DDL Tetikleyicisi: DDL olayı (CREATE/ALTER) belirtildiğinde tetiklenir.
• VERİTABANI Tetikleyicisi: Veritabanı olayı belirtildiğinde (OTURUM AÇMA/OTURUM KAPATMA/BAŞLATMA/KAPATMA) tetiklenir.

Dolayısıyla her tetikleyici, yukarıdaki parametrelerin bir kombinasyonudur.

Tetikleyici Nasıl Oluşturulur

Aşağıda tetikleyici oluşturma sözdizimi verilmiştir. Aşağıdaki ekran görüntüsü bu tetikleyici oluşturma sözdizimini göstermektedir. Oracle.

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 seçenekleri etkinlik zamanlamasını belirleyecektir.
• EKLEYİN/GÜNCELLE/OTURUM AÇIN/OLUŞTUR/vb. tetikleyicinin tetiklenmesi gereken olayı belirtecektir.
• ON yan tümcesi, yukarıda bahsedilen olayın geçerli olacağı nesneyi belirtir. Örneğin, bu, bir DML tetikleyicisi durumunda DML olayının gerçekleşebileceği tablo adı olacaktır.
• “HER SATIR İÇİN” komutu, satır düzeyinde tetiklemeyi belirleyecektir.
• WHEN maddesi, tetikleyicinin devreye girmesi gereken ek koşulu belirtecektir.
• Bildirim kısmı, yürütme kısmı ve istisna işleme kısmı diğerleriyle aynıdır. PL/SQL bloklarıBeyanname kısmı ve istisna işleme Bazı kısımlar isteğe bağlıdır.

: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 Bu değerleri tutmak için satır düzeyindeki tetikleyiciye iki madde eklenmiştir. Bu maddeleri, tetikleyici gövdesi içinde eski ve yeni değerlere başvurmak için kullanabiliriz.

• :YENİ – Tetikleyici yürütülürken temel tablo/görünümün sütunları için yeni bir değer tutar.
• :ESKİMİŞ – Tetikleyici çalıştırıldığı sırada temel tablo/görünümün sütunlarının eski değerlerini korur.

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

Tetikleyici Yerine

"INSTEAD OF" tetikleyicisi özel bir tetikleyici türüdür. Sadece DML tetikleyicilerinde kullanılır. Karmaşık bir görünümde herhangi bir DML olayının gerçekleşeceği durumlarda kullanılır.

Üç temel tablodan oluşturulmuş bir görünümü ele alalım. Bu görünüm üzerinde herhangi bir DML olayı gerçekleştiğinde, veriler üç farklı tablodan alındığı için görünüm geçersiz hale gelir. Bu durumda, bir INSTEAD OF tetikleyicisi 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, Table_1'in çalışan tablosu ve Table_2'nin departman tablosu olduğu iki temel tablodan karmaşık bir görünüm oluşturacağız.

Ardından, bu karmaşık görünümde konum detayının güncellenmesi için INSTEAD OF tetikleyicisinin nasıl kullanıldığını göreceğiz. Ayrıca, tetikleyicilerde :NEW ve :OLD'un nasıl faydalı olduğunu da inceleyeceğiz. Örnek aşağıdaki adımlarla gerçekleştirilir:

• Adım 1: Uygun sütunlarla 'emp' ve 'dept' tablolarını oluşturma
• Adım 2: Tabloları örnek değerlerle doldurma
• 3. Adım: Yukarıda oluşturulan tablolar için bir görünüm oluşturma
• Adım 4: INSTEAD OF tetikleyicisinden önce görünümün güncellenmesi
• Adım 5: YERİNE tetikleyicisinin oluşturulması
• Adım 6: INSTEAD OF tetikleyicisinden sonra görünümün güncellenmesi

Adım 1) Uygun sütunlarla 'emp' ve 'dept' tablolarını oluşturun.

Aşağıdaki ekran görüntüsü, 'emp' ve 'dept' temel tablolarının oluşturulmasını göstermektedir. Oracle.

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

Code açıklama

• Code 1-7. satırlar: 'emp' tablosunun oluşturulması.
• Code 8-12. satırlar: 'Dept' tablosunun oluşturulması.

Çıktı:

Table Created

) 2 Adım Şimdi, tabloları oluşturduğumuza göre, bunları örnek değerlerle dolduracağız.

Aşağıdaki ekran görüntüsü, 'dept' ve 'emp' tablolarına eklenen örnek satırları göstermektedir.

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,'XXX',15000,'AAA',30);
INSERT INTO EMP VALUES(1001,'YYY',18000,'AAA',20) ;
INSERT INTO EMP VALUES(1002,'ZZZ',20000,'AAA',10);
COMMIT;
END;
/

Code açıklama

• Code 13-19. satırlar: 'Dept' tablosuna veri ekleniyor.
• Code 20-26. satırlar: 'emp' tablosuna veri ekleniyor.

Çıktı:

PL/SQL procedure completed

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

Aşağıdaki ekran görüntüsü, karmaşık görünümün oluşturulmasını ve ardından sorgulanmasını göstermektedir.

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;

Code açıklama

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

Çıktı:

View created

) 4 Adım INSTEAD OF tetikleyicisinden önce görünümün güncellenmesi.

Aşağıdaki ekran görüntüsü, karmaşık görünümde yapılan güncelleme girişimini ve ortaya çıkan hatayı göstermektedir.

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

Code açıklama

• Code 34-38. satırlar: “XXX” konumunu 'FRANCE' olarak güncelleyin. DML ifadelerinin doğrudan karmaşık görünüm üzerinde kullanılmasına izin verilmediği için bir istisna oluştu.

Çıktı:

ORA-01779: cannot modify a column which maps to a non key-preserved table

ORA-06512: at line 2

) 5 Adım Önceki adımda görünümü güncellerken karşılaşılan hatayı önlemek için, bu adımda "BUNUN YERİNE" tetikleyicisini kullanacağız.

Aşağıdaki ekran görüntüsü, INSTEAD OF tetikleyicisinin oluşturulmasını göstermektedir.

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

Code açıklama

• Code 39 hattı: 'guru99_emp_view' görünümündeki 'UPDATE' olayı için ROW düzeyinde INSTEAD OF tetikleyicisinin oluşturulması. Bu tetikleyici, 'dept' temel tablosundaki konumu güncellemek için gerekli güncelleme ifadesini içerir.
• Code 44 hattı: Güncelleme ifadesi, güncellemeden önce ve sonra sütunların değerini bulmak için ':NEW' ve ':OLD' ifadelerini kullanır.

Çıktı:

Trigger Created

) 6 Adım INSTEAD OF tetikleyicisinden sonra görünümün güncellenmesi. Artık hata görünmeyecek, çünkü "INSTEAD OF tetikleyicisi" bu karmaşık görünümün güncelleme işlemini halledecek. Kod yürütüldüğünde, XXX çalışanının konumu "Japonya"dan "Fransa"ya güncellenecektir.

Aşağıdaki ekran görüntüsü, INSTEAD OF tetikleyicisi aracılığıyla gerçekleştirilen başarılı güncellemeyi ve yenilenmiş görünümü göstermektedir.

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

SELECT * FROM guru99_emp_view;

Code Açıklama:

• Code 49-53. satırlar: “XXX” konumunun “FRANSA” olarak güncellenmesi başarılı oldu çünkü “INSTEAD OF” tetikleyicisi, görünüm üzerindeki gerçek güncelleme ifadesini durdurdu ve temel tablo güncellemesini gerçekleştirdi.
• Code 55 hattı: Güncellenen kaydın doğrulanması.

Çıktı:

PL/SQL procedure successfully completed

Bileşik Tetikleyici

Bileşik tetikleyici, tek bir tetikleyici gövdesinde dört farklı zamanlama noktası için eylemler belirlemenizi sağlayan bir tetikleyicidir. Desteklediği dört farklı zamanlama noktası aşağıdadır.

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

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

Aşağıdaki ekran görüntüsü, dört zamanlama bölümüyle birlikte bileşik tetikleyici sözdizimini göstermektedir.

CREATE [ OR REPLACE ] TRIGGER <trigger_name>
FOR
[INSERT | UPDATE | DELETE.......]
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İRLEŞİK' bir tetikleyicinin oluşturulmasını göstermektedir.
• Bildirimsel bölüm, tetikleyici gövdesindeki tüm yürütme blokları için ortaktır.
• Bu dört zamanlama bloğu herhangi bir sırada olabilir. Dört zamanlama bloğunun da olması zorunlu değildir. Sadece gerekli olan zamanlamalar için bir BİLEŞİK tetikleyici oluşturabiliriz.

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

Aşağıdaki ekran görüntüsü, bileşik tetikleyici örneğini ve çıktısını göstermektedir.

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;

Code Açıklama:

• Code 2-10. satırlar: Bileşik tetikleyicinin oluşturulması. Bu tetikleyici, maaş alanını varsayılan 5000 değeriyle doldurmak için SATIR ÖNCESİ zamanlama seviyesi için oluşturulmuştur. Bu işlem, kayıt tabloya eklenmeden önce maaş alanını varsayılan '5000' değerine değiştirecektir.
• Code 11-14. satırlar: Kaydı 'emp' tablosuna ekleyin.
• Code 16 hattı: Eklenen kaydın doğrulanması.

Çıktı:

Trigger created

PL/SQL procedure successfully completed.

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

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

Aşağıda tetikleyicileri etkinleştirme/devre dışı bırakma sözdizimi yer almaktadır.

ALTER TRIGGER <trigger_name> [ENABLE|DISABLE];
ALTER TABLE <table_name> [ENABLE|DISABLE] ALL TRIGGERS;

Sözdizimi Açıklaması:

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

SSS