MySQL’de Stored Procedure ve Trigger Kullanımı

yazar

Veritabanı yönetiminde işlerin gerçekten karmaşıklaştığı noktalardan biri, uygulama katmanında mı yoksa veritabanı katmanında mı iş mantığını tutacağın kararıdır. Stored procedure’ler ve trigger’lar bu tartışmanın tam merkezinde yer alır. Yıllarca “her şeyi uygulama katmanında yap” diyenler oldu, ama gerçek dünyada bu her zaman mümkün değil. Özellikle birden fazla uygulamanın aynı veritabanını kullandığı ortamlarda, ya da performansın kritik olduğu sistemlerde stored procedure ve trigger’lar hayat kurtarır. Bu yazıda MySQL üzerinde bu iki güçlü özelliği, gerçek senaryolarla ve pratik örneklerle inceleyeceğiz.

Stored Procedure Nedir ve Neden Kullanılır?

Stored procedure, veritabanı sunucusunda saklanan ve tekrar tekrar çağrılabilen SQL kod bloklarıdır. Bir nevi veritabanı fonksiyonu gibi düşünebilirsin. Avantajları şu şekilde sıralanabilir:

  • Ağ trafiğini azaltır: Onlarca sorgu yerine tek bir prosedür çağrısı gider
  • Kod tekrarını önler: Aynı mantığı her uygulamada yazmak zorunda kalmazsın
  • Güvenliği artırır: Tablolara direkt erişim yerine prosedür üzerinden erişim sağlanabilir
  • Performans: MySQL ilk çalıştırmada execution plan oluşturur, sonraki çağrılarda bu plan kullanılır
  • Merkezi yönetim: İş mantığı değiştiğinde tek bir yeri güncellersin

Tabii dezavantajları da var. Debug etmek zordur, versiyon kontrolü uygulamak karmaşıklaşabilir ve taşınabilirlik (portability) sorunu çıkabilir. Ama bunları bilerek kullandığında çok güçlü bir araç.

İlk Stored Procedure’ü Yazmak

MySQL’de stored procedure yazmadan önce delimiter’ı değiştirmemiz gerekir. Çünkü procedure içindeki noktalı virgüller MySQL’i karıştırır.

mysql -u root -p
mysql> DELIMITER //

CREATE PROCEDURE kullanici_ozeti(IN kullanici_id INT)
BEGIN
    DECLARE toplam_siparis INT DEFAULT 0;
    DECLARE toplam_tutar DECIMAL(10,2) DEFAULT 0.00;
    
    SELECT COUNT(*), SUM(tutar)
    INTO toplam_siparis, toplam_tutar
    FROM siparisler
    WHERE musteri_id = kullanici_id;
    
    SELECT 
        k.ad,
        k.soyad,
        k.email,
        toplam_siparis AS siparis_sayisi,
        toplam_tutar AS toplam_harcama
    FROM kullanicilar k
    WHERE k.id = kullanici_id;
END //

DELIMITER ;

Bu prosedürü çağırmak için:

mysql> CALL kullanici_ozeti(42);

Basit ama işlevsel. Şimdi daha gerçekçi bir senaryoya geçelim.

Gerçek Dünya Senaryosu: Stok Yönetimi

Bir e-ticaret sisteminde sipariş oluşturulduğunda stoktan düşülmesi gerekir. Bu işlemi birden fazla adımdan oluşan bir transaction içinde yapmak en doğrusu. Hem siparişi kaydet, hem stoku güncelle, hem de log tut.

DELIMITER //

CREATE PROCEDURE siparis_olustur(
    IN p_musteri_id INT,
    IN p_urun_id INT,
    IN p_miktar INT,
    OUT p_siparis_id INT,
    OUT p_hata_mesaji VARCHAR(255)
)
BEGIN
    DECLARE v_stok INT DEFAULT 0;
    DECLARE v_fiyat DECIMAL(10,2) DEFAULT 0.00;
    DECLARE v_toplam DECIMAL(10,2) DEFAULT 0.00;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_siparis_id = -1;
        SET p_hata_mesaji = 'İşlem sırasında hata oluştu, geri alındı.';
    END;
    
    -- Stok kontrolü
    SELECT stok_miktari, satis_fiyati
    INTO v_stok, v_fiyat
    FROM urunler
    WHERE id = p_urun_id
    FOR UPDATE;
    
    IF v_stok < p_miktar THEN
        SET p_siparis_id = -1;
        SET p_hata_mesaji = 'Yeterli stok yok.';
    ELSE
        START TRANSACTION;
        
        SET v_toplam = v_fiyat * p_miktar;
        
        -- Sipariş kaydı oluştur
        INSERT INTO siparisler (musteri_id, urun_id, miktar, tutar, tarih)
        VALUES (p_musteri_id, p_urun_id, p_miktar, v_toplam, NOW());
        
        SET p_siparis_id = LAST_INSERT_ID();
        
        -- Stok güncelle
        UPDATE urunler
        SET stok_miktari = stok_miktari - p_miktar
        WHERE id = p_urun_id;
        
        -- Hareket logu
        INSERT INTO stok_hareketleri (urun_id, islem_tipi, miktar, referans_id, tarih)
        VALUES (p_urun_id, 'SATIS', p_miktar, p_siparis_id, NOW());
        
        COMMIT;
        SET p_hata_mesaji = '';
    END IF;
END //

DELIMITER ;

Bunu uygulamadan şöyle çağırırsın:

mysql> CALL siparis_olustur(5, 101, 3, @siparis_id, @hata);
mysql> SELECT @siparis_id, @hata;

OUT parametreleri sayesinde işlemin sonucunu uygulama katmanına temiz bir şekilde iletebiliyorsun.

Döngüler ve Koşullar: Prosedürlerde Akış Kontrolü

Stored procedure’lerin güçlü olduğu noktalardan biri de programatik yapıları desteklemesi. IF/ELSE, WHILE, REPEAT ve LOOP yapılarını kullanabilirsin.

DELIMITER //

CREATE PROCEDURE aylik_rapor_olustur(IN p_yil INT, IN p_ay INT)
BEGIN
    DECLARE v_baslangic DATE;
    DECLARE v_bitis DATE;
    DECLARE v_gun INT DEFAULT 1;
    DECLARE v_toplam_gun INT;
    
    SET v_baslangic = DATE(CONCAT(p_yil, '-', LPAD(p_ay, 2, '0'), '-01'));
    SET v_bitis = LAST_DAY(v_baslangic);
    SET v_toplam_gun = DAY(v_bitis);
    
    -- Önceki rapor varsa sil
    DELETE FROM gunluk_raporlar
    WHERE rapor_yil = p_yil AND rapor_ay = p_ay;
    
    -- Her gün için rapor satırı oluştur
    WHILE v_gun <= v_toplam_gun DO
        INSERT INTO gunluk_raporlar (rapor_yil, rapor_ay, gun, 
                                     siparis_sayisi, toplam_ciro)
        SELECT 
            p_yil,
            p_ay,
            v_gun,
            COUNT(*),
            COALESCE(SUM(tutar), 0)
        FROM siparisler
        WHERE DATE(tarih) = DATE_ADD(v_baslangic, INTERVAL (v_gun - 1) DAY);
        
        SET v_gun = v_gun + 1;
    END WHILE;
    
    SELECT CONCAT(p_yil, '/', p_ay, ' raporu oluşturuldu. ',
                  v_toplam_gun, ' gün işlendi.') AS sonuc;
END //

DELIMITER ;

Trigger’a Giriş: Otomatik Tepki Mekanizması

Trigger’lar, bir tabloda INSERT, UPDATE veya DELETE işlemi gerçekleştiğinde otomatik olarak çalışan kod bloklarıdır. BEFORE veya AFTER olarak tanımlanabilirler.

  • BEFORE INSERT: Kayıt eklenmeden önce çalışır, veriyi değiştirebilirsin
  • AFTER INSERT: Kayıt eklendikten sonra çalışır, başka tablolara işlem yapabilirsin
  • BEFORE UPDATE: Güncelleme öncesi, eski ve yeni değerlere erişebilirsin
  • AFTER UPDATE: Güncelleme sonrası log tutmak için idealdir
  • BEFORE DELETE: Silme öncesi kontrol yapabilirsin
  • AFTER DELETE: Silme sonrası temizlik işlemleri için kullanılır

Trigger içinde OLD ve NEW anahtar kelimelerini kullanırsın. OLD silinen veya güncellenen önceki değeri, NEW eklenen veya güncellenen yeni değeri temsil eder.

İlk Trigger: Audit Log Tutmak

En klasik trigger kullanım senaryosu audit logging’dir. Kritik tablolarda kim ne zaman ne değiştirdi bilgisini tutmak.

DELIMITER //

CREATE TRIGGER kullanici_guncelleme_log
AFTER UPDATE ON kullanicilar
FOR EACH ROW
BEGIN
    IF OLD.email != NEW.email THEN
        INSERT INTO audit_log (tablo_adi, kayit_id, alan_adi, 
                               eski_deger, yeni_deger, degisiklik_tarihi, 
                               kullanici_ip)
        VALUES ('kullanicilar', NEW.id, 'email',
                OLD.email, NEW.email, NOW(),
                @kullanici_ip);
    END IF;
    
    IF OLD.telefon != NEW.telefon OR 
       (OLD.telefon IS NULL AND NEW.telefon IS NOT NULL) THEN
        INSERT INTO audit_log (tablo_adi, kayit_id, alan_adi,
                               eski_deger, yeni_deger, degisiklik_tarihi,
                               kullanici_ip)
        VALUES ('kullanicilar', NEW.id, 'telefon',
                OLD.telefon, NEW.telefon, NOW(),
                @kullanici_ip);
    END IF;
END //

DELIMITER ;

Session değişkeni olan @kullanici_ip‘yi uygulama katmanından bağlantı başında set edebilirsin:

mysql> SET @kullanici_ip = '192.168.1.100';

Gerçek Dünya Senaryosu: Stok Uyarı Sistemi

Stok seviyesi belirli bir eşiğin altına düştüğünde otomatik uyarı kaydı oluşturalım.

DELIMITER //

CREATE TRIGGER stok_uyari_kontrolu
AFTER UPDATE ON urunler
FOR EACH ROW
BEGIN
    DECLARE v_esik INT DEFAULT 10;
    
    -- Stok azaldı mı kontrol et
    IF NEW.stok_miktari < OLD.stok_miktari THEN
        
        -- Kritik stok seviyesine düştü mü?
        IF NEW.stok_miktari <= v_esik AND OLD.stok_miktari > v_esik THEN
            INSERT INTO stok_uyarilari (
                urun_id, 
                urun_adi,
                mevcut_stok,
                esik_deger,
                uyari_tipi,
                olusturulma_tarihi,
                okundu
            )
            SELECT 
                NEW.id,
                NEW.urun_adi,
                NEW.stok_miktari,
                v_esik,
                CASE 
                    WHEN NEW.stok_miktari = 0 THEN 'KRITIK'
                    WHEN NEW.stok_miktari <= 5 THEN 'DUSUK'
                    ELSE 'UYARI'
                END,
                NOW(),
                0;
        END IF;
        
        -- Tamamen bitti
        IF NEW.stok_miktari = 0 AND OLD.stok_miktari > 0 THEN
            UPDATE urunler
            SET aktif = 0
            WHERE id = NEW.id;
        END IF;
        
    END IF;
END //

DELIMITER ;

Bu trigger sayesinde stok yönetim panelinde “okunmamış uyarılar” özelliği kolayca hayata geçirilebilir.

Prosedür Yönetimi ve Bakımı

Sistemde hangi prosedürlerin olduğunu görmek:

-- Tüm stored procedure'leri listele
SHOW PROCEDURE STATUS WHERE Db = 'veritabanim';

-- Belirli bir prosedürün kodunu gör
SHOW CREATE PROCEDURE siparis_olustur;

-- Information schema'dan sorgula
SELECT ROUTINE_NAME, ROUTINE_TYPE, CREATED, LAST_ALTERED
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'veritabanim'
AND ROUTINE_TYPE = 'PROCEDURE'
ORDER BY LAST_ALTERED DESC;

Trigger’ları listelemek için:

-- Tüm trigger'ları listele
SHOW TRIGGERS FROM veritabanim;

-- Belirli tablonun trigger'larını gör
SHOW TRIGGERS FROM veritabanim LIKE 'kullanicilar';

-- Information schema'dan detaylı bilgi
SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE,
       ACTION_TIMING, CREATED
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'veritabanim'
ORDER BY EVENT_OBJECT_TABLE, ACTION_TIMING;

Performans Konuları

Stored procedure ve trigger kullanırken dikkat etmen gereken bazı performans tuzakları var.

Trigger’lar her satır için çalışır. FOR EACH ROW bu demek. Eğer 100.000 satır UPDATE ediyorsan, trigger 100.000 kez tetiklenir. Bu ciddi performans sorunlarına yol açabilir.

-- Trigger'ın tetiklenme sayısını izlemek için
SHOW STATUS LIKE 'Handler_update';

-- Slow query log'u aktifleştir
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

Prosedürlerde cursor kullanımından mümkün olduğunca kaçın. Set bazlı operasyonlar her zaman daha hızlıdır. Cursor kullanmak zorundaysan şöyle tanımlarsın:

DELIMITER //

CREATE PROCEDURE toplu_indirim_uygula(IN p_kategori_id INT, IN p_oran DECIMAL(5,2))
BEGIN
    DECLARE v_bitti INT DEFAULT 0;
    DECLARE v_urun_id INT;
    DECLARE v_fiyat DECIMAL(10,2);
    
    DECLARE urun_cursor CURSOR FOR
        SELECT id, satis_fiyati FROM urunler
        WHERE kategori_id = p_kategori_id AND aktif = 1;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_bitti = 1;
    
    OPEN urun_cursor;
    
    urun_dongusu: LOOP
        FETCH urun_cursor INTO v_urun_id, v_fiyat;
        
        IF v_bitti = 1 THEN
            LEAVE urun_dongusu;
        END IF;
        
        UPDATE urunler
        SET satis_fiyati = v_fiyat * (1 - p_oran / 100),
            guncelleme_tarihi = NOW()
        WHERE id = v_urun_id;
        
    END LOOP urun_dongusu;
    
    CLOSE urun_cursor;
    
    -- Aslında bunu tek sorguda yapmak çok daha iyi
    -- UPDATE urunler SET satis_fiyati = satis_fiyati * (1 - p_oran/100)
    -- WHERE kategori_id = p_kategori_id AND aktif = 1;
END //

DELIMITER ;

Yukarıdaki örnekte cursor kullanımını gösterdim ama yorum satırında da belirttiğim gibi bu işi tek bir UPDATE ile yapmak çok daha verimli. Cursor’un mantıklı olduğu durumlar gerçekten çok sınırlı.

Güvenlik ve Yetkilendirme

Stored procedure’ler güvenlik açısından çok değerlidir. Kullanıcılara tabloya direkt erişim yerine sadece prosedür çalıştırma yetkisi verebilirsin.

-- Kullanıcıya sadece belirli prosedürleri çalıştırma yetkisi ver
GRANT EXECUTE ON PROCEDURE veritabanim.siparis_olustur TO 'uygulama_kullanici'@'192.168.1.%';
GRANT EXECUTE ON PROCEDURE veritabanim.kullanici_ozeti TO 'uygulama_kullanici'@'192.168.1.%';

-- Tabloya direkt erişim verme
-- REVOKE ALL ON veritabanim.* FROM 'uygulama_kullanici'@'192.168.1.%';

-- Yetkileri kontrol et
SHOW GRANTS FOR 'uygulama_kullanici'@'192.168.1.%';

DEFINER ve INVOKER kavramlarını da bilmek gerekir:

  • DEFINER: Prosedür, onu oluşturan kullanıcının yetkileriyle çalışır (varsayılan)
  • INVOKER: Prosedür, onu çağıran kullanıcının yetkileriyle çalışır
-- DEFINER belirterek prosedür oluştur
CREATE DEFINER='admin'@'localhost' PROCEDURE guvenli_rapor()
SQL SECURITY DEFINER
BEGIN
    -- Bu prosedürü kim çağırırsa çağırsın, admin yetkisiyle çalışır
    SELECT * FROM gizli_tablo;
END;

Trigger ve Prosedür Silme ve Güncelleme

MySQL’de stored procedure ve trigger’ları güncellemek için önce silip sonra yeniden oluşturmak gerekir. ALTER PROCEDURE sadece bazı özellikleri değiştirir, kodu değiştiremezsiniz.

-- Prosedür sil
DROP PROCEDURE IF EXISTS siparis_olustur;

-- Trigger sil  
DROP TRIGGER IF EXISTS stok_uyari_kontrolu;

-- Toplu temizlik için (dikkatli kullan!)
-- Veritabanındaki tüm trigger'ları bul ve DROP script'i oluştur
SELECT CONCAT('DROP TRIGGER IF EXISTS ', TRIGGER_NAME, ';')
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'veritabanim';

Production ortamında prosedür güncellemesi yaparken şöyle bir yaklaşım izle:

-- Önce yedek al
mysqldump --routines --no-data -u root -p veritabanim > routines_backup.sql

-- Sonra güncelle
mysql -u root -p veritabanim < yeni_prosedur.sql

-- Doğrula
mysql -u root -p -e "SHOW CREATE PROCEDURE veritabanim.siparis_olusturG"

Sık Yapılan Hatalar

Yıllar içinde gördüğüm en yaygın hatalar şunlar:

  • Trigger içinde aynı tabloya yazma: Trigger tetiklendiği tablo üzerinde işlem yaparsan sonsuz döngüye girersin. MySQL bunu SQLEXCEPTION ile durdurur ama yine de tehlikeli.
  • Transaction kontrolünü unutmak: Prosedür içinde hata olduğunda ROLLBACK yapmayı unutmak veri tutarsızlığına yol açar. EXIT HANDLER kullanımı şart.
  • NULL kontrolü yapmamak: IF OLD.alan != NEW.alan ifadesi NULL değerlerde doğru çalışmaz. COALESCE veya IS NULL kullan.
  • Test ortamında çalışıp production’da çalışmamak: MySQL versiyonları arasında sözdizimi farklılıkları olabilir. SELECT VERSION(); ile kontrol et.
  • Çok fazla iş mantığı koymak: Prosedürleri her şeyi yapan dev bloklara dönüştürmek. Tek sorumluluk ilkesi veritabanında da geçerli.

Sonuç

Stored procedure ve trigger’lar, doğru kullanıldığında veritabanı yönetimini çok daha güçlü ve güvenli hale getirir. Özellikle çok katmanlı uygulamalarda ya da birden fazla servisin aynı veritabanını kullandığı mimarilerde iş mantığını veritabanı katmanında tutmak büyük avantaj sağlar.

Benim kişisel önerim şu: Trigger’ları audit log ve otomatik hesaplama gibi basit, net amaçlar için kullan. Karmaşık iş mantığını trigger içine gömmek debug cehennemine dönüşür. Stored procedure’leri ise transaction gerektiren çok adımlı işlemler ve güvenlik katmanı olarak düşün.

Şunu da unutma: Bu araçların en büyük düşmanı dokümantasyon eksikliği. Her prosedürü ve trigger’ı oluşturduğunda amacını, parametrelerini ve bağımlılıklarını bir yere not et. Altı ay sonra o kodu okuyacak kişi muhtemelen sen olacaksın ve o an kendine çok teşekkür edeceksin.

Yorum yapın