SQL MySQL dynamischen Trigger

[-Rayz-]

Guten Tag :-)

ich schaue mir gerade ein paar Beispiele zum Aufbau eines Triggers an und diese haben irgendwie alle eine feste Zuweisung der Spaltennamen (Old.spaltenname). Insgesamt habe ich aber ca. 50 Tabellen mit durchschnittlich 60 Spalten und ich wollte eigentlich nicht jede Spalte erneut im Trigger fest definieren.
Naiv wie ich bin hatte ich mir im Kopf das so gedacht:

CREATE TABLE triggerTable_audit (
    id INT AUTO_INCREMENT PRIMARY KEY,
    userId CHAR(36) NOT NULL,
    tableName VARCHAR(60) DEFAULT Null,
    columnName VARCHAR(60) DEFAULT Null,
    oldValue longtext NOT NULL,
    newValue longtext NOT NULL,
    changedate DATETIME DEFAULT NULL,
    action VARCHAR(50) DEFAULT NULL
);

Ich möchte den User, den Tabellen und Spaltennamen in dem der Wert geändert wurde sowie den vorherigen und den neuen Wert speichern. Unter action steht dann halt ob es ein ein Update oder Insert war. Delete gibt es eh nicht :-)

CREATE TRIGGER before_table_update
    BEFORE UPDATE ON ganzVieleTabellen
    FOR EACH ROW
 INSERT INTO triggerTable_audit
 SET action = 'update',
      userId = OLD.userId,
     tableName = 'ganzVieleTabellen',
     columnName = .....
     oldValue  = OLD....,
     newValue  = OLD.....,
     changedat = NOW();

Muss ich wirklich für JEDE Spalte eine Abfrage ala "IF NEW.blub<> OLD.blubTHEN " schreiben um oldValue, newValue und columnName zu füllen oder gibt es auch irgendeine dynamische Möglichkeit?
Hoffe mein Anliegen ist verständlich genug...

Vielen Dank :-)

 

[mental.dIseASe]

Wir haben es für unsere Anwendung im Banking-Umfeld genau so gemacht. Aber den Code der Trigger kannst du dir ja eigentlich wunderbar generieren lassen. Du musst die Trigger ja nicht alle von Hand schreiben.

 

[-Rayz-]

Also mit MySQL Workbench bzw. PhpMyAdmin kann ich mir doch nur so Kopfdaten generieren lassen sprich Name, Tabelle Zeit und Ereignis (Insert update delete).

Die "nervigste" Tabelle hat 92 Spalten. Dann müsste ich demnach ja 92 mal eine Zuordnung von Old Value und New Value machen + Überprüfung ob die Werte unterschiedlich sein.
Das kann ich mir im Jahr 2019 irgendwie schwer vorstellen dass das nicht automatisiert geht...

 

[mental.dIseASe]

Mit MySQL kenne ich mich nicht aus, aber es wird doch bestimmt irgendwelche Views geben, mit denen du Meta-Daten zu deinem Schema betracht kannst, wie bei Oracle ALL_TAB_COLUMNS.

Bist du ferner sicher, dass du für alle Spalten in allen Tabellen ein Änderungsprotokoll brauchst? Ich würde erwarten, dass ein guter Produktmanager hier die Tabellen und Spalten definiert, die auditiert werden sollen.

 

[-Rayz-]

Da jede Spalte einen relevanten Wert für weitere Prüfungen abbildet, muss ich leider auch jede Spalte mit in den Trigger fürs Audit nehmen..

 

[Oelepoeto]

Denke auch, dass du über irgendwelche Systemtabellen oder Views die Spalten auslesen kannst und somit zumindest das Generieren der Trigger erleichtern könntest. Kenn mich leider mit MySQL auch nicht aus, gibt es aber bestimmt!

 

[-Rayz-]

Ich versuche es nun via php (Laravel) zu lösen:

  $tables = DB::select('SHOW TABLES');
        foreach ($tables as $table) {
            foreach ($table as $key => $tableName) {
                $idName = $this->getColumn()[$tableName];
                $triggerSql = '';
                $triggerSql .= 'CREATE TRIGGER before_' . $tableName . '_update BEFORE UPDATE ON ' . $tableName . ' FOR EACH ROW BEGIN';

                $triggerSql .= 'SET @userId := (SELECT user_id FROM andereTabelle WHERE ' . $idName . ' = OLD.id)';
                $columns = Schema::getColumnListing($tableName);
                foreach ($columns as $columnName) {
                    $triggerSql .= 'IF (OLD.' . $columnName . ' <> NEW.' . $columnName . ') THEN INSERT INTO _audit(userId, tableName, columnName, oldValue, newValue, changedate, action)';
                    $triggerSql .= 'VALUES (@userId,' . $tableName . ',' . $columnName . ',OLD.' . $columnName . ',NEW.' . $columnName . ')';
                    $triggerSql .= 'END IF; END;';
                }
                return response()->json([
                    'data' => $triggerSql
                ], 200);

            }
        }

getColumn ist nur ein Mapping um anhand des Tabellennamens den Spaltennamen der ID zu bekommen.
Hier mal ein Teil vom Ergebnis:

CREATE TRIGGER before_tabelle_update BEFORE UPDATE ON tabelle FOR EACH ROW BEGINSET @userId := (SELECT user_id FROM andereTabelle WHERE andereTabelle_id = OLD.id)IF (OLD.id <> NEW.id) THEN INSERT INTO _audit(userId, tableName, columnName, oldValue, newValue, changedate, action)VALUES (@userId,name,id,OLD.id,NEW.id)END IF; END;

Leider kommt da die Fehlermeldung:
"#1064 - Fehler in der SQL-Syntax. Bitte die korrekte Syntax im Handbuch nachschlagen bei '@userId := (SELECT user_id FROM andereTabelle WHERE andereTabelle_id = OLD.id)IF (OLD.' in Zeile 1 "

"andereTabelle " ist jetzt nur ein willkürlicher Name

Unter Values fehlen noch zwei Einträge..


PS: So geht es nun

     $triggerSql .= 'CREATE TRIGGER before_' . $tableName . '_update BEFORE UPDATE ON ' . $tableName . ' FOR EACH ROW BEGIN ';

                $triggerSql .= 'SET @userId = (SELECT user_id FROM blabla WHERE ' . $idName . ' = OLD.id); ';
                $columns = Schema::getColumnListing($tableName);
                foreach ($columns as $columnName) {
                    $triggerSql .= 'IF (OLD.' . $columnName . ' <> NEW.' . $columnName . ') THEN INSERT INTO _audit(userId, tableName, columnName, oldValue, newValue, changedate, action)';
                    $triggerSql .= 'VALUES (@userId,' . $tableName . ',' . $columnName . ',OLD.' . $columnName . ',NEW.' . $columnName . ',CURRENT_DATE,"update");';
                    $triggerSql .= 'END IF;';
                }
                $triggerSql .= 'END$$';

PPS: Hier noch mit INSERT - falls von Interesse

  $triggerSqlInsert = 'DELIMITER $$ ';
                $triggerSqlInsert .= 'CREATE TRIGGER before_' . $tableName . '_insert BEFORE INSERT ON ' . $tableName . ' FOR EACH ROW BEGIN ';

                $triggerSqlInsert .= 'SET @userId = (SELECT user_id FROM probes WHERE ' . $idName . ' = NEW.id); ';
                $columns = Schema::getColumnListing($tableName);
                $triggerSqlInsert .= 'IF (NEW.' . $columnName . ' <> NULL) THEN INSERT INTO _audit(userId, tableName, columnName, oldValue, newValue, changedate, action) VALUES ';
                $i = 1;
                foreach ($columns as $columnName) {
                    if ($i >= sizeof($columns)) {
                        $triggerSqlInsert .= '(@userId,' . $tableName . ',' . $columnName . ',NULL,NEW.' . $columnName . ',CURRENT_DATE,"insert");';
                    } else {
                        $triggerSqlInsert .= '(@userId,' . $tableName . ',' . $columnName . ',NULL,NEW.' . $columnName . ',CURRENT_DATE,"insert"),';
                    }
                    $i++;
                }
                $triggerSqlInsert .= 'END IF;';
                $triggerSqlInsert .= 'END$$';
                $triggerSql .= $triggerSqlInsert;

Habe fertig :-)

 

[mental.dIseASe]

Ich weiß nicht, wie das bei MySQL ist, aber bei Oracle müsstest du für die Update-Trigger bei optionalen Spalten (also nullable) noch den Fall abdecken, dass der alte Wert NULL ist und der neue nicht mehr und umgekehrt, denn (zumindest in Oracle) existiert kein x, sodass gilt: x != NULL OR x = NULL.

In dem Fall müsste noch sowas rein wie: OR (OLD.SPALTE IS NULL AND NEW.SPALTE IS NOT NULL) OR (OLD.SPALTE IS NOT NULL AND NEW.SPALTE IS NULL)

 

[-Rayz-]

Generell fehlen noch ein paar Abfragen da hast du recht. Beim anlegen eines Objektes wird der Trigger auch ausgelöst, wenn der Wert null ist.
Im Moment hab ich das Problem, dass ich bei einem Insert die user_id noch nicht habe obwohl ich zuerst die Tabelle speichere, wo diese drin steht aber meine Trigger werden genau verkehrtherum aufgerufen.