Triggers (Tetikleyiciler)

Trigger Nedir?

Trigger, belirli olaylar (INSERT, UPDATE, DELETE) gerçekleştiğinde otomatik çalışan fonksiyonlardır.

Trigger Türleri

Updated_at Trigger

-- Trigger fonksiyonu
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger tanımı
CREATE TRIGGER set_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_timestamp();

-- Birden fazla tabloya uygula
CREATE TRIGGER set_updated_at BEFORE UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER set_updated_at BEFORE UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION update_timestamp();

Audit Log Trigger

-- Audit tablosu
CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,
    table_name TEXT NOT NULL,
    action TEXT NOT NULL,
    old_data JSONB,
    new_data JSONB,
    changed_by TEXT DEFAULT current_user,
    changed_at TIMESTAMP DEFAULT NOW()
);

-- Trigger fonksiyonu
CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO audit_log (table_name, action, new_data)
        VALUES (TG_TABLE_NAME, 'INSERT', to_jsonb(NEW));
        RETURN NEW;
        
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log (table_name, action, old_data, new_data)
        VALUES (TG_TABLE_NAME, 'UPDATE', to_jsonb(OLD), to_jsonb(NEW));
        RETURN NEW;
        
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log (table_name, action, old_data)
        VALUES (TG_TABLE_NAME, 'DELETE', to_jsonb(OLD));
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Trigger uygula
CREATE TRIGGER users_audit
    AFTER INSERT OR UPDATE OR DELETE ON users
    FOR EACH ROW
    EXECUTE FUNCTION audit_trigger();

Validasyon Trigger

CREATE OR REPLACE FUNCTION validate_order()
RETURNS TRIGGER AS $$
BEGIN
    -- Tutar kontrolü
    IF NEW.total_amount <= 0 THEN
        RAISE EXCEPTION 'Order amount must be greater than 0';
    END IF;
    
    -- Miktar kontrolü
    IF NEW.quantity > 100 THEN
        RAISE EXCEPTION 'Maximum 100 items per order';
    END IF;
    
    -- Status kontrolü
    IF TG_OP = 'UPDATE' AND OLD.status = 'completed' THEN
        RAISE EXCEPTION 'Cannot modify completed orders';
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_order
    BEFORE INSERT OR UPDATE ON orders
    FOR EACH ROW
    EXECUTE FUNCTION validate_order();

Stok Güncelleme Trigger

CREATE OR REPLACE FUNCTION update_stock()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE products 
        SET stock = stock - NEW.quantity 
        WHERE id = NEW.product_id;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE products 
        SET stock = stock + OLD.quantity 
        WHERE id = OLD.product_id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_product_stock
    AFTER INSERT OR DELETE ON order_items
    FOR EACH ROW
    EXECUTE FUNCTION update_stock();

Trigger Yönetimi

-- Trigger'ları listele
SELECT trigger_name, event_manipulation, action_timing
FROM information_schema.triggers
WHERE trigger_schema = 'public';

-- Trigger devre dışı bırak
ALTER TABLE users DISABLE TRIGGER users_audit;

-- Trigger etkinleştir
ALTER TABLE users ENABLE TRIGGER users_audit;

-- Tüm trigger'ları devre dışı bırak
ALTER TABLE users DISABLE TRIGGER ALL;

-- Trigger sil
DROP TRIGGER IF EXISTS users_audit ON users;