Oracle Veritabanı: Trigger'lar - Trigger Nedir? - Triggers - What are Triggers?

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.