Stored Procedures, Trigger und Views: Noch sinnvoll oder nur Altlast?

Datenbanklogik: Fluch oder Segen?

Kaum ein Thema spaltet Datenbankentwickler so zuverlässig wie die Frage nach Stored Procedures in MySQL. Die eine Seite schwört auf sie als effizienteste Form der Datenbanklogik — wiederverwendbar, netzwerksparend und atomar. Die andere Seite meidet sie wie die Pest, weil sie schlecht testbar, schwer zu debuggen und noch schwerer zu versionieren sind.

Die Wahrheit liegt in der Mitte und hängt stark vom Anwendungsfall ab. Dieser Artikel betrachtet alle drei Konzepte — Stored Procedures, Trigger und Views — nüchtern und praxisorientiert. Besonders interessant: Magento 2 nutzt Trigger extensiv für seinen EAV-Changelog-Mechanismus, was zeigt, dass diese "alten" Werkzeuge in professionellem Code durchaus ihren Platz haben können.

1. Stored Procedures in MySQL

Eine Stored Procedure in MySQL ist ein benanntes, gespeichertes SQL-Programm, das Parameter entgegennehmen, Variablen deklarieren, Kontrollstrukturen (IF, LOOP, WHILE, CASE) verwenden und Cursor über Resultsets öffnen kann. Der zentrale Kniff bei der Definition ist das DELIMITER-Befehl, der das Standard-Trennzeichen temporär ersetzt.

-- Change delimiter to allow semicolons inside the procedure body
DELIMITER $$

-- Stored Procedure: archive old orders to a separate table
CREATE PROCEDURE ArchiveOldOrders(
    IN  p_cutoff_date    DATE,
    IN  p_batch_size     INT,
    OUT p_archived_count INT
)
BEGIN
    -- Declare local variables
    DECLARE v_batch_count INT DEFAULT 0;
    DECLARE v_total       INT DEFAULT 0;
    DECLARE v_done        TINYINT DEFAULT 0;

    -- Declare a cursor for orders to archive
    DECLARE order_cursor CURSOR FOR
        SELECT entity_id FROM sales_order
        WHERE created_at < p_cutoff_date
            AND status IN ('complete', 'closed', 'canceled')
        LIMIT p_batch_size;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;

    START TRANSACTION;

    -- Open cursor and iterate
    OPEN order_cursor;
    archive_loop: LOOP
        FETCH order_cursor INTO v_batch_count;
        IF v_done THEN
            LEAVE archive_loop;
        END IF;
        SET v_total = v_total + 1;
    END LOOP archive_loop;
    CLOSE order_cursor;

    -- Insert into archive table
    INSERT INTO sales_order_archive
    SELECT * FROM sales_order
    WHERE created_at < p_cutoff_date
        AND status IN ('complete', 'closed', 'canceled')
    LIMIT p_batch_size;

    SET v_batch_count = ROW_COUNT();

    -- Remove archived rows from main table
    DELETE FROM sales_order
    WHERE created_at < p_cutoff_date
        AND status IN ('complete', 'closed', 'canceled')
    LIMIT p_batch_size;

    COMMIT;

    SET p_archived_count = v_batch_count;
END$$

-- Reset delimiter back to semicolon
DELIMITER ;

-- Call the procedure
CALL ArchiveOldOrders('2024-01-01', 1000, @archived);
SELECT @archived AS rows_archived;
    

2. Wann Stored Procedures noch sinnvoll sind

Die wichtigste Frage vor dem Schreiben einer Stored Procedure in MySQL: Gibt es einen triftigen Grund, diese Logik in der Datenbank statt im Anwendungscode zu implementieren? In den meisten Webanwendungen lautet die Antwort nein. Es gibt jedoch Fälle, in denen Stored Procedures klar überlegen sind:

• Batch-Wartungsjobs auf dem Server: Datenbankarchivierung, Cleanup-Routinen oder Aggregationsoperationen auf großen Datensätzen, die direkt auf dem Datenbankserver laufen und keine Netzwerk-Round-Trips verursachen sollen. Besonders effektiv, wenn Millionen von Zeilen verarbeitet werden.
• Atomare datenbanknahe Business-Logik: Operationen, die strikt atomar sein müssen und auf mehrere Tabellen schreiben — bei denen ein Application-Layer-Fehler in der Mitte der Operation zu einem inkonsistenten Zustand führen würde.
• Einheitliche Daten-API für mehrere Anwendungen: Wenn mehrere unabhängige Systeme dieselbe Datenbankinstanz nutzen, kann eine Stored Procedure eine einheitliche Schnittstelle bieten, hinter der sich Schemaänderungen verbergen lassen.

3. Nachteile und Fallstricke von Stored Procedures

Die Nachteile von Stored Procedures in MySQL sind erheblich und erklären, warum moderne Anwendungsarchitekturen sie oft vermeiden:

• Kein Unit Testing ohne Datenbankverbindung: Stored Procedures können nicht isoliert getestet werden. Jeder Test braucht eine vollständige Datenbankverbindung mit Testdaten — das macht CI/CD-Pipelines komplizierter.
• Schlechte Versionierbarkeit: Procedures leben in der Datenbank, nicht im Dateisystem. Ein git blame auf eine Procedure ist nicht möglich. Migrationsscripte sind nötig, aber fehleranfällig.
• Debugging-Alptraum: MySQL bietet kein eingebautes Debugger-Interface für Procedures. Fehlersuche erfolgt meist über eingebaute SELECT-Statements oder Log-Tabellen.
• Gecachter Execution Plan pro Session: MySQL cached den Ausführungsplan einer Stored Procedure pro Session. Bei stark wachsenden Tabellen kann ein beim ersten Aufruf optimierter Plan bei späteren Aufrufen suboptimal sein — ohne dass es offensichtlich ist.

-- Simple stored procedure with IN/OUT/INOUT parameters
DELIMITER $$

CREATE PROCEDURE UpdateProductPrice(
    IN    p_product_id   INT,
    IN    p_new_price    DECIMAL(12,4),
    INOUT p_adjustment   DECIMAL(12,4),  -- pass in old price, get back difference
    OUT   p_success      TINYINT
)
BEGIN
    DECLARE v_old_price DECIMAL(12,4);

    -- Get current price
    SELECT price INTO v_old_price
    FROM catalog_product_entity_decimal
    WHERE entity_id = p_product_id AND attribute_id = 75  -- price attribute_id
    LIMIT 1;

    IF v_old_price IS NULL THEN
        SET p_success = 0;
        LEAVE sp_label;  -- named label for early exit
    END IF;

    -- Calculate the adjustment (INOUT: old price IN, difference OUT)
    SET p_adjustment = p_new_price - v_old_price;

    -- Update the price
    UPDATE catalog_product_entity_decimal
    SET value = p_new_price
    WHERE entity_id = p_product_id AND attribute_id = 75;

    SET p_success = 1;
END$$

DELIMITER ;
    

4. Trigger in MySQL: BEFORE und AFTER

Ein MySQL-Trigger ist eine gespeicherte Prozedur, die automatisch bei DML-Operationen (INSERT, UPDATE, DELETE) auf einer Tabelle ausgeführt wird. MySQL unterstützt BEFORE und AFTER als Timing-Varianten sowie alle drei DML-Operationen — insgesamt also sechs Kombinationen pro Tabelle (in neueren MySQL-Versionen sind auch mehrere Trigger desselben Typs möglich).

-- BEFORE INSERT trigger: validate and normalize data
DELIMITER $$

CREATE TRIGGER trg_product_before_insert
BEFORE INSERT ON catalog_product_entity
FOR EACH ROW
BEGIN
    -- Normalize SKU to uppercase
    SET NEW.sku = UPPER(TRIM(NEW.sku));

    -- Set default created_at if not provided
    IF NEW.created_at IS NULL THEN
        SET NEW.created_at = NOW();
    END IF;
END$$

-- AFTER UPDATE trigger: write audit log
CREATE TRIGGER trg_product_after_update
AFTER UPDATE ON catalog_product_entity
FOR EACH ROW
BEGIN
    -- Only log when relevant fields change
    IF OLD.sku != NEW.sku OR OLD.updated_at != NEW.updated_at THEN
        INSERT INTO product_change_log (
            entity_id, old_sku, new_sku, changed_at
        ) VALUES (
            NEW.entity_id, OLD.sku, NEW.sku, NOW()
        );
    END IF;
END$$

DELIMITER ;

-- List all triggers on a table
SHOW TRIGGERS LIKE 'catalog_product_entity';

-- Show trigger definition
SHOW CREATE TRIGGER trg_product_before_insert\G
    

5. Magento 2 und EAV-Changelog-Trigger

Magento 2 setzt Trigger extensiv für seinen Indexer-Mechanismus ein. Das System nutzt einen "Mview" (Materialized View)-Ansatz: Wenn sich EAV-Attributwerte ändern, sollen Indexer nur die betroffenen Produkte neu indexieren — nicht den gesamten Katalog. Dafür erzeugt Magento automatisch Trigger auf den EAV-Tabellen.

-- Magento creates these triggers automatically via Mview mechanism
-- Example: trigger on catalog_product_entity_int for catalog_product indexer

-- Check Magento's auto-generated triggers
SHOW TRIGGERS WHERE `Table` LIKE 'catalog_product_entity%'\G

-- Typical Magento EAV changelog trigger (auto-generated by Mview)
-- This writes changed entity_ids to the changelog table
SHOW CREATE TRIGGER catalog_product_entity_int_before_insert\G

-- The changelog tables Magento uses:
SHOW TABLES LIKE '%_cl';
-- catalog_product_entity_int_cl
-- catalog_product_entity_varchar_cl
-- catalog_product_entity_decimal_cl
-- catalog_product_entity_datetime_cl
-- catalog_product_entity_text_cl

-- Inspect a changelog table
SELECT * FROM catalog_product_entity_int_cl ORDER BY version_id DESC LIMIT 10;

-- The Mview version tracking table
SELECT * FROM mview_state ORDER BY updated DESC;

-- How the indexer uses the changelog:
-- 1. Trigger fires on EAV table change
-- 2. Trigger inserts entity_id into _cl table with current version_id
-- 3. Indexer reads _cl table, processes only changed entity_ids
-- 4. Indexer updates mview_state.version_id to mark progress
    

Dieser Mechanismus zeigt, wann Trigger tatsächlich die beste Lösung sind: wenn eine reaktive, datenbanknahe Änderungsverfolgung benötigt wird, die unabhängig vom Anwendungscode zuverlässig funktionieren muss. Kein Application-Layer-Code kann so zuverlässig garantieren, dass jede Änderung in der Changelog-Tabelle landet.

6. Trigger und Statement-basierte Replikation

Trigger und Replikation zusammen sind ein bekanntes Gefahrengebiet in MySQL. Das Problem tritt auf, wenn binlog_format=STATEMENT verwendet wird — der ältere Modus, bei dem SQL-Statements statt Row-Changes in den Binary Log geschrieben werden.

-- Check current binlog format
SHOW VARIABLES LIKE 'binlog_format';

-- Dangerous combination: statement-based replication + non-deterministic triggers
-- With binlog_format=STATEMENT, the trigger executes on BOTH primary and replica
-- If the trigger uses NOW(), RAND(), UUID() → different results on replica!

-- Safe solution: use ROW-based binlog format (recommended for Magento)
SET GLOBAL binlog_format = 'ROW';
-- Or in my.cnf:
-- binlog_format = ROW
-- binlog_row_image = MINIMAL

-- With ROW format: only the resulting row changes are replicated
-- Triggers DO NOT fire again on the replica (they already fired on primary)
-- This is safe for Magento's EAV triggers

-- Verify Magento's binlog recommendation
SHOW VARIABLES LIKE 'binlog_format';
SHOW VARIABLES LIKE 'binlog_row_image';
    

Die Lösung: binlog_format=ROW ist für MySQL-Produktionsumgebungen mit Triggern immer zu empfehlen. Mit Row-Based Replication werden nur die tatsächlich geänderten Zeilen repliziert — Trigger feuern nicht erneut auf dem Replica und erzeugen daher keine Inkonsistenzen. Magento setzt ROW-Format ebenfalls voraus.

7. Views als Dokumentations- und Berechtigungsschicht

MySQL Views haben in modernen Anwendungen einen klareren Platz als Stored Procedures: Sie eignen sich hervorragend als Abstraktionsschicht für Reporting-Nutzer, als Berechtigungsfilter und als selbst-dokumentierende Abkürzung für komplexe Joins.

-- View for reporting: monthly order summary (non-updatable view)
CREATE OR REPLACE VIEW v_monthly_order_summary AS
SELECT
    DATE_FORMAT(created_at, '%Y-%m')  AS order_month,
    store_id,
    status,
    COUNT(*)                          AS order_count,
    ROUND(SUM(grand_total), 2)        AS total_revenue,
    ROUND(AVG(grand_total), 2)        AS avg_order_value,
    COUNT(DISTINCT customer_id)       AS unique_customers
FROM sales_order
GROUP BY
    DATE_FORMAT(created_at, '%Y-%m'),
    store_id,
    status;

-- Permission view: expose only non-sensitive order data to reporting role
CREATE OR REPLACE VIEW v_orders_public AS
SELECT
    increment_id,
    status,
    DATE(created_at)            AS order_date,
    grand_total,
    store_id
FROM sales_order;

-- Grant SELECT on view, but not on underlying table
GRANT SELECT ON myshop.v_orders_public TO 'reporting_user'@'%';

-- View as documentation for a complex join
CREATE OR REPLACE VIEW v_product_stock AS
SELECT
    p.entity_id,
    p.sku,
    s.qty,
    s.is_in_stock,
    s.min_qty,
    s.backorders
FROM catalog_product_entity p
JOIN cataloginventory_stock_item s ON s.product_id = p.entity_id
    AND s.stock_id = 1;

-- Use the view like a simple table
SELECT sku, qty FROM v_product_stock WHERE qty < 5;
    

8. Updatable vs. Non-Updatable Views

Nicht alle Views erlauben Schreiboperationen. MySQL unterscheidet zwischen updatable Views (INSERT, UPDATE, DELETE möglich) und non-updatable Views (nur SELECT).

-- UPDATABLE VIEW: maps to a single table, no aggregations
CREATE VIEW v_active_products AS
SELECT entity_id, sku, status
FROM catalog_product_entity
WHERE status = 1;

-- This UPDATE works through the view:
UPDATE v_active_products SET sku = 'NEW-SKU' WHERE entity_id = 123;

-- NON-UPDATABLE VIEW: uses GROUP BY, aggregates, JOINS
-- The v_monthly_order_summary from above is non-updatable.
-- The following would fail with an error:
-- UPDATE v_monthly_order_summary SET order_count = 0;  -- ERROR

-- Check if a view is updatable
SELECT
    table_name,
    is_updatable,
    is_insertable_into
FROM information_schema.VIEWS
WHERE table_schema = DATABASE();

-- WITH CHECK OPTION: prevent inserting rows that would fall outside the view's WHERE
CREATE VIEW v_high_value_orders AS
SELECT * FROM sales_order
WHERE grand_total > 500
WITH CHECK OPTION;

-- This INSERT would fail because grand_total = 50 doesn't match WHERE grand_total > 500:
-- INSERT INTO v_high_value_orders (grand_total, ...) VALUES (50, ...);
    

9. SHOW CREATE für Diagnose

Das SHOW CREATE-Statement gibt den vollständigen Definitionscode von Procedures, Triggern und Views zurück — unverzichtbar für Diagnose und Dokumentation, besonders wenn fremder Datenbankcode verstanden werden muss.

-- Show stored procedure definition
SHOW CREATE PROCEDURE ArchiveOldOrders\G

-- Show all stored procedures in current database
SELECT
    routine_name,
    routine_type,
    created,
    last_altered,
    security_type,
    LEFT(routine_definition, 100) AS definition_snippet
FROM information_schema.ROUTINES
WHERE routine_schema = DATABASE()
ORDER BY routine_type, routine_name;

-- Show trigger definition
SHOW CREATE TRIGGER trg_product_after_update\G

-- List all triggers with their timing and event
SELECT
    trigger_name,
    event_manipulation,
    event_object_table,
    action_timing,
    created
FROM information_schema.TRIGGERS
WHERE trigger_schema = DATABASE()
ORDER BY event_object_table, action_timing, event_manipulation;

-- Show view definition
SHOW CREATE VIEW v_monthly_order_summary\G

-- List all views in database
SELECT
    table_name         AS view_name,
    is_updatable,
    LEFT(view_definition, 150) AS definition
FROM information_schema.VIEWS
WHERE table_schema = DATABASE();
    

10. Zusammenfassung

Stored Procedures in MySQL haben ihren Platz — aber einen kleineren, als viele Legacy-Codebasen vermuten lassen. Sie eignen sich für Batch-Wartungsjobs und atomare Datenbankoperationen, versagen aber bei Testbarkeit und Versionierbarkeit. Trigger sind das wirkungsvollste Werkzeug für reaktive Änderungsverfolgung, wie Magentos EAV-Changelog-Mechanismus beweist — aber gefährlich mit statement-basierter Replikation. Views glänzen als selbst-dokumentierende Reporting-Schicht und für Berechtigungsmanagement.

11. FAQ: Stored Procedures, Trigger und Views in MySQL