Trigger'lar insert, update ve delete ifadelerinden sonra çalıştırılan procedure'lardır. Bunlar tablolar üzerinde tanımlanırlar. Trigger'lar SQL ve PL\SQL ifadelerini içerebilirler ve diğer PL\SQL birimlerini çağırabilirler. Veritabanında saklanırlar. Belirli durumlarda çağırılırlar.
Trigger'ların procedurelardan farkı; trigger'lar Oracle tarafından tetiklenirken procedure'lar başka trigger'lar, application'lar veya kullanıcı tarafından çalıştırılabilirler.
Trigger'lar veritabanında tablolardan farklı bir yerde tutulurlar. Sadece tablolar üzerinde tanımlanabilirler. View'lar üzerine tanımlanamasalarda, view'ların üzerinde bir değişiklik yapılmaya çalışıldığında view'ın üzerine tanımlandığı tablolarda kaydedilmiş trigger'lar çalıştırılır.
Trigger'ın Tetiklendiği Durum Tipleri:
Herhangi bir DML(Data Manipulation Language) ifadesi : INSERT, UPDATE, DELETE
Herhangi bir DDL(Data Definition Language) ifadesi: CREATE, ALTER, DROP
Herhnagi bir veritabanı işleminde: Örnek olarak Logon, Logoff, Startup, Shutdown işleminde.
Trigger'ların Kullanım Amaçları:
Trigger'lar ile DML ifadelerinin kullanımı kısıtlanabilir. Normalde bunun için role ve yetkiler kullanılırken, trigger'lar ile kullanıcının adına bakılmaksızın tablolar üzerine giriş çıkışlar kısıtlanır ve değişiklik yapılması düzenlenir.
Trigger'ların diğer genel kullanım alanlar:
-Otomatik olarak ilgili sütunlar için değerler üretmesi
-Uygun olmayan transaction'ları engellemek için
-Güvenlik katmanı oluşturmak için
-Loglama yapmak için
-Yapılan işlemlerle ilgili kayıt oluşturmak için
-Tablolar arasında eşleme ve eşitleme yapmak için
-Tablo erişimiyle ilgili bilgiler toplamak için
Not: Transaction Nedir?
Transaction olarak belirttiğimiz sorgular ya DML ifadeleridir ya da DDL ifadeleridir. DDL ifadeleri gönderildiğinde otomatik olarak commit edilirken, DML ifadeleri gönderildiğinde ertesinde commit veya rollback sorgularıda gönderilmelidirler ki transaction bitsin.
Trigger'lar ve Constraint'ler(Kısıtlar):
Kısıtlarda tablo içerisindeki veriler düzenlenir ve gelecek olan transactionlarda kısıtlar uygulanır. Yani tablo içerisindeki bütün verilerin ve gelecekde girilecek verilerin constraint'lere uygun olabilieceğini söyleyebiliriz. Bir tabloya kısıt koyulduğunda içerideki datalarında buna uygun olması gerekir. Öte yandan trigger'lar tanımlandıkları zaman tablo içerisindeki verileri kontrol etmezler. Sadece sonradan girilen verileri kontrol ederler ve buna göre içerilerindeki kodları çalıştırırlar.
Trigger Zamanlama Tipleri:
1-Before: Trigger içerisindeki işlemler tablo üzerine gerçekleşecek işlemden önce çalışır.
2-After: Trigger yapılan işlemden sonra çalışır.
3-Instead of: SQL ifadesi yerine Trigger'ın içerisindeki ifadeler çalıştırılır. Bu işlem genelde view'lar üzerine işlem yapmak istendiğinde kullanılır. Böylece view'a ulaşılmak istendiğinde aslında onu oluşturan tablolar oluşturulur.
Statement Level Triggers ve Row Level Triggers:
Statement Level Triggers
|
Row Level Triggers
|
Standart Trigger tipidir.
|
“For Each Row” ifadesini kullanır.
|
Triggerı çalıştıran durum için çalıştırılır. Yani eğer update,delete veya insert ifadesi
birden fazla satırı değiştirse bile, trigger bir kere çalışır.
|
Row level trigger’da update,delete veya insert ifadelerinden herhangi
birisinin değiştirdiği her satır için trigger çalıştırılır.
|
Herhangi bir değişikliğe neden olmasa da bir kere çalıştırılır.
|
Her hiçbir değişikliğe neden olmıyorsa tetiklenmez.
|
Trigger'ların Kısımları:
Bir trigger'ın basitçe 3 tane kısmı vardır.
1-Trigger'ın hangi tablo üzerinde ne zaman çalışacağını gösteren kısım.
2-Trigger kısıtı - Ne zaman tetikleneceğini gösteren kısım.
3-Trigger aksiyonu - Bütün koşullar oluştuğunda trigger'ın hangi işlemleri yapacağını gösteren bölüm.
1-Trigger Olayı:
Trigger'ın çalışmasını sağlayan ilk kısımdır. Burada hangi tablonun hangi sütununun üzerine tanımlı olduğu ve DML ifadesinin öncesinde mi sonrasında mı çalışacağını tanımlarız.
Genel ifade:
[AFTER|BEFORE] [INSERT|UPDATE|DELETE] OF [KOLON_ADI] ON [TABLO_ADI]
Update ifadesinde birden fazla kolon adı belirtilebilinirken delete ve insert ifadelerinde bütün satır etkilendiği için tek bir kolon adı verilebilinir.
2- Trigger Kısıtı:
Trigger kısıtlarında boolean bir ifade verilir. Bu ifade doğru olduğunda ancak devamındaki kod çalıştırılır.
WHEN (trigger_kısıt =true)
3- Trigger Aksiyonu:
Trigger aksiyonu içinde SQL ifadeleri içeren bir PL\SQL procedure'udür. Trigger tetiklendikten ve trigger kısıtı doğru çıktıktan sonra trigger aksiyonu çalıştırılır.
Koşullu İfadeler:
Koşullu ifadeler olarak belirttiğimiz anahtar kelimeler birden fazla trigger tipi kullanıldığı zaman işe yarayabilirler. Örnekte belirttiğimiz trigger birden fazla durumda(insert,update,delete) tetikleneceği için bunları ayırmak için içerisinde "DELETING", "INSERTING" veya "UPDATING" ifadelerinin kullanılması gerekir.
CREATE OR REPLACE TRIGGER secure_emp BEFORE
INSERT OR UPDATE OR DELETE ON employees
BEGIN
IF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN')) OR
(TO_CHAR(SYSDATE,'HH24')
NOT BETWEEN '08' AND '18') THEN
IF DELETING THEN RAISE_APPLICATION_ERROR(
-20502,'You may delete from EMPLOYEES table'||
'only during normal business hours.');
ELSIF INSERTING THEN RAISE_APPLICATION_ERROR(
-20500,'You may insert into EMPLOYEES table'||
'only during normal business hours.');
ELSIF UPDATING ('SALARY') THEN
RAISE_APPLICATION_ERROR(-20503, 'You may '|| 'update SALARY only normal during business hours.');
ELSE RAISE_APPLICATION_ERROR(-20504,'You may'||
' update EMPLOYEES table only during'||
' normal business hours.');
END IF;
END IF;
END;
DML Trigger Örneği:
Dml trigger'ımızda yaptığımız işleme göre trigger insert veya update işlemlerinde tetiklenmektedir. Tetiklendiğinde de tetikleyen ifadenin etkilediği bütün satırlar için belirli şartlara göre trigger içinde sorgular çalışır. Eğer belirleyen şartlara uymazsa hata verir. Raise_application_error çalıştığında içinde bulunduğu blok biter ve o blok hata döndürür.
CREATE OR REPLACE TRIGGER restrict_salary
BEFORE INSERT OR UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
IF NOT (:NEW.job_id IN ('AD_PRES', 'AD_VP'))
AND :NEW.salary > 15000 THEN
RAISE_APPLICATION_ERROR (-20202,
'Employee cannot earn more than $15,000.');
END IF;
END;/
OLD ve NEW Belirteçleri:
DML ifadeleri
|
:OLD
|
:NEW
|
Insert
|
Null
|
Yeni girilen değer
|
Update
|
Update yapılmadan önceki değer
|
Yeni update edilen değer
|
Delete
|
Silinmeden önceki değer
|
Null
|
OLD ve NEW Belirteç Örneği:
Burada verilen örnekte employees adlı tabloya yapılan her giriş için bu tabloyla ilgili loglama yapan tabloya yani audit_emp'e değerler girilir.
CREATE OR REPLACE TRIGGER audit_emp_values
AFTER DELETE OR INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO audit_emp(user_name, time_stamp, id,
old_last_name, new_last_name, old_title,
new_title, old_salary, new_salary)
VALUES (USER, SYSDATE, :OLD.employee_id,
:OLD.last_name, :NEW.last_name, :OLD.job_id,
:NEW.job_id, :OLD.salary, :NEW.salary);
END;
/
Trigger'larda When ifadesi:
Trigger'lardaki when ifadesi trigger'ın çalışmasıyla ilgili bir koşul olarak kullanılabilir. Böylece her satır için trigger çalıştırıldığında trigger'ın içindeki ifadelerin uygulanıp uygulanmayacağını belirler.
CREATE OR REPLACE TRIGGER derive_commission_pct
BEFORE INSERT OR UPDATE OF salary ON employees
FOR EACH ROW
WHEN (NEW.job_id = 'SA_REP')
BEGIN
IF INSERTING THEN
:NEW.commission_pct := 0;
ELSIF :OLD.commission_pct IS NULL THEN
:NEW.commission_pct := 0;
ELSE
:NEW.commission_pct := :OLD.commission_pct+0.05;
END IF;
END;
/
Trigger'lardaki İşlem Sırası:
1-Önce "Before" triggerı varsa çalışır.
2-Sonra varsa "Before Row" triggerı kullanılır.
3-Ertesinde triggeri içerisinde yapılacak işlemler yapılır
4-"After Row" trigger'ı bulunuyorsa o çalıştırılır.
5-"After" triggerı tetiklenir.
Trigger'la ilgili Tablolar:
user|all|dba_triggers
Örnekler:
1-
CREATE TRIGGER rt BEFORE UPDATE OR DELETE OR INSERT ON sal
FOR EACH ROW BEGIN
stat.rowcnt := stat.rowcnt + 1;
END;
/
Buradaki örneğimizde yarattığımız trigger, update,delete ve insert ifadelerinden sonra tablodaki her değer için bir değeri bir arttırmaktadır.
2-
CREATE TRIGGER at AFTER UPDATE OR DELETE OR INSERT ON sal
DECLARE
typ CHAR(8);
hour NUMBER;
BEGIN
IF updating
THEN typ := 'update'; END IF;
IF deleting THEN typ := 'delete'; END IF;
IF inserting THEN typ := 'insert'; END IF;
hour := TRUNC((SYSDATE - TRUNC(SYSDATE)) * 24);
UPDATE stat_tab
SET rowcnt = rowcnt + stat.rowcnt
WHERE utype = typ
AND uhour = hour;
IF SQL%ROWCOUNT = 0 THEN
INSERT INTO stat_tab VALUES (typ, stat.rowcnt, hour);
END IF;
EXCEPTION
WHEN dup_val_on_index THEN
UPDATE stat_tab
SET rowcnt = rowcnt + stat.rowcnt
WHERE utype = typ
AND uhour = hour;
END;
/
Buradaki örneğimizde de yine sütun belirtmeden tablomuza yapılan bir update,delete veya insert transaction'ınından sonra bir kere çalışmak üzere, her trigger update için çalıştıysa bir kolon değeri update diye, diğer işlemler için de aynı şekilde çalışacak şekilde bir trigger yaratmaktayız. Bu örneğin genel amacı bir tabloya yapılan güncellemeleri loglamaktır.
3-
CREATE OR REPLACE TRIGGER DBA.Trigger
BEFORE INSERT OR UPDATE
ON DBA.Logs
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
TYPE session_type IS TABLE OF SYS.V_$SESSION%ROWTYPE;
session_tab session_type;
BEGIN
IF ( UPPER (:NEW.UPDT_BY_OSUSER) ^= UPPER (:OLD.UPDT_BY_OSUSER)
OR UPPER (:NEW.UPDT_BY_MACHINE) ^= UPPER (:OLD.UPDT_BY_MACHINE))
THEN
RAISE_APPLICATION_ERROR (-20000, 'You Cannot Update UPDT_BY* columns!');
ELSE
SELECT s.*
BULK COLLECT INTO session_tab
FROM sys.v_$session s
WHERE s.audsid = USERENV ('SESSIONID') AND ROWNUM = 1;
:NEW.UPDT_BY_USER := SESSION_TAB (1).USERNAME;
:NEW.UPDT_BY_OSUSER := SESSION_TAB (1).OSUSER;
:NEW.UPDT_BY_MACHINE := SESSION_TAB (1).MACHINE;
:NEW.UPDT_TIMESTAMP := SYSDATE;
END IF;
END Trigger;
/
Trigger'da var olan eski değerler ile tablonun içerisine aktarılacak yeni değerleri ayırt etmek için bir ifade bulunmaktadır.
"
REFERENCING NEW AS NEW OLD AS OLD"
Bu kullanımla satırların hangi sütununa ekleme veya değişiklik yapılıyorsa :NEW veya :OLD şeklinde sütunun değişiklikten önceki ve sütunun değişiklikten sonraki hallerine referanslar yapılabilinir. Sütuna insert edilmeye çalışılan ve update edilmeye çalışılıan değer :NEW ile erişilebilinirken eski değere :OLD ile erişilebilinir. Bu procedure içerisinde eski değer yeni değerle karşılaştırılarak bir tutarsızlık varsa hata verdirilmektedir. Ertesinde de eğer hata çıkmaz ise kullanıcının girdiği değerler başka bir sütuna belirli değerlerle kaydedilir.
ORA-25001 Hatası:
ORA-25001 hatası view'lar üzerine trigger yaratmaya çalıştığımızda olmaktadır. Bu sorunun çözümü yaratılacak trigger'ı instead of ile trigger'ı ile değiştirmektir.
