MariaDB Trigger (Tetikleyici): CREATE TRIGGER, BEFORE ve AFTER, NEW ve OLD

CREATE TRIGGER sözdizimi

CREATE [OR REPLACE]
    [DEFINER = { user | CURRENT_USER | role | CURRENT_ROLE }]
    TRIGGER [IF NOT EXISTS]
            trigger_name trigger_time {trigger_event [OR trigger_event] ...}
    ON tbl_name FOR EACH ROW
    [{ FOLLOWS | PRECEDES } other_trigger_name]
    trigger_stmt

trigger_time:
    BEFORE
  | AFTER

trigger_event:
    INSERT
  | UPDATE [OF column_name [, column_name] ...]
  | DELETE

Tetikleyici kalıcı bir tabloya bağlanmalıdır; TEMPORARY tablolara ve view'lara tetikleyici tanımlanamaz. Oluşturmak için ilgili tabloda TRIGGER yetkisi gerekir. Aynı tabloda aynı zaman ve olay için birden fazla tetikleyici olabilir. DEFINER yan tümcesi, tetikleyici çalıştığında yetkilerin hangi hesaba göre denetleneceğini belirler ve başka bir hesap yazmak için SET USER yetkisi ister. Belgelere göre CREATE TRIGGER atomik bir DDL işlemidir.

Önemli bir sürüm farkı vardır: MariaDB 12.0'dan itibaren tek tetikleyicide BEFORE INSERT OR UPDATE OR DELETE gibi birden fazla olay yazılabilir; 12.0 öncesinde yalnızca tek olay kabul edilir. Çoklu olaylı tetikleyicilerde gövde içinde hangi olayın tetiklediğini anlamak için INSERTING, UPDATING ve DELETING yüklemleri kullanılır. Söz diziminde görülen UPDATE OF sutun, ... biçimi tetikleyiciyi belirli sütunların güncellenmesine bağlar; bu sütunları listeleyen INFORMATION_SCHEMA.TRIGGERED_UPDATE_COLUMNS tablosu belgelere göre MariaDB 12.2'den itibaren mevcuttur.

CREATE TRIGGER t1_b_any BEFORE INSERT OR UPDATE OR DELETE ON t1 FOR EACH ROW
BEGIN
  IF INSERTING THEN
    INSERT INTO t2 VALUES (NEW.a, 'INSERTING');
  ELSEIF UPDATING THEN
    INSERT INTO t2 VALUES (NEW.a, 'UPDATING');
  ELSEIF DELETING THEN
    INSERT INTO t2 VALUES (OLD.a, 'DELETING');
  END IF;
END

NEW ve OLD: hangi satıra ne zaman erişilir?

NEW ve OLD takma adları MariaDB ve MySQL'e özgü eklentilerdir ve büyük-küçük harf duyarsızdır. INSERT tetikleyicisinde eski satır olmadığı için yalnızca NEW, DELETE tetikleyicisinde yeni satır olmadığı için yalnızca OLD kullanılabilir. UPDATE tetikleyicisinde ikisi birlikte bulunur: OLD güncellemeden önceki, NEW sonraki değerleri tutar.

OLD sütunları her zaman salt okunurdur. BEFORE tetikleyicisinde UPDATE yetkiniz varsa SET NEW.sutun = değer ile yazılacak değeri değiştirebilirsiniz; bu sayede eklenen veya güncellenen satırı düzeltebilirsiniz. AFTER tetikleyicisinde aynı SET etkisizdir, çünkü satır değişikliği çoktan gerçekleşmiştir. Belgelere göre tetikleyiciler üretilmiş (generated) sütunlara NEW veya OLD ile başvuramaz.

DELIMITER //

CREATE TRIGGER trg_limit_population BEFORE UPDATE ON country_stats
FOR EACH ROW
BEGIN
    -- Ensure population is at least 1
    IF NEW.population < 1 THEN
        SET NEW.population = 1;
    -- Cap population at 2 billion for data integrity
    ELSEIF NEW.population > 2000000000 THEN
        SET NEW.population = 2000000000;
    END IF;
END;
//

DELIMITER ;

İlk örnek: kayıt sayacı

Belgelerdeki tanıtım örneği, bir tabloya her yeni satır eklendiğinde başka bir tablodaki sayacı artırır. Tetikleyicinin parçaları bir ad, bir zaman (AFTER), bir olay (INSERT), bağlı olduğu tablo ve çalıştırılacak ifadedir. Tek ifadeli gövdelerde BEGIN ... END ve dolayısıyla DELIMITER gerekmez.

CREATE TABLE animals (id MEDIUMINT(9)
NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (`id`));

CREATE TABLE animal_count (animals INT);

INSERT INTO animal_count (animals) VALUES(0);

CREATE TRIGGER increment_animal
AFTER INSERT ON animals
FOR EACH ROW
UPDATE animal_count SET animal_count.animals = animal_count.animals+1;

INSERT INTO animals (name) VALUES('aardvark');
INSERT INTO animals (name) VALUES('baboon');

SELECT * FROM animal_count;
+---------+
| animals |
+---------+
|       2 |
+---------+

Gövdede başka bir tabloya başvururken tablo adını açıkça yazmanız gerekir; değişen satırın sütunlarına ise NEW.sutun biçiminde erişilir. Birden çok ifade gerektiğinde gövde BEGIN ... END içine alınır ve komut satırında geçici olarak başka bir ayırıcı tanımlanır.

Veri doğrulama: BEFORE tetikleyicisi ve SIGNAL

Bir BEFORE tetikleyicisi içinde SIGNAL ile hata üretirseniz satır yazılmaz ve tetikleyen ifade hata ile sonlanır. Belgelerdeki örnek, e-posta sütununun basit bir kalıba uymadığı satırları reddeder; tablo MyISAM olduğu halde BEFORE tetikleyicisi olduğu için satır eklenmez:

DELIMITER //
CREATE TRIGGER bi_user
  BEFORE INSERT ON user
  FOR EACH ROW
BEGIN
  IF NEW.email NOT LIKE '_%@_%.__%' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Email field IS NOT valid';
  END IF;
END; //
DELIMITER ;

INSERT INTO user (first_name, last_name, email) VALUES ('John', 'Doe', 'john_doe.example.net');
ERROR 1644 (45000): Email field is not valid

SELECT * FROM user;
Empty set (0.00 sec)

Basit kurallar için önce CHECK kısıtlarını düşünün; tetikleyici, kuralın başka tabloları okuması veya değeri düzeltmesi gerektiğinde daha uygundur. Kısıtların kendisi için foreign key ve CHECK kısıtlamaları yazısına bakın. Belgelere göre SIGNAL veya RESIGNAL ile '01' ile başlayan bir uyarı üretmek de tetikleyici içinde hata gibi ele alınır.

Hangi ifadeler tetikleyiciyi çalıştırır?

INSERT tetikleyicileri yalnızca INSERT ile değil, her satır için LOAD DATA ve LOAD XML ile de çalışır. DELETE tetikleyicileri DELETE ve REPLACE ile çalışır; ama DROP TABLE ve TRUNCATE TABLE DELETE kullanmadığı için hiçbir tetikleyiciyi çalıştırmaz. Bir bölümü (partition) silmek de DELETE tetikleyicilerini çalıştırmaz. Belgelerin kısıt listesine göre yabancı anahtar eylemleri (örneğin ON DELETE CASCADE ile silinen alt satırlar) da tetikleyicileri etkinleştirmez.

İki ifadenin tetikleyici akışı özellikle dikkat ister:

Bu tablo, örneğin bir denetim (audit) tablosuna yazan tetikleyicilerde beklenmedik kayıtları açıklar: REPLACE var olan bir satırı değiştirirken arka planda silip yeniden eklediği için hem silme hem ekleme tetikleyicileri çalışır. Bu ifadelerin davranışı için INSERT, UPDATE, DELETE ve toplu yükleme için LOAD DATA ve mariadb-import yazılarına bakın.

Hatalar ve transaction davranışı

Transaction destekleyen bir motorda (InnoDB gibi) tetikleyiciler, onları çalıştıran ifadeyle aynı transaction içinde yürütülür. Bu yüzden tetikleyicide hata olursa ya da tetikleyici BEFORE tetikleyicisiyse, hem tetikleyici hem asıl ifade geri alınır. Transaction desteklemeyen bir motorda (MyISAM gibi) ise AFTER tetikleyicisindeki hata asıl ifadeyi geri almaz: satır yazılmış olur, tetikleyicinin yapması gereken iş yapılmamış olur.

Belgeler bunu aynı hatalı tetikleyiciyle iki kez gösterir. Tetikleyici var olmayan bir sütunu güncellemeye çalışır ve her iki durumda da ERROR 1054 (42S22): Unknown column 'animal_count.id' in 'field list' hatası alınır. InnoDB tablosunda ardından yapılan SELECT * FROM animals boş küme döndürür; MyISAM tablosunda ise aardvark satırı tabloda kalmıştır. Tutarlılığın önemli olduğu her yerde tetikleyicili tabloları transaction destekleyen motorlarda tutmanın nedeni budur. Ayrıntılar için transaction ve izolasyon seviyeleri yazısına bakın.

Birden fazla tetikleyici: FOLLOWS ve PRECEDES

Aynı tablo, zaman ve olay için birden çok tetikleyici tanımlandığında varsayılan olarak yeni tetikleyici en sona eklenir. FOLLOWS diger_tetikleyici yeni tetikleyiciyi belirtilen tetikleyiciden sonra, PRECEDES ise önce çalıştırır. Belgelere göre bu yan tümceler tetikleyici tanımında saklanmaz, ama sıra zamanla değişmez ve mariadb-dump gibi yedekleme yöntemleri de sırayı değiştirmez. Geçerli sırayı INFORMATION_SCHEMA.TRIGGERS tablosundaki ACTION_ORDER sütunundan okuyabilirsiniz.

SELECT trigger_name, action_order FROM information_schema.triggers 
  WHERE event_object_table='t1';

Tetikleyici kısıtları

Tetikleyiciler hem saklı rutinlerin genel kısıtlarına hem de saklı fonksiyonların ek kısıtlarına tabidir: sonuç kümesi döndüremez, açık veya örtük commit yapamaz, kendisini çalıştıran ifadenin kullandığı tabloyu değiştiremez. Belgelerin tetikleyicilere özgü listesi şunları ekler:

• Tetikleyiciler her zaman satır başına çalışır; standarttaki FOR EACH STATEMENT desteklenmez.
• mysql, information_schema ve performance_schema veritabanlarındaki tablolar üzerinde işlem yapamaz.
• RETURN kullanılamaz; tetikleyiciden hemen çıkmak için LEAVE kullanılır.
• Yabancı anahtar eylemleri tetikleyicileri çalıştırmaz.
• Önbelleğe yüklenmiş bir tetikleyici, tablo meta verisi değişince otomatik yeniden yüklenmez ve eski meta veriyle çalışabilir.
• Satır tabanlı replikasyonda varsayılan olarak tetikleyiciler birincil sunucuda çalışır ve etkileri replikalara aktarılır; replikada çalıştırmak ayrıca yapılandırılabilir.

LOCK TABLES kullanan uygulamalar için bir ayrıntı daha vardır: bir tabloyu kilitlediğinizde, o tablonun tetikleyicilerinin okuduğu tablolar okuma kilidiyle, yazdığı tablolar yazma kilidiyle örtük olarak kilitlenir. Belgelerdeki örnekte table1 yazma kilidi alındığında, tetikleyicinin ekleme ve güncelleme yaptığı table2 ve table3 de yazma kilidi alır, yalnızca okuduğu table4 ise okuma kilidi alır. Kilit beklemelerini izlemek için SHOW PROCESSLIST ve KILL yazısına bakın.

Tetikleyicileri listelemek, değiştirmek ve silmek

Bir veritabanındaki tetikleyicileri SHOW TRIGGERS [FROM db_adi] [LIKE 'kalip'] ile listeleyebilir, tam tanımı SHOW CREATE TRIGGER ile görebilir ya da INFORMATION_SCHEMA.TRIGGERS tablosunu sorgulayabilirsiniz. Var olan bir tetikleyiciyi aynı adla yeniden oluşturmaya çalışmak 1359 hatası verir; OR REPLACE eskisini silip yenisini yazar, IF NOT EXISTS ise hata yerine not üretir.

CREATE DEFINER=`root`@`localhost` TRIGGER increment_animal
  AFTER INSERT ON animals FOR EACH ROW
    UPDATE animal_count SET animal_count.animals = animal_count.animals+1;
ERROR 1359 (HY000): Trigger already exists

CREATE OR REPLACE DEFINER=`root`@`localhost` TRIGGER increment_animal
  AFTER INSERT ON animals  FOR EACH ROW
    UPDATE animal_count SET animal_count.animals = animal_count.animals+1;
Query OK, 0 rows affected (0.12 sec)

DROP TRIGGER [IF EXISTS] [db_adi.]tetikleyici_adi bir tetikleyiciyi siler ve tablodaki TRIGGER yetkisini ister. Bir tablo silindiğinde ona bağlı tüm tetikleyiciler de silinir. Tabloyu yeniden oluşturduğunuzda tetikleyicileri de yeniden tanımlamanız gerektiğini unutmayın.

Uygulamalı mini örnek: fiyat değişikliği günlüğü

Aşağıdaki tetikleyici, bir ürünün fiyatı değiştiğinde eski ve yeni fiyatı ayrı bir tabloya kaydeder. AFTER UPDATE kullanıyoruz, çünkü kaydı ancak güncelleme gerçekten yapıldıktan sonra yazmak istiyoruz; fiyat değişmediyse kayıt atlanır. Tablo adları bize aittir, bu yüzden çıktı göstermiyoruz.

CREATE TABLE fiyat_gecmisi (
  id INT AUTO_INCREMENT PRIMARY KEY,
  urun_id INT NOT NULL,
  eski_fiyat DECIMAL(10,2),
  yeni_fiyat DECIMAL(10,2),
  degisim DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

DELIMITER //

CREATE TRIGGER au_urun_fiyat
AFTER UPDATE ON urunler
FOR EACH ROW
BEGIN
  IF NOT (OLD.fiyat <=> NEW.fiyat) THEN
    INSERT INTO fiyat_gecmisi (urun_id, eski_fiyat, yeni_fiyat)
    VALUES (NEW.urun_id, OLD.fiyat, NEW.fiyat);
  END IF;
END //

DELIMITER ;

Karşılaştırmada = yerine NULL güvenli <=> operatörünü kullanmak, fiyatın NULL'dan bir değere geçtiği durumu da yakalar. Her iki tablo da InnoDB olduğu için günlük kaydı başarısız olursa fiyat güncellemesi de geri alınır. Tarih ve saat değerleri için tarih ve saat fonksiyonları yazısına bakabilirsiniz.

Tetikleyici mi, başka bir araç mı?

Tetikleyiciler güçlüdür ama görünmezdir: tabloya yazan bir geliştirici, arka planda başka tabloların da değiştiğini fark etmeyebilir. Bu yüzden önce daha açık araçların işinizi görüp görmediğine bakmak iyi bir alışkanlıktır. Tek bir sütunun geçerlilik kuralı için CHECK kısıtı, tablolar arası tutarlılık için yabancı anahtar, başka sütunlardan hesaplanan değerler için üretilmiş (generated) sütunlar daha sade çözümlerdir. Bir satırın geçmiş sürümlerini tutmak istiyorsanız MariaDB'nin system-versioned temporal tabloları bunu tetikleyici yazmadan sağlar.

Tetikleyici şu durumlarda öne çıkar: değişikliği başka bir tabloya özet veya günlük olarak yansıtmak, yazılacak değeri başka tablolardan okunan bilgiyle düzeltmek ya da birden fazla uygulamanın aynı tabloya yazdığı ortamlarda kuralı tek yerde zorlamak. Zamana bağlı, satır değişikliğinden bağımsız işler (örneğin her gece eski kayıtları silmek) için ise tetikleyici değil Event Scheduler doğru araçtır.

Sık yapılan hatalar

• AFTER tetikleyicisinde SET NEW kullanmak: Değişiklik çoktan yapıldığı için etkisizdir; değeri değiştirmek için BEFORE kullanın.
• Tetikleyicinin kendi tablosunu güncellemeye çalışmak: Saklı fonksiyon kısıtları gereği tetikleyici, kendisini çalıştıran ifadenin kullandığı tabloyu değiştiremez. Aynı satırı değiştirmek için BEFORE içinde SET NEW.sutun yazın.
• TRUNCATE'in DELETE tetikleyicisini çalıştıracağını sanmak: TRUNCATE TABLE ve DROP TABLE hiçbir tetikleyiciyi çalıştırmaz.
• CASCADE silmelerinin tetikleyiciden geçeceğini varsaymak: Yabancı anahtar eylemleri tetikleyicileri etkinleştirmez; denetim kaydı gerekiyorsa bunu ayrıca planlayın.
• MyISAM tablolarda tutarlılık beklemek: AFTER tetikleyicisi hata verse bile asıl satır yazılmış kalır. InnoDB kullanın.
• 12.0 öncesi sunucuda çoklu olay yazmak: INSERT OR UPDATE sözdizimi 12.0'dan itibaren geçerlidir; eski sürümlerde her olay için ayrı tetikleyici tanımlayın.

Sık Sorulan Sorular

MariaDB statement düzeyinde tetikleyici destekliyor mu?

Hayır. Belgelere göre tetikleyiciler her zaman satır başına çalışır ve FOR EACH STATEMENT desteklenmez. Bin satırı güncelleyen bir ifade tetikleyiciyi bin kez çalıştırır; bu yüzden gövdeyi kısa tutmak önemlidir.

Tetikleyici içinden saklı yordam çağırabilir miyim?

CALL ile çağırabilirsiniz, ancak çağrılan yordam tetikleyicinin kısıtlarını devralır: sonuç kümesi döndüremez ve commit yapamaz.

Tetikleyiciler replikasyonda iki kez çalışır mı?

Satır tabanlı replikasyonda varsayılan olarak tetikleyiciler yalnızca birincil sunucuda çalışır ve ürettikleri satır değişiklikleri replikalara aktarılır. İfade tabanlı günlükte ise belgelere göre tetikleyiciler günlük açısından her zaman deterministik kabul edilir. Replikasyon için MariaDB replikasyon kurulumu yazısına bakın.

Bir tetikleyiciyi geçici olarak devre dışı bırakabilir miyim?

Yerel belgelerde tetikleyiciler için ENABLE/DISABLE gibi bir seçenek anlatılmaz; event'lerde olan bu seçenek tetikleyicilerde yoktur. Tetikleyiciyi kaldırmak için DROP TRIGGER, yeniden eklemek için CREATE TRIGGER kullanmanız gerekir.

Marka adları sahiplerine aittir. Bu yazı bağımsız bir teknik rehberdir; adı geçen kuruluşlarla bayilik veya ortaklık ilişkisi bulunduğu anlamına gelmez.

İlgili içerikler: MariaDB öğrenme rehberi · Stored procedure ve function · Event Scheduler · Foreign key ve CHECK kısıtlamaları · Transaction ve izolasyon · INSERT, UPDATE, DELETE · System-versioned tablolar