Wie man MySQL lehrt, in die Vergangenheit zu schauen

    Wie man MySQL lehrt, in die Vergangenheit zu schauen

    Der Artikel wird sich auf das Protokollieren von Änderungen in MySQL konzentrieren . Ich möchte die Implementierung der Protokollierung von Triggern zeigen und welche erstaunlichen Dinge Sie damit tun können.

    Warum bei Triggern? Weil es keinen Zugriff auf das Binärlog gibt. Die Implementierung mit dem binären Protokoll ist potenziell produktiver, wenn auch schwieriger zu entwickeln, weil erforderlich, um das Protokoll zu analysieren.

    Ich möchte Sie sofort warnen, dass diese Methode den Server zusätzlich belastet. Wenn Sie Daten aktiv ändern, ist diese Lösung möglicherweise nicht für Sie geeignet oder erfordert einige Anpassungen und Verbesserungen.

    Im Allgemeinen ist die Lösung vollständig und komplex. Es kann "wie besehen" implementiert werden und seine Aufgabe perfekt erfüllen.

    Alles unten ist in der Version implementiertMariaDB 10.0.32
    Spalten mit Typen werden protokolliert : Zahlen, Zeichenfolgen, Datumsangaben. Angemeldete Tabelle muss ein eindeutiges hat NOT NULL numerisches Feld ID .

    Erstellen Sie zunächst eine Tabelle mit der Protokollierungskonfiguration:

    DROP TABLE IF EXISTS protocol_config;
    CREATE TABLE protocol_config (
    id int(11) NOT NULL PRIMARY KEY auto_increment
    , command VARCHAR(50) NOT NULL -- Команда
    , table_name VARCHAR(50) -- Имя таблицы
    , column_name VARCHAR(50) -- Имя колонки
    , denormalize_column VARCHAR(50) -- Денормализованная колонка в таблице protocol
    , UNIQUE (command, table_name, column_name, denormalize_column)
    ) DEFAULT CHARSET=utf8 COMMENT='Настройка протоколирования';
    

    Alle Optionen werden bei der Generierung eines Triggers für die Protokollierung angewendet. Das heißt Wenn Sie Einstellungen ändern, müssen Sie Trigger neu generieren. Feldbefehl

    - Protokoll Konfigurationsoption:

    1. disable_protocol - Deaktiviert die Protokollierung.
    2. exclude_table - Gibt die Tabelle an, die von der Protokollierung ausgeschlossen werden soll. Standardmäßig sind alle BASE TABLE ENGINE = InnoDB an der Protokollierung beteiligt .
      Beispiel:
      exclude_table protocol
      exclude_table protocol_pos
    3. exclude_column - gibt das Feld an, das von der Protokollierung ausgeschlossen werden soll. Zum Beispiel ein denormalisiertes Feld, das von Triggern unterstützt wird.

      Zum Beispiel
      exclude_column docs sum
    4. denormalize_column - gibt die Spalte an, die im Protokoll zusätzlich denormalisiert werden muss ( Protokolltabelle ). Standardmäßig werden alle Felder in der Tabelle protocol_pos protokolliert .

      Zum Beispiel zeichnet
      denormalize_column docs id doc_id
      aus der docs- Tabelle das id- Feld in der Protokolltabelle in der doc_id- Spalte auf . Das Feld doc_id in der Protokolltabelle muss von Hand erstellt werden.
      denormalize_column doc_pos doc_id doc_id
      der Tabelle doc_pos Feld protokolliert doc_id Tabelle Protokollin der Spalte doc_id .

    Protokolltabelle:

    DROP TABLE IF EXISTS protocol_pos;
    DROP TABLE IF EXISTS protocol;
    CREATE TABLE protocol (
    id BIGINT NOT NULL PRIMARY KEY auto_increment
    , date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP -- Дата время операции
    , oper VARCHAR(1) NOT NULL -- операция I, U, D
    , table_name VARCHAR(50) NOT NULL -- Имя таблица
    , table_id BIGINT NOT NULL -- значение поля id в протоколируемой таблице
    , username VARCHAR(50) NOT NULL -- имя пользователя приложения или БД
    , ip varchar(45) -- IP адрес пользователя
    , user_agent varchar(256) -- Браузер
    , KEY (table_name, date)
    ) DEFAULT CHARSET=utf8 COMMENT='Протокол изменений';
    

    Protocol_pos-Tabelle:

    DROP TABLE IF EXISTS protocol_pos;
    CREATE TABLE protocol_pos (
    prot_id BIGINT NOT NULL -- ссылка на protocol.id
    , column_name VARCHAR(50) NOT NULL -- имя столбца из протоколируемой таблицы
    , old_val VARCHAR(2000) -- старое значение столбца
    , new_val VARCHAR(2000) -- новое значение столбца
    , PRIMARY KEY (prot_id, column_name)
    , FOREIGN KEY (prot_id) REFERENCES protocol(id)
    ) DEFAULT CHARSET=utf8 COMMENT='Поля протокола изменений';
    

    In der Protokolltabelle übergeben wir die Operation und in der Tabelle protocol_pos geben wir die geänderten Felder ein.

    Nehmen wir nun den Trigger-Generator aus meinem vorherigen Artikel „Implementieren von Business Logic in MySQL“ als Grundlage und schreiben einen Generator für die Protokollierung auf dieser Basis.

    Die Generierungsfunktion für Geschäftslogik-Trigger gen_bl_trigger überprüft das Vorhandensein der Prozedur <Tabellenname> _trg_proc

    gen_bl_trigger
    DELIMITER $
    DROP FUNCTION IF EXISTS gen_bl_trigger$
    CREATE FUNCTION gen_bl_trigger(table_name VARCHAR(200), trigger_time VARCHAR(200), trigger_type VARCHAR(200)) RETURNS TEXT BEGIN
        DECLARE text TEXT;
        DECLARE f_proc INT;
        SET group_concat_max_len = 9000000;
        SET f_proc := (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES
            WHERE ROUTINE_NAME = CONCAT(table_name, '_trg_proc')
             AND ROUTINE_TYPE = 'PROCEDURE'
             AND ROUTINE_SCHEMA = DATABASE()
        );
        IF IFNULL(f_proc, 0) = 0 THEN
            RETURN '';
        END IF;
        SET text := CONCAT('\nbl_proc: BEGIN
    IF @disable_', table_name, '_bl_trg = 1 OR @disable_all_bl_trg = 1 THEN
    LEAVE bl_proc;
    END IF;');
        IF trigger_time = 'BEFORE' THEN
            -- Создаём временную таблицу
            SET text := CONCAT(text, '\nCREATE TEMPORARY TABLE ');
            -- Временная таблица уже может быть создана конструкцией INSERT INTO ... ON DUPLICATE KEY UPDATE поэтому добавляем IF NOT EXISTS
            -- для INSERT IGNORE не сработает AFTER TRIGGER, поэтому тоже добавляем
            IF trigger_type IN ('INSERT', 'UPDATE') THEN
                SET text := CONCAT(text, 'IF NOT EXISTS ');
            END IF;
            SET text := CONCAT(text, table_name, '_tmp_trg (');
            SET text := CONCAT(text, '\ntime VARCHAR(1)');
            SET text := CONCAT(text, '\n, type VARCHAR(1)');
            SET text := CONCAT(text, '\n, col_changed VARCHAR(1000)');
            SET text := CONCAT(text, (SELECT GROUP_CONCAT('\n, new_', COLUMN_NAME, ' ', COLUMN_TYPE
                , '\n, old_', COLUMN_NAME, ' ', COLUMN_TYPE SEPARATOR '') text
                FROM INFORMATION_SCHEMA.COLUMNS C
                WHERE C.TABLE_NAME = table_name
                AND C.TABLE_SCHEMA = DATABASE()
                AND C.COLUMN_TYPE != 'text'
                ));
            SET text := CONCAT(text, ') ENGINE=MEMORY;');
            -- Создаём переменные
            SET text := CONCAT(text, (SELECT GROUP_CONCAT('\nSET @new_', COLUMN_NAME, ' := '
                , IF(trigger_type = 'DELETE', 'NULL', CONCAT('NEW.', COLUMN_NAME)), ';'
                , '\nSET @old_', COLUMN_NAME, ' := '
                , IF(trigger_type = 'INSERT', 'NULL', CONCAT('OLD.', COLUMN_NAME)), ';'
                SEPARATOR '') text
                FROM INFORMATION_SCHEMA.COLUMNS C
                WHERE C.TABLE_NAME = table_name
                AND C.TABLE_SCHEMA = DATABASE()
                AND C.COLUMN_TYPE != 'text'
                ));
        END IF;
        SET text := CONCAT(text, '\nINSERT INTO ', table_name, '_tmp_trg VALUES ("', SUBSTR(trigger_time, 1, 1), '", "', SUBSTR(trigger_type, 1, 1), '", ');
        -- заполним col_changed для UPDATE
        IF trigger_type = 'UPDATE' THEN
            SET text := CONCAT(text, 'CONCAT('
                , (SELECT GROUP_CONCAT(CONCAT('IF(IFNULL(NEW.'
                       , COLUMN_NAME, ', "-ЪъЪ") != IFNULL(OLD.', COLUMN_NAME, ', "-ЪъЪ"), "|', COLUMN_NAME, '|", "")'
                   ) SEPARATOR ', ') text
                   FROM INFORMATION_SCHEMA.COLUMNS C
                   WHERE C.TABLE_NAME = table_name
                   AND C.TABLE_SCHEMA = DATABASE()
                   AND C.COLUMN_TYPE != 'text'
                  )
                , '), ');
        ELSE
            SET text := CONCAT(text, 'NULL, ');
        END IF;
        -- остальные поля
        SET text := CONCAT(text, (SELECT GROUP_CONCAT(
            CASE
                WHEN trigger_time = 'BEFORE' THEN CONCAT('@new_', COLUMN_NAME)
                WHEN trigger_type = 'DELETE' THEN 'NULL'
                ELSE CONCAT('NEW.', COLUMN_NAME)
            END
            , ', '
            , CASE
                WHEN trigger_time = 'BEFORE' THEN CONCAT('@old_', COLUMN_NAME)
                WHEN trigger_type = 'INSERT' THEN 'NULL'
                ELSE CONCAT('OLD.', COLUMN_NAME)
            END
            SEPARATOR ', ') text
            FROM INFORMATION_SCHEMA.COLUMNS C
            WHERE C.TABLE_NAME = table_name
            AND C.TABLE_SCHEMA = DATABASE()
            AND C.COLUMN_TYPE != 'text'
            ));
        SET text := CONCAT(text, ');');
        SET text := CONCAT(text, '\nCALL ', table_name, '_trg_proc;');
        IF trigger_time = 'BEFORE' THEN
            SET text := CONCAT(text
            , IF(trigger_type = 'DELETE'
              , ''
              , (SELECT CONCAT('\nSELECT '
                 , GROUP_CONCAT('new_', COLUMN_NAME SEPARATOR ', ')
                 , '\nINTO ', GROUP_CONCAT('@new_', COLUMN_NAME SEPARATOR ', ')
                 , '\nFROM ', table_name, '_tmp_trg;'
                 , GROUP_CONCAT('\nSET NEW.', COLUMN_NAME, ' := @new_', COLUMN_NAME, ';' SEPARATOR '')
                 ) text
                 FROM INFORMATION_SCHEMA.COLUMNS C
                 WHERE C.TABLE_NAME = table_name
                  AND C.TABLE_SCHEMA = DATABASE()
                  AND C.COLUMN_TYPE != 'text'
                )
              )
            );
            SET text := CONCAT(text, '\nDELETE FROM ', table_name, '_tmp_trg;');
        ELSE
            SET text := CONCAT(text, '\nDROP TEMPORARY TABLE ', table_name, '_tmp_trg;');
        END IF;
        SET text := CONCAT(text, '\nEND;');
        RETURN text;
    END$
    


    Logging Trigger Generation Funktion gen_prot_trigger :

    gen_prot_trigger
    DELIMITER $
    DROP FUNCTION IF EXISTS gen_prot_trigger$
    CREATE FUNCTION gen_prot_trigger(table_name VARCHAR(200), trigger_time VARCHAR(200), trigger_type VARCHAR(200)) RETURNS TEXT BEGIN
        DECLARE text TEXT;
        DECLARE denormalize_columns TEXT;
        DECLARE denormalize_values TEXT;
        DECLARE f_exclude_table INT;
        SET group_concat_max_len = 9000000;
        -- исключим таблицу если её не надо протоколировать, в ней нет поля id или протоколирование выключено
        SET f_exclude_table := (
            SELECT CASE WHEN pd.id IS NOT NULL THEN 1
              WHEN pc.id IS NOT NULL THEN 1
              WHEN C.COLUMN_NAME IS NULL THEN 1
             END
            FROM (SELECT NULL FROM dual) d
            LEFT JOIN protocol_config pd ON pd.command = 'disable_protocol'
            LEFT JOIN protocol_config pc ON pc.command = 'exclude_table' AND pc.table_name = table_name
            LEFT JOIN INFORMATION_SCHEMA.COLUMNS C ON C.TABLE_SCHEMA = DATABASE()
             AND C.TABLE_NAME = table_name
             AND C.COLUMN_NAME = 'id'
        );
        IF trigger_time = 'BEFORE' OR f_exclude_table = 1 OR table_name IN ('protocol', 'protocol_pos') THEN
            RETURN '';
        END IF;
        SET text := CONCAT('\nprot_proc: BEGIN
    DECLARE prot_id INT;
    IF @disable_', table_name, '_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN
    LEAVE prot_proc;
    END IF;');
        -- Проверяем что изменилось хоть 1 поле, иначе выходим из протоколирования
        IF trigger_type = 'UPDATE' THEN
            SET text := CONCAT(text
            , '\nIF '
            , (SELECT GROUP_CONCAT('IFNULL(NEW.'
                , C.COLUMN_NAME, ', "-ЪъЪ") = IFNULL(OLD.', C.COLUMN_NAME, ', "-ЪъЪ")'
                SEPARATOR ' AND '
                ) text
                FROM INFORMATION_SCHEMA.COLUMNS C
                LEFT JOIN protocol_config ec ON ec.command = 'exclude_column'
                 AND ec.table_name = C.TABLE_NAME
                 AND ec.column_name = C.COLUMN_NAME
                WHERE C.TABLE_NAME = table_name
                 AND C.TABLE_SCHEMA = DATABASE()
                 AND C.COLUMN_TYPE != 'text'
                 AND ec.id IS NULL)
            , ' THEN
    LEAVE prot_proc;
    END IF;'
            );
        END IF;
        -- денормализованные поля для таблицы protocol
        SELECT IFNULL(GROUP_CONCAT(', ', dc.denormalize_column ORDER BY dc.id SEPARATOR ''), '') denormalize_columns
        , IFNULL(GROUP_CONCAT(', '
            , CASE trigger_type WHEN 'DELETE' THEN 'OLD' ELSE 'NEW' END
            , dc.column_name
            ORDER BY dc.id SEPARATOR ', '
          )
          , '') denormalize_values
        INTO denormalize_columns, denormalize_values
        FROM INFORMATION_SCHEMA.COLUMNS C
        INNER JOIN protocol_config dc ON dc.command = 'denormalize_column'
         AND dc.table_name = C.TABLE_NAME
         AND dc.column_name = C.COLUMN_NAME
        WHERE C.TABLE_NAME = table_name
         AND C.TABLE_SCHEMA = DATABASE()
        ;
        -- Вставляем строчку в протокол
        SET text := CONCAT(text, '\nINSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent'
    , denormalize_columns, ')
    SELECT IFNULL(u.email, USER()) username, "', SUBSTR(trigger_type, 1, 1), '", "', table_name, '"'
    , ', ', CASE trigger_type WHEN 'DELETE' THEN 'OLD' ELSE 'NEW' END, '.id'
    , ', au.ip, au.user_agent'
    , denormalize_values, '
    FROM (SELECT NULL FROM dual) d
    LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID()
    LEFT JOIN users u ON u.id = au.user_id;
    SET prot_id := LAST_INSERT_ID();');
        -- Генерим строчки для вставки полей в позиции протокола
        SET text := CONCAT(text
        , '\nINSERT INTO protocol_pos (prot_id, column_name, '
        , CASE trigger_type WHEN 'INSERT' THEN 'new_val'
            WHEN 'UPDATE' THEN 'old_val, new_val'
            WHEN 'DELETE' THEN 'old_val'
          END
        , ')\n'
        , (SELECT GROUP_CONCAT('SELECT prot_id, "', C.COLUMN_NAME, '", '
            , CASE WHEN trigger_type = 'UPDATE'
               THEN CONCAT('OLD.', C.COLUMN_NAME, ', NEW.', C.COLUMN_NAME, ' FROM dual WHERE IFNULL(NEW.', C.COLUMN_NAME, ', "-ЪъЪ") != IFNULL(OLD.', C.COLUMN_NAME, ', "-ЪъЪ")')
               WHEN trigger_type = 'INSERT'
               THEN CONCAT('NEW.', C.COLUMN_NAME)
               WHEN trigger_type = 'DELETE'
               THEN CONCAT('OLD.', C.COLUMN_NAME)
              END
            SEPARATOR '\nUNION ALL '
            ) text
            FROM INFORMATION_SCHEMA.COLUMNS C
            LEFT JOIN protocol_config ec ON ec.command = 'exclude_column'
             AND ec.table_name = C.TABLE_NAME
             AND ec.column_name = C.COLUMN_NAME
            WHERE C.TABLE_NAME = table_name
             AND C.TABLE_SCHEMA = DATABASE()
             AND C.COLUMN_TYPE != 'text'
             AND ec.id IS NULL
          )
        , ';\nEND;'
        );
        RETURN text;
    END$
    


    Generate_trigger- Funktion - Geschäftslogik + Protokollierung:

    generate_trigger
    DELIMITER $
    DROP FUNCTION IF EXISTS generate_trigger$
    CREATE FUNCTION generate_trigger(table_name VARCHAR(200), trigger_time VARCHAR(200), trigger_type VARCHAR(200)) RETURNS TEXT
    BEGIN
        DECLARE text TEXT;
        DECLARE bl_text TEXT;
        DECLARE prot_text TEXT;
        DECLARE trigger_time_short VARCHAR(3);
        DECLARE trigger_type_short VARCHAR(3);
        SET group_concat_max_len = 9000000;
        SET trigger_time_short := LOWER(SUBSTR(trigger_time, 1, 3));
        SET trigger_type_short := LOWER(SUBSTR(trigger_type, 1, 3));
        SET text := '';
        SET text := CONCAT(text, 'DROP TRIGGER IF EXISTS ', table_name, '_', trigger_time_short, '_', trigger_type_short, '_trg$');
        SET bl_text := gen_bl_trigger(table_name, trigger_time, trigger_type);
        SET prot_text := gen_prot_trigger(table_name, trigger_time, trigger_type);
        IF bl_text = '' AND prot_text = '' THEN
            RETURN text;
        END IF;
        SET text := CONCAT(text, '\nCREATE TRIGGER ', table_name, '_', trigger_time_short, '_', trigger_type_short, '_trg ', trigger_time, ' ', trigger_type, ' ON ', table_name,' FOR EACH ROW
    trg_proc:BEGIN
    IF @disable_', table_name, '_trg = 1 OR @disable_all_trg = 1 THEN
    LEAVE trg_proc;
    END IF;'
        , bl_text
        , prot_text
        , '\nEND$\n'
        );
        RETURN text;
    END$
    


    Die Funktion generate_triggers zum Generieren des Texts aller Trigger in einer Tabelle:

    generate_triggers
    DELIMITER $
    DROP FUNCTION IF EXISTS generate_triggers$
    CREATE FUNCTION generate_triggers(p_table_name VARCHAR(200)) RETURNS TEXT BEGIN
        DECLARE table_name VARCHAR(200);
        DECLARE text TEXT;
        SET group_concat_max_len = 9000000;
        SET table_name := p_table_name;
        SET text := '';
        SET text := (SELECT GROUP_CONCAT(generate_trigger(table_name, trigger_time, trigger_type) SEPARATOR '\n')
        FROM (SELECT 'BEFORE' trigger_time
        UNION ALL SELECT 'AFTER' trigger_time) trigger_time
        , (SELECT 'INSERT' trigger_type
        UNION ALL SELECT 'UPDATE' trigger_type
        UNION ALL SELECT 'DELETE' trigger_type
        ) trigger_type);
        RETURN text;
    END$
    


    Die Autorisierung wird im Artikel "Implementieren der Sicherheit auf Zeilenebene in MySQL" beschrieben.

    DELIMITER ;
    DROP TABLE IF EXISTS users;
    CREATE TABLE `users` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `email` varchar(100) NOT NULL,
      `pass` varchar(30) NOT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `email` (`email`)
    ) DEFAULT CHARSET=utf8 COMMENT='Пользователи системы';
    DROP TABLE IF EXISTS auth_users;
    CREATE TABLE `auth_users` (
      `conn_id` bigint(20) NOT NULL,
      `user_id` int(11) NOT NULL,
      `login_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `ip` varchar(45) DEFAULT NULL,
      `user_agent` varchar(256) DEFAULT NULL,
      PRIMARY KEY (`conn_id`)
      -- , FOREIGN KEY (user_id) REFERENCES users(id)
    ) ENGINE=MEMORY DEFAULT CHARSET=utf8 COMMENT='Авторизованные пользователи';
    

    Erstellen Sie nun ein paar Testmuster:

    DROP TABLE IF EXISTS doc_pos;
    DROP TABLE IF EXISTS docs;
    CREATE TABLE `docs` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `num` VARCHAR(20) NOT NULL,
      `date` DATE NOT NULL,
      `warehouse` VARCHAR(100) NOT NULL,
      `partner` VARCHAR(100) NOT NULL,
      PRIMARY KEY (`id`)
    ) DEFAULT CHARSET=utf8 COMMENT='Документы';
    DROP TABLE IF EXISTS doc_pos;
    CREATE TABLE `doc_pos` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `doc_id` int(11) NOT NULL,
      `material` VARCHAR(100) NOT NULL,
      `amount` int(11) NOT NULL,
      `price` int(11) NOT NULL,
      PRIMARY KEY (`id`)
      , FOREIGN KEY (doc_id) REFERENCES docs(id)
    ) DEFAULT CHARSET=utf8 COMMENT='Позиции документов';
    

    Führen Sie eine Anforderung aus, um die Richtigkeit der Trigger in der Datenbank zu überprüfen:

    Eine Anforderung zur Überprüfung der Richtigkeit von Triggern in der Datenbank
    SELECT table_name, comment, rows_cn, data_len_mb
    , MAX(need_bl_trg) need_bl_trg
    , MAX(exclude_prot) exclude_prot
    , MAX(CASE
        WHEN action_statement != gen_trg THEN CONCAT('SELECT generate_triggers("', table_name, '")')
        WHEN gen_trg IS NULL AND (exclude_prot IS NULL OR need_bl_trg = 1) THEN CONCAT('SELECT generate_triggers("', table_name, '")')
      END
      ) create_trg
    FROM (
    SELECT table_name, comment, rows_cn, data_len_mb, need_bl_trg, exclude_prot, action_statement, gen_trg
    FROM (
    SELECT table_name, comment, rows_cn, data_len_mb, need_bl_trg, exclude_prot
    , CONCAT(SUBSTRING_INDEX(gen_trg, '\n', 2), '\n', action_statement, '$', '\n') action_statement
    , gen_trg
    FROM (
    SELECT t.TABLE_NAME table_name
    , t.TABLE_COMMENT comment
    , t.TABLE_ROWS rows_cn
    , ROUND(t.DATA_LENGTH / 1024 / 1024, 2) data_len_mb
    , CASE WHEN r.ROUTINE_NAME IS NOT NULL THEN 1 END need_bl_trg
    , CASE WHEN pd.id IS NOT NULL THEN 'Протокол выключен'
       WHEN pc.id IS NOT NULL THEN 'Исключено настройкой'
       WHEN t.TABLE_NAME IN ('protocol', 'protocol_pos') THEN 'Не протоколируется'
       WHEN C.COLUMN_NAME IS NULL THEN 'Нет поля id'
      END exclude_prot
    , tr.ACTION_STATEMENT action_statement
    , generate_trigger(tr.EVENT_OBJECT_TABLE, tr.ACTION_TIMING, tr.EVENT_MANIPULATION) gen_trg
    FROM INFORMATION_SCHEMA.TABLES t
    LEFT JOIN INFORMATION_SCHEMA.ROUTINES r ON r.ROUTINE_NAME = CONCAT(t.TABLE_NAME, '_trg_proc')
     AND r.ROUTINE_TYPE = 'PROCEDURE' AND r.ROUTINE_SCHEMA = t.TABLE_SCHEMA
    LEFT JOIN protocol_config pd ON pd.command = 'disable_protocol'
    LEFT JOIN protocol_config pc ON pc.command = 'exclude_table' AND pc.table_name = t.TABLE_NAME
    LEFT JOIN INFORMATION_SCHEMA.COLUMNS C ON C.TABLE_SCHEMA = t.TABLE_SCHEMA
     AND C.TABLE_NAME = t.TABLE_NAME AND C.COLUMN_NAME = 'id'
    LEFT JOIN INFORMATION_SCHEMA.TRIGGERS tr ON tr.TRIGGER_SCHEMA = t.TABLE_SCHEMA
     AND tr.EVENT_OBJECT_TABLE = t.TABLE_NAME
    WHERE t.TABLE_SCHEMA = DATABASE()
     AND t.TABLE_TYPE = 'BASE TABLE'
     AND t.ENGINE = 'InnoDB'
    ) d) d) d
    GROUP BY table_name, comment, rows_cn, data_len_mb
    ORDER BY table_name
    ;
    


    + --------------- + -------------------------- + ------ - + ----------- + ----------- + ------------------ + ----- -------------------------------------- +
    | table_name | comment | rows_cn | data_len_mb | need_bl_trg | exclude_prot | create_trg |
    + --------------- + -------------------------- + ------ - + ----------- + ----------- + ------------------ + ----- -------------------------------------- +
    | docs | Documents | 0 | 0,02 | NULL | NULL | SELECT generate_triggers ("docs") |
    | doc_pos | Positionen von Dokumenten | 0 | 0,02 | NULL | NULL | SELECT generate_triggers ("doc_pos") |
    | protocol | Änderungsprotokoll | 0 | 0,02 | NULL | Nicht angemeldet | NULL |
    | protocol_config | Protokollierungskonfiguration | 0 | 0,02 | NULL | NULL | SELECT generate_triggers ("protocol_config") |
    | protocol_pos | Änderungsprotokollfelder | 0 | 0,02 | NULL | Nicht angemeldet | NULL |
    | users | Systembenutzer | 0 | 0,02 | NULL | NULL | SELECT generate_triggers ("users") |
    + --------------- + -------------------------- + ------ - + ----------- + ----------- + ------------------ + ----- -------------------------------------- +
    

    Das System bietet uns die Möglichkeit, Protokollierungsauslöser für die

    Tabellen docs, doc_pos, protocol_config und users zu erstellen , und die vorherige Abfrage mit SELECT zu schließen und erneut auszuführen:

    Eine Anforderung zur Überprüfung der Richtigkeit von Triggern in der Datenbank
    SELECT GROUP_CONCAT(create_trg SEPARATOR '\nUNION ALL ') sql_text
    FROM (
    SELECT table_name, comment, rows_cn, data_len_mb
    , MAX(need_bl_trg) need_bl_trg
    , MAX(exclude_prot) exclude_prot
    , MAX(CASE
        WHEN action_statement != gen_trg THEN CONCAT('SELECT generate_triggers("', table_name, '")')
        WHEN gen_trg IS NULL AND (exclude_prot IS NULL OR need_bl_trg = 1) THEN CONCAT('SELECT generate_triggers("', table_name, '")')
      END
      ) create_trg
    FROM (
    SELECT table_name, comment, rows_cn, data_len_mb, need_bl_trg, exclude_prot, action_statement, gen_trg
    FROM (
    SELECT table_name, comment, rows_cn, data_len_mb, need_bl_trg, exclude_prot
    , CONCAT(SUBSTRING_INDEX(gen_trg, '\n', 2), '\n', action_statement, '$', '\n') action_statement
    , gen_trg
    FROM (
    SELECT t.TABLE_NAME table_name
    , t.TABLE_COMMENT comment
    , t.TABLE_ROWS rows_cn
    , ROUND(t.DATA_LENGTH / 1024 / 1024, 2) data_len_mb
    , CASE WHEN r.ROUTINE_NAME IS NOT NULL THEN 1 END need_bl_trg
    , CASE WHEN pd.id IS NOT NULL THEN 'Протокол выключен'
       WHEN pc.id IS NOT NULL THEN 'Исключено настройкой'
       WHEN t.TABLE_NAME IN ('protocol', 'protocol_pos') THEN 'Не протоколируется'
       WHEN C.COLUMN_NAME IS NULL THEN 'Нет поля id'
      END exclude_prot
    , tr.ACTION_STATEMENT action_statement
    , generate_trigger(tr.EVENT_OBJECT_TABLE, tr.ACTION_TIMING, tr.EVENT_MANIPULATION) gen_trg
    FROM INFORMATION_SCHEMA.TABLES t
    LEFT JOIN INFORMATION_SCHEMA.ROUTINES r ON r.ROUTINE_NAME = CONCAT(t.TABLE_NAME, '_trg_proc')
     AND r.ROUTINE_TYPE = 'PROCEDURE' AND r.ROUTINE_SCHEMA = t.TABLE_SCHEMA
    LEFT JOIN protocol_config pd ON pd.command = 'disable_protocol'
    LEFT JOIN protocol_config pc ON pc.command = 'exclude_table' AND pc.table_name = t.TABLE_NAME
    LEFT JOIN INFORMATION_SCHEMA.COLUMNS C ON C.TABLE_SCHEMA = t.TABLE_SCHEMA
     AND C.TABLE_NAME = t.TABLE_NAME AND C.COLUMN_NAME = 'id'
    LEFT JOIN INFORMATION_SCHEMA.TRIGGERS tr ON tr.TRIGGER_SCHEMA = t.TABLE_SCHEMA
     AND tr.EVENT_OBJECT_TABLE = t.TABLE_NAME
    WHERE t.TABLE_SCHEMA = DATABASE()
     AND t.TABLE_TYPE = 'BASE TABLE'
     AND t.ENGINE = 'InnoDB'
    ) d) d) d
    GROUP BY table_name, comment, rows_cn, data_len_mb
    ORDER BY table_name
    ) d
    ;
    

    Ergebnis:
    SELECT generate_triggers("docs")
    UNION ALL SELECT generate_triggers("doc_pos")
    UNION ALL SELECT generate_triggers("protocol_config")
    UNION ALL SELECT generate_triggers("users")
    ;
    

    Lassen Sie uns diese Anfrage jetzt ausführen:

    SELECT generate_triggers (docs) UNION ALL SELECT ....
    DROP TRIGGER IF EXISTS docs_bef_ins_trg$
    DROP TRIGGER IF EXISTS docs_aft_ins_trg$
    CREATE TRIGGER docs_aft_ins_trg AFTER INSERT ON docs FOR EACH ROW
    trg_proc:BEGIN
    IF @disable_docs_trg = 1 OR @disable_all_trg = 1 THEN
    LEAVE trg_proc;
    END IF;
    prot_proc: BEGIN
    DECLARE prot_id INT;
    IF @disable_docs_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN
    LEAVE prot_proc;
    END IF;
    INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent)
    SELECT IFNULL(u.email, USER()) username, "I", "docs", NEW.id, au.ip, au.user_agent
    FROM (SELECT NULL FROM dual) d
    LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID()
    LEFT JOIN users u ON u.id = au.user_id;
    SET prot_id := LAST_INSERT_ID();
    INSERT INTO protocol_pos (prot_id, column_name, new_val)
    SELECT prot_id, "id", NEW.id
    UNION ALL SELECT prot_id, "num", NEW.num
    UNION ALL SELECT prot_id, "date", NEW.date
    UNION ALL SELECT prot_id, "warehouse", NEW.warehouse
    UNION ALL SELECT prot_id, "partner", NEW.partner;
    END;
    END$
    DROP TRIGGER IF EXISTS docs_bef_upd_trg$
    DROP TRIGGER IF EXISTS docs_aft_upd_trg$
    CREATE TRIGGER docs_aft_upd_trg AFTER UPDATE ON docs FOR EACH ROW
    trg_proc:BEGIN
    IF @disable_docs_trg = 1 OR @disable_all_trg = 1 THEN
    LEAVE trg_proc;
    END IF;
    prot_proc: BEGIN
    DECLARE prot_id INT;
    IF @disable_docs_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN
    LEAVE prot_proc;
    END IF;
    IF IFNULL(NEW.id, "-ЪъЪ") = IFNULL(OLD.id, "-ЪъЪ") AND IFNULL(NEW.num, "-ЪъЪ") = IFNULL(OLD.num, "-ЪъЪ") AND IFNULL(NEW.date, "-ЪъЪ") = IFNULL(OLD.date, "-ЪъЪ") AND IFNULL(NEW.warehouse, "-ЪъЪ") = IFNULL(OLD.warehouse, "-ЪъЪ") AND IFNULL(NEW.partner, "-ЪъЪ") = IFNULL(OLD.partner, "-ЪъЪ") THEN
    LEAVE prot_proc;
    END IF;
    INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent)
    SELECT IFNULL(u.email, USER()) username, "U", "docs", NEW.id, au.ip, au.user_agent
    FROM (SELECT NULL FROM dual) d
    LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID()
    LEFT JOIN users u ON u.id = au.user_id;
    SET prot_id := LAST_INSERT_ID();
    INSERT INTO protocol_pos (prot_id, column_name, old_val, new_val)
    SELECT prot_id, "id", OLD.id, NEW.id FROM dual WHERE IFNULL(NEW.id, "-ЪъЪ") != IFNULL(OLD.id, "-ЪъЪ")
    UNION ALL SELECT prot_id, "num", OLD.num, NEW.num FROM dual WHERE IFNULL(NEW.num, "-ЪъЪ") != IFNULL(OLD.num, "-ЪъЪ")
    UNION ALL SELECT prot_id, "date", OLD.date, NEW.date FROM dual WHERE IFNULL(NEW.date, "-ЪъЪ") != IFNULL(OLD.date, "-ЪъЪ")
    UNION ALL SELECT prot_id, "warehouse", OLD.warehouse, NEW.warehouse FROM dual WHERE IFNULL(NEW.warehouse, "-ЪъЪ") != IFNULL(OLD.warehouse, "-ЪъЪ")
    UNION ALL SELECT prot_id, "partner", OLD.partner, NEW.partner FROM dual WHERE IFNULL(NEW.partner, "-ЪъЪ") != IFNULL(OLD.partner, "-ЪъЪ");
    END;
    END$
    DROP TRIGGER IF EXISTS docs_bef_del_trg$
    DROP TRIGGER IF EXISTS docs_aft_del_trg$
    CREATE TRIGGER docs_aft_del_trg AFTER DELETE ON docs FOR EACH ROW
    trg_proc:BEGIN
    IF @disable_docs_trg = 1 OR @disable_all_trg = 1 THEN
    LEAVE trg_proc;
    END IF;
    prot_proc: BEGIN
    DECLARE prot_id INT;
    IF @disable_docs_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN
    LEAVE prot_proc;
    END IF;
    INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent)
    SELECT IFNULL(u.email, USER()) username, "D", "docs", OLD.id, au.ip, au.user_agent
    FROM (SELECT NULL FROM dual) d
    LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID()
    LEFT JOIN users u ON u.id = au.user_id;
    SET prot_id := LAST_INSERT_ID();
    INSERT INTO protocol_pos (prot_id, column_name, old_val)
    SELECT prot_id, "id", OLD.id
    UNION ALL SELECT prot_id, "num", OLD.num
    UNION ALL SELECT prot_id, "date", OLD.date
    UNION ALL SELECT prot_id, "warehouse", OLD.warehouse
    UNION ALL SELECT prot_id, "partner", OLD.partner;
    END;
    END$
    DROP TRIGGER IF EXISTS users_bef_ins_trg$
    DROP TRIGGER IF EXISTS users_aft_ins_trg$
    CREATE TRIGGER users_aft_ins_trg AFTER INSERT ON users FOR EACH ROW
    trg_proc:BEGIN
    IF @disable_users_trg = 1 OR @disable_all_trg = 1 THEN
    LEAVE trg_proc;
    END IF;
    prot_proc: BEGIN
    DECLARE prot_id INT;
    IF @disable_users_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN
    LEAVE prot_proc;
    END IF;
    INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent)
    SELECT IFNULL(u.email, USER()) username, "I", "users", NEW.id, au.ip, au.user_agent
    FROM (SELECT NULL FROM dual) d
    LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID()
    LEFT JOIN users u ON u.id = au.user_id;
    SET prot_id := LAST_INSERT_ID();
    INSERT INTO protocol_pos (prot_id, column_name, new_val)
    SELECT prot_id, "id", NEW.id
    UNION ALL SELECT prot_id, "email", NEW.email
    UNION ALL SELECT prot_id, "pass", NEW.pass;
    END;
    END$
    DROP TRIGGER IF EXISTS users_bef_upd_trg$
    DROP TRIGGER IF EXISTS users_aft_upd_trg$
    CREATE TRIGGER users_aft_upd_trg AFTER UPDATE ON users FOR EACH ROW
    trg_proc:BEGIN
    IF @disable_users_trg = 1 OR @disable_all_trg = 1 THEN
    LEAVE trg_proc;
    END IF;
    prot_proc: BEGIN
    DECLARE prot_id INT;
    IF @disable_users_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN
    LEAVE prot_proc;
    END IF;
    IF IFNULL(NEW.id, "-ЪъЪ") = IFNULL(OLD.id, "-ЪъЪ") AND IFNULL(NEW.email, "-ЪъЪ") = IFNULL(OLD.email, "-ЪъЪ") AND IFNULL(NEW.pass, "-ЪъЪ") = IFNULL(OLD.pass, "-ЪъЪ") THEN
    LEAVE prot_proc;
    END IF;
    INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent)
    SELECT IFNULL(u.email, USER()) username, "U", "users", NEW.id, au.ip, au.user_agent
    FROM (SELECT NULL FROM dual) d
    LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID()
    LEFT JOIN users u ON u.id = au.user_id;
    SET prot_id := LAST_INSERT_ID();
    INSERT INTO protocol_pos (prot_id, column_name, old_val, new_val)
    SELECT prot_id, "id", OLD.id, NEW.id FROM dual WHERE IFNULL(NEW.id, "-ЪъЪ") != IFNULL(OLD.id, "-ЪъЪ")
    UNION ALL SELECT prot_id, "email", OLD.email, NEW.email FROM dual WHERE IFNULL(NEW.email, "-ЪъЪ") != IFNULL(OLD.email, "-ЪъЪ")
    UNION ALL SELECT prot_id, "pass", OLD.pass, NEW.pass FROM dual WHERE IFNULL(NEW.pass, "-ЪъЪ") != IFNULL(OLD.pass, "-ЪъЪ");
    END;
    END$
    DROP TRIGGER IF EXISTS users_bef_del_trg$
    DROP TRIGGER IF EXISTS users_aft_del_trg$
    CREATE TRIGGER users_aft_del_trg AFTER DELETE ON users FOR EACH ROW
    trg_proc:BEGIN
    IF @disable_users_trg = 1 OR @disable_all_trg = 1 THEN
    LEAVE trg_proc;
    END IF;
    prot_proc: BEGIN
    DECLARE prot_id INT;
    IF @disable_users_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN
    LEAVE prot_proc;
    END IF;
    INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent)
    SELECT IFNULL(u.email, USER()) username, "D", "users", OLD.id, au.ip, au.user_agent
    FROM (SELECT NULL FROM dual) d
    LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID()
    LEFT JOIN users u ON u.id = au.user_id;
    SET prot_id := LAST_INSERT_ID();
    INSERT INTO protocol_pos (prot_id, column_name, old_val)
    SELECT prot_id, "id", OLD.id
    UNION ALL SELECT prot_id, "email", OLD.email
    UNION ALL SELECT prot_id, "pass", OLD.pass;
    END;
    END$
    DROP TRIGGER IF EXISTS doc_pos_bef_ins_trg$
    DROP TRIGGER IF EXISTS doc_pos_aft_ins_trg$
    CREATE TRIGGER doc_pos_aft_ins_trg AFTER INSERT ON doc_pos FOR EACH ROW
    trg_proc:BEGIN
    IF @disable_doc_pos_trg = 1 OR @disable_all_trg = 1 THEN
    LEAVE trg_proc;
    END IF;
    prot_proc: BEGIN
    DECLARE prot_id INT;
    IF @disable_doc_pos_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN
    LEAVE prot_proc;
    END IF;
    INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent)
    SELECT IFNULL(u.email, USER()) username, "I", "doc_pos", NEW.id, au.ip, au.user_agent
    FROM (SELECT NULL FROM dual) d
    LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID()
    LEFT JOIN users u ON u.id = au.user_id;
    SET prot_id := LAST_INSERT_ID();
    INSERT INTO protocol_pos (prot_id, column_name, new_val)
    SELECT prot_id, "id", NEW.id
    UNION ALL SELECT prot_id, "doc_id", NEW.doc_id
    UNION ALL SELECT prot_id, "material", NEW.material
    UNION ALL SELECT prot_id, "amount", NEW.amount
    UNION ALL SELECT prot_id, "price", NEW.price;
    END;
    END$
    DROP TRIGGER IF EXISTS doc_pos_bef_upd_trg$
    DROP TRIGGER IF EXISTS doc_pos_aft_upd_trg$
    CREATE TRIGGER doc_pos_aft_upd_trg AFTER UPDATE ON doc_pos FOR EACH ROW
    trg_proc:BEGIN
    IF @disable_doc_pos_trg = 1 OR @disable_all_trg = 1 THEN
    LEAVE trg_proc;
    END IF;
    prot_proc: BEGIN
    DECLARE prot_id INT;
    IF @disable_doc_pos_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN
    LEAVE prot_proc;
    END IF;
    IF IFNULL(NEW.id, "-ЪъЪ") = IFNULL(OLD.id, "-ЪъЪ") AND IFNULL(NEW.doc_id, "-ЪъЪ") = IFNULL(OLD.doc_id, "-ЪъЪ") AND IFNULL(NEW.material, "-ЪъЪ") = IFNULL(OLD.material, "-ЪъЪ") AND IFNULL(NEW.amount, "-ЪъЪ") = IFNULL(OLD.amount, "-ЪъЪ") AND IFNULL(NEW.price, "-ЪъЪ") = IFNULL(OLD.price, "-ЪъЪ") THEN
    LEAVE prot_proc;
    END IF;
    INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent)
    SELECT IFNULL(u.email, USER()) username, "U", "doc_pos", NEW.id, au.ip, au.user_agent
    FROM (SELECT NULL FROM dual) d
    LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID()
    LEFT JOIN users u ON u.id = au.user_id;
    SET prot_id := LAST_INSERT_ID();
    INSERT INTO protocol_pos (prot_id, column_name, old_val, new_val)
    SELECT prot_id, "id", OLD.id, NEW.id FROM dual WHERE IFNULL(NEW.id, "-ЪъЪ") != IFNULL(OLD.id, "-ЪъЪ")
    UNION ALL SELECT prot_id, "doc_id", OLD.doc_id, NEW.doc_id FROM dual WHERE IFNULL(NEW.doc_id, "-ЪъЪ") != IFNULL(OLD.doc_id, "-ЪъЪ")
    UNION ALL SELECT prot_id, "material", OLD.material, NEW.material FROM dual WHERE IFNULL(NEW.material, "-ЪъЪ") != IFNULL(OLD.material, "-ЪъЪ")
    UNION ALL SELECT prot_id, "amount", OLD.amount, NEW.amount FROM dual WHERE IFNULL(NEW.amount, "-ЪъЪ") != IFNULL(OLD.amount, "-ЪъЪ")
    UNION ALL SELECT prot_id, "price", OLD.price, NEW.price FROM dual WHERE IFNULL(NEW.price, "-ЪъЪ") != IFNULL(OLD.price, "-ЪъЪ");
    END;
    END$
    DROP TRIGGER IF EXISTS doc_pos_bef_del_trg$
    DROP TRIGGER IF EXISTS doc_pos_aft_del_trg$
    CREATE TRIGGER doc_pos_aft_del_trg AFTER DELETE ON doc_pos FOR EACH ROW
    trg_proc:BEGIN
    IF @disable_doc_pos_trg = 1 OR @disable_all_trg = 1 THEN
    LEAVE trg_proc;
    END IF;
    prot_proc: BEGIN
    DECLARE prot_id INT;
    IF @disable_doc_pos_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN
    LEAVE prot_proc;
    END IF;
    INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent)
    SELECT IFNULL(u.email, USER()) username, "D", "doc_pos", OLD.id, au.ip, au.user_agent
    FROM (SELECT NULL FROM dual) d
    LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID()
    LEFT JOIN users u ON u.id = au.user_id;
    SET prot_id := LAST_INSERT_ID();
    INSERT INTO protocol_pos (prot_id, column_name, old_val)
    SELECT prot_id, "id", OLD.id
    UNION ALL SELECT prot_id, "doc_id", OLD.doc_id
    UNION ALL SELECT prot_id, "material", OLD.material
    UNION ALL SELECT prot_id, "amount", OLD.amount
    UNION ALL SELECT prot_id, "price", OLD.price;
    END;
    END$
    DROP TRIGGER IF EXISTS protocol_config_bef_ins_trg$
    DROP TRIGGER IF EXISTS protocol_config_aft_ins_trg$
    CREATE TRIGGER protocol_config_aft_ins_trg AFTER INSERT ON protocol_config FOR EACH ROW
    trg_proc:BEGIN
    IF @disable_protocol_config_trg = 1 OR @disable_all_trg = 1 THEN
    LEAVE trg_proc;
    END IF;
    prot_proc: BEGIN
    DECLARE prot_id INT;
    IF @disable_protocol_config_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN
    LEAVE prot_proc;
    END IF;
    INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent)
    SELECT IFNULL(u.email, USER()) username, "I", "protocol_config", NEW.id, au.ip, au.user_agent
    FROM (SELECT NULL FROM dual) d
    LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID()
    LEFT JOIN users u ON u.id = au.user_id;
    SET prot_id := LAST_INSERT_ID();
    INSERT INTO protocol_pos (prot_id, column_name, new_val)
    SELECT prot_id, "id", NEW.id
    UNION ALL SELECT prot_id, "command", NEW.command
    UNION ALL SELECT prot_id, "table_name", NEW.table_name
    UNION ALL SELECT prot_id, "column_name", NEW.column_name
    UNION ALL SELECT prot_id, "denormalize_column", NEW.denormalize_column;
    END;
    END$
    DROP TRIGGER IF EXISTS protocol_config_bef_upd_trg$
    DROP TRIGGER IF EXISTS protocol_config_aft_upd_trg$
    CREATE TRIGGER protocol_config_aft_upd_trg AFTER UPDATE ON protocol_config FOR EACH ROW
    trg_proc:BEGIN
    IF @disable_protocol_config_trg = 1 OR @disable_all_trg = 1 THEN
    LEAVE trg_proc;
    END IF;
    prot_proc: BEGIN
    DECLARE prot_id INT;
    IF @disable_protocol_config_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN
    LEAVE prot_proc;
    END IF;
    IF IFNULL(NEW.id, "-ЪъЪ") = IFNULL(OLD.id, "-ЪъЪ") AND IFNULL(NEW.command, "-ЪъЪ") = IFNULL(OLD.command, "-ЪъЪ") AND IFNULL(NEW.table_name, "-ЪъЪ") = IFNULL(OLD.table_name, "-ЪъЪ") AND IFNULL(NEW.column_name, "-ЪъЪ") = IFNULL(OLD.column_name, "-ЪъЪ") AND IFNULL(NEW.denormalize_column, "-ЪъЪ") = IFNULL(OLD.denormalize_column, "-ЪъЪ") THEN
    LEAVE prot_proc;
    END IF;
    INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent)
    SELECT IFNULL(u.email, USER()) username, "U", "protocol_config", NEW.id, au.ip, au.user_agent
    FROM (SELECT NULL FROM dual) d
    LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID()
    LEFT JOIN users u ON u.id = au.user_id;
    SET prot_id := LAST_INSERT_ID();
    INSERT INTO protocol_pos (prot_id, column_name, old_val, new_val)
    SELECT prot_id, "id", OLD.id, NEW.id FROM dual WHERE IFNULL(NEW.id, "-ЪъЪ") != IFNULL(OLD.id, "-ЪъЪ")
    UNION ALL SELECT prot_id, "command", OLD.command, NEW.command FROM dual WHERE IFNULL(NEW.command, "-ЪъЪ") != IFNULL(OLD.command, "-ЪъЪ")
    UNION ALL SELECT prot_id, "table_name", OLD.table_name, NEW.table_name FROM dual WHERE IFNULL(NEW.table_name, "-ЪъЪ") != IFNULL(OLD.table_name, "-ЪъЪ")
    UNION ALL SELECT prot_id, "column_name", OLD.column_name, NEW.column_name FROM dual WHERE IFNULL(NEW.column_name, "-ЪъЪ") != IFNULL(OLD.column_name, "-ЪъЪ")
    UNION ALL SELECT prot_id, "denormalize_column", OLD.denormalize_column, NEW.denormalize_column FROM dual WHERE IFNULL(NEW.denormalize_column, "-ЪъЪ") != IFNULL(OLD.denormalize_column, "-ЪъЪ");
    END;
    END$
    DROP TRIGGER IF EXISTS protocol_config_bef_del_trg$
    DROP TRIGGER IF EXISTS protocol_config_aft_del_trg$
    CREATE TRIGGER protocol_config_aft_del_trg AFTER DELETE ON protocol_config FOR EACH ROW
    trg_proc:BEGIN
    IF @disable_protocol_config_trg = 1 OR @disable_all_trg = 1 THEN
    LEAVE trg_proc;
    END IF;
    prot_proc: BEGIN
    DECLARE prot_id INT;
    IF @disable_protocol_config_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN
    LEAVE prot_proc;
    END IF;
    INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent)
    SELECT IFNULL(u.email, USER()) username, "D", "protocol_config", OLD.id, au.ip, au.user_agent
    FROM (SELECT NULL FROM dual) d
    LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID()
    LEFT JOIN users u ON u.id = au.user_id;
    SET prot_id := LAST_INSERT_ID();
    INSERT INTO protocol_pos (prot_id, column_name, old_val)
    SELECT prot_id, "id", OLD.id
    UNION ALL SELECT prot_id, "command", OLD.command
    UNION ALL SELECT prot_id, "table_name", OLD.table_name
    UNION ALL SELECT prot_id, "column_name", OLD.column_name
    UNION ALL SELECT prot_id, "denormalize_column", OLD.denormalize_column;
    END;
    END$
    


    Wir haben den Text der Trigger erhalten, führen ihn aus (mit DELIMITER $ ).

    Jetzt werden unsere Tabellen protokolliert und alle Datenänderungen in das Protokoll geschrieben.
    Überprüfen Sie die Protokollierungseinstellungen bei der ersten Anforderung:
    Eine Anforderung zur Überprüfung der Richtigkeit von Triggern in der Datenbank
    DELIMITER ;
    SELECT table_name, comment, rows_cn, data_len_mb
    , MAX(need_bl_trg) need_bl_trg
    , MAX(exclude_prot) exclude_prot
    , MAX(CASE
        WHEN action_statement != gen_trg THEN CONCAT('SELECT generate_triggers("', table_name, '")')
        WHEN gen_trg IS NULL AND (exclude_prot IS NULL OR need_bl_trg = 1) THEN CONCAT('SELECT generate_triggers("', table_name, '")')
      END
      ) create_trg
    FROM (
    SELECT table_name, comment, rows_cn, data_len_mb, need_bl_trg, exclude_prot, action_statement, gen_trg
    FROM (
    SELECT table_name, comment, rows_cn, data_len_mb, need_bl_trg, exclude_prot
    , CONCAT(SUBSTRING_INDEX(gen_trg, '\n', 2), '\n', action_statement, '$', '\n') action_statement
    , gen_trg
    FROM (
    SELECT t.TABLE_NAME table_name
    , t.TABLE_COMMENT comment
    , t.TABLE_ROWS rows_cn
    , ROUND(t.DATA_LENGTH / 1024 / 1024, 2) data_len_mb
    , CASE WHEN r.ROUTINE_NAME IS NOT NULL THEN 1 END need_bl_trg
    , CASE WHEN pd.id IS NOT NULL THEN 'Протокол выключен'
       WHEN pc.id IS NOT NULL THEN 'Исключено настройкой'
       WHEN t.TABLE_NAME IN ('protocol', 'protocol_pos') THEN 'Не протоколируется'
       WHEN C.COLUMN_NAME IS NULL THEN 'Нет поля id'
      END exclude_prot
    , tr.ACTION_STATEMENT action_statement
    , generate_trigger(tr.EVENT_OBJECT_TABLE, tr.ACTION_TIMING, tr.EVENT_MANIPULATION) gen_trg
    FROM INFORMATION_SCHEMA.TABLES t
    LEFT JOIN INFORMATION_SCHEMA.ROUTINES r ON r.ROUTINE_NAME = CONCAT(t.TABLE_NAME, '_trg_proc')
     AND r.ROUTINE_TYPE = 'PROCEDURE' AND r.ROUTINE_SCHEMA = t.TABLE_SCHEMA
    LEFT JOIN protocol_config pd ON pd.command = 'disable_protocol'
    LEFT JOIN protocol_config pc ON pc.command = 'exclude_table' AND pc.table_name = t.TABLE_NAME
    LEFT JOIN INFORMATION_SCHEMA.COLUMNS C ON C.TABLE_SCHEMA = t.TABLE_SCHEMA
     AND C.TABLE_NAME = t.TABLE_NAME AND C.COLUMN_NAME = 'id'
    LEFT JOIN INFORMATION_SCHEMA.TRIGGERS tr ON tr.TRIGGER_SCHEMA = t.TABLE_SCHEMA
     AND tr.EVENT_OBJECT_TABLE = t.TABLE_NAME
    WHERE t.TABLE_SCHEMA = DATABASE()
     AND t.TABLE_TYPE = 'BASE TABLE'
     AND t.ENGINE = 'InnoDB'
    ) d) d) d
    GROUP BY table_name, comment, rows_cn, data_len_mb
    ORDER BY table_name
    ;
    


    + --------------- + ---------------------- + ------ - + ----------- + ----------- + ------------------ + ----- ----- +
    | table_name | comment | rows_cn | data_len_mb | need_bl_trg | exclude_prot | create_trg |
    + --------------- + -------------------------- + ------ - + ----------- + ----------- + ------------------ + ----- ----- +
    | docs | Documents | 0 | 0,02 | NULL | NULL | NULL |
    | doc_pos | Positionen von Dokumenten | 0 | 0,02 | NULL | NULL | NULL |
    | protocol | Änderungsprotokoll | 0 | 0,02 | NULL | Nicht angemeldet | NULL |
    | protocol_config | Protokollierungskonfiguration | 0 | 0,02 | NULL | NULL | NULL |
    | protocol_pos | Änderungsprotokollfelder | 0 | 0,02 | NULL | Nicht angemeldet | NULL |
    | users | Systembenutzer | 0 | 0,02 | NULL | NULL | NULL |
    + --------------- + -------------------------- + ------ - + ----------- + ----------- + ------------------ + ----- ----- +
    6 Zeilen im Set, 0 Warnungen (5,33 Sek.)
    

    Fügen Sie einen Benutzer hinzu und melden Sie sich an:
    /*
    DELETE FROM doc_pos;
    DELETE FROM docs;
    DELETE FROM auth_users;
    DELETE FROM users;
    DELETE FROM protocol_pos;
    DELETE FROM protocol;
    */
    INSERT INTO users (email, pass) VALUES ('test@test.ru', '12345');
    Query OK, 1 row affected (0.01 sec)
    INSERT INTO auth_users (conn_id, user_id)
    SELECT CONNECTION_ID() conn_id
    , (SELECT u.id FROM users u WHERE u.email = 'test@test.ru') user_id
    ;
    Query OK, 1 row affected (0.00 sec)
    

    Legen Sie ein Testdokument ein:

    BEGIN;
    INSERT INTO docs (num, date, warehouse, partner)
    VALUES ('1', '2018-07-17', 'Склад Москва', 'Горизонт, ООО');
    SET @doc_id := LAST_INSERT_ID();
    INSERT INTO doc_pos (doc_id, material, amount, price)
    VALUES (@doc_id, 'Краски акварельные', 10, 52)
    , (@doc_id, 'Краски акриловые', 20, 165)
    , (@doc_id, 'Ручка шариковая', 7, 30);
    COMMIT;
    

    Mal sehen, was im Protokoll passiert ist:

    SELECT id, date, oper, table_name, table_id
    , (SELECT GROUP_CONCAT(pp.column_name, ': ('
         , IFNULL(pp.old_val, 'NULL')
         , ', '
         , IFNULL(pp.new_val, 'NULL')
         , ')'
         SEPARATOR ', '
         )
       FROM protocol_pos pp
       WHERE pp.prot_id = p.id
       ) vals
    , p.username
    FROM protocol p;
    

    Anfrage für HTML
    SELECT id, date, oper, table_name, table_id
    , (SELECT CONCAT(''
       , GROUP_CONCAT(''
         , ''
         , ''
         SEPARATOR ''
         )
       , '
    ', pp.column_name, '', IFNULL(pp.old_val, "NULL"), '', IFNULL(pp.new_val, "NULL"), '
    ' ) FROM protocol_pos pp WHERE pp.prot_id = p.id ) vals , p.username FROM protocol p;


    + ---- + -------------------- + ------ + ------------ + - -------- + ----------------------------------------- -------------------------------------------------- ------------------------------ + ------------------- +
    | id | Datum | oper | Tabellenname | table_id | vals | Benutzername |
    + ---- + -------------------- + ------ + ------------ + - -------- + ----------------------------------------- -------------------------------------------------- ------------------------------ + ------------------- +
    | 1 | 2018-10-09 17:21:27 | Ich | Benutzer | 1 | E-Mail: (NULL, test@test.ru), ID: (NULL, 1), Pass: (NULL, 12345) | admin@myhosting.ru |
    | 2 | 2018-10-09 17:21:51 | Ich | docs | 1 | Datum: (NULL, 2018-07-17), ID: (NULL, 1), Nummer: (NULL, 1), Partner: (NULL, Horizon, LLC), Lager: (NULL, Lager Moskau) | test@test.ru |
    | 3 | 2018-10-09 17:21:51 | Ich | doc_pos | 1 | Menge: (NULL, 10), doc_id: (NULL, 1), ID: (NULL, 1), Material: (NULL, Aquarellfarben), Preis: (NULL, 52) | test@test.ru |
    | 4 | 2018-10-09 17:21:51 | Ich | doc_pos | 2 | Menge: (NULL, 20), doc_id: (NULL, 1), ID: (NULL, 2), Material: (NULL, Acrylfarben), Preis: (NULL, 165) | test@test.ru |
    | 5 | 2018-10-09 17:21:51 | Ich | doc_pos | 3 | Menge: (NULL, 7), doc_id: (NULL, 1), ID: (NULL, 3), Material: (NULL, Kugelschreiber), Preis: (NULL, 30) | test@test.ru |
    + ---- + -------------------- + ------ + ------------ + - -------- + ----------------------------------------- -------------------------------------------------- ------------------------------ + ------------------- +
    

    Wie Sie sehen, werden alle Datenbankänderungen ab dem Einfügen des Benutzers protokolliert.

    Wir erhalten den Umsatzbericht:

    SELECT NOW() report_time, d.date, SUM(p.amount * p.price) sum
    FROM docs d
    INNER JOIN doc_pos p ON d.id = p.doc_id
    GROUP BY d.date;
    

    + --------------------- + ------------ + ------ +
    | report_time | Datum | sum |
    + --------------------- + ------------ + ------ +
    | 2018-10-09 17:23:47 | 2018-07-17 | 4030 |
    + --------------------- + ------------ + ------ +
    

    Ändern Sie nun das vorhandene Dokument und fügen Sie ein weiteres hinzu:
    BEGIN;
    SET @doc_id := (SELECT id FROM docs WHERE num = '1');
    UPDATE docs SET date = '2018-07-16', warehouse = warehouse WHERE id = @doc_id;
    DELETE FROM doc_pos WHERE doc_id = @doc_id AND material = 'Ручка шариковая';
    UPDATE doc_pos p SET p.price = 105, p.material = 'Краски масляные' WHERE p.doc_id = @doc_id AND p.material = 'Краски акриловые';
    INSERT INTO docs (num, date, warehouse, partner)
    VALUES ('2', '2018-07-18', 'Склад Новосибирск', 'Радуга, ЗАО');
    SET @doc_id := LAST_INSERT_ID();
    INSERT INTO doc_pos (doc_id, material, amount, price)
    VALUES (@doc_id, 'Рамка 10*15', 5, 102)
    , (@doc_id, 'Бумага А4', 2, 165);
    COMMIT;
    

    Dies sieht aus wie ein neues Protokoll
    SELECT id, date, oper, table_name, table_id
    , (SELECT GROUP_CONCAT(pp.column_name, ': ('
         , IFNULL(pp.old_val, 'NULL')
         , ', '
         , IFNULL(pp.new_val, 'NULL')
         , ')'
         SEPARATOR ', '
         )
       FROM protocol_pos pp
       WHERE pp.prot_id = p.id
       ) vals
    , p.username
    FROM protocol p;
    

    + ---- + -------------------- + ------ + ------------ + - -------- + ----------------------------------------- -------------------------------------------------- --------------------------------- + ---------------- --- +
    | id | Datum | oper | Tabellenname | table_id | vals | Benutzername |
    + ---- + -------------------- + ------ + ------------ + - -------- + ----------------------------------------- -------------------------------------------------- --------------------------------- + ---------------- --- +
    | 1 | 2018-10-09 17:21:27 | Ich | Benutzer | 1 | E-Mail: (NULL, test@test.ru), ID: (NULL, 1), Pass: (NULL, 12345) | admin@myhosting.ru |
    | 2 | 2018-10-09 17:21:51 | Ich | docs | 1 | Datum: (NULL, 2018-07-17), ID: (NULL, 1), Nummer: (NULL, 1), Partner: (NULL, Horizon, LLC), Lager: (NULL, Lager Moskau) | test@test.ru |
    | 3 | 2018-10-09 17:21:51 | Ich | doc_pos | 1 | Menge: (NULL, 10), doc_id: (NULL, 1), ID: (NULL, 1), Material: (NULL, Aquarellfarben), Preis: (NULL, 52) | test@test.ru |
    | 4 | 2018-10-09 17:21:51 | Ich | doc_pos | 2 | Menge: (NULL, 20), doc_id: (NULL, 1), ID: (NULL, 2), Material: (NULL, Acrylfarben), Preis: (NULL, 165) | test@test.ru |
    | 5 | 2018-10-09 17:21:51 | Ich | doc_pos | 3 | Menge: (NULL, 7), doc_id: (NULL, 1), ID: (NULL, 3), Material: (NULL, Kugelschreiber), Preis: (NULL, 30) | test@test.ru |
    | 6 | 2018-10-09 17:24:27 | U | docs | 1 | Datum: (2018-07-17, 2018-07-16) | test@test.ru |
    | 7 | 2018-10-09 17:24:27 | D | doc_pos | 3 | Menge: (7, NULL), doc_id: (1, NULL), ID: (3, NULL), Material: (Kugelschreiber, NULL), Preis: (30, NULL) | test@test.ru |
    | 8 | 2018-10-09 17:24:27 | U | doc_pos | 2 | Material: (Acrylfarben, Ölfarben), Preis: (165, 105) | test@test.ru |
    | 9 | 2018-10-09 17:24:27 | Ich | docs | 2 | Datum: (NULL, 2018-07-18), ID: (NULL, 2), Nummer: (NULL, 2), Partner: (NULL, Rainbow, CJSC), Lager: (NULL, Lager Novosibirsk) | test@test.ru |
    | 10 | 2018-10-09 17:24:27 | Ich | doc_pos | 4 | Menge: (NULL, 5), doc_id: (NULL, 2), ID: (NULL, 4), Material: (NULL, Frame 10 * 15), Preis: (NULL, 102) | test@test.ru |
    | 11 | 2018-10-09 17:24:27 | Ich | doc_pos | 5 | Menge: (NULL, 2), doc_id: (NULL, 2), ID: (NULL, 5), Material: (NULL, A4-Papier), Preis: (NULL, 165) | test@test.ru |
    + ---- + -------------------- + ------ + ------------ + - -------- + ----------------------------------------- -------------------------------------------------- --------------------------------- + ---------------- --- +
    
    Holen Sie sich einen neuen Bericht:
    SELECT NOW() report_time, d.date, SUM(p.amount * p.price) sum
    FROM docs d
    INNER JOIN doc_pos p ON d.id = p.doc_id
    GROUP BY d.date;
    

    + --------------------- + ------------ + ------ +
    | report_time | Datum | sum |
    + --------------------- + ------------ + ------ +
    | 2018-10-09 17:26:18 | 2018-07-16 | 2620 |
    | 2018-10-09 17:26:18 | 2018-07-18 | 840 |
    + --------------------- + ------------ + ------ +
    

    Wir sehen uns den Bericht an und können die Daten für 2018-07-17 nicht finden , obwohl wir uns genau daran erinnern, was sie waren, haben wir sogar einen gedruckten Bericht für 2018-10-09 17:23:47.

    Lassen Sie uns MySQL beibringen , in die Vergangenheit zu schauen! Zu diesem Zweck schreiben wir Prozeduren, die gemäß dem Protokoll die Änderungen rückgängig machen können.

    Die Prozedur exec_protocol führt Änderungen in der Protokollzeile durch (p_prot_id)
    exec_protocol
    DELIMITER $
    DROP PROCEDURE IF EXISTS exec_protocol$
    CREATE PROCEDURE exec_protocol(p_prot_id BIGINT, direction INT) BEGIN
        DECLARE p_sql_text TEXT;
        DECLARE done INT DEFAULT FALSE;
        DECLARE cur CURSOR FOR
        SELECT CONCAT(
          CASE WHEN p.oper = 'I' AND direction = 1 OR p.oper = 'D' AND direction = -1 THEN 'INSERT INTO'
              WHEN p.oper = 'U' THEN 'UPDATE'
              WHEN p.oper = 'D' AND direction = 1 OR p.oper = 'I' AND direction = -1 THEN 'DELETE FROM'
            END
          , ' ', p.table_name, ' '
          , CASE WHEN p.oper = 'I' AND direction = 1 OR p.oper = 'D' AND direction = -1
              THEN CONCAT('(', GROUP_CONCAT(pos.column_name ORDER BY pos.column_name SEPARATOR ', '), ')'
              , ' VALUES (', GROUP_CONCAT(QUOTE(CASE direction WHEN 1 THEN pos.new_val WHEN -1 THEN pos.old_val END)
                ORDER BY pos.column_name
                SEPARATOR ', '
                )
              , ')'
              )
              WHEN p.oper = 'U'
              THEN CONCAT('SET ', GROUP_CONCAT(pos.column_name
                , ' = ', QUOTE(CASE direction WHEN 1 THEN pos.new_val WHEN -1 THEN pos.old_val END)
                ORDER BY pos.column_name
                SEPARATOR ', '
                )
              , ' WHERE id = ', p.table_id
              )
              WHEN p.oper = 'D' AND direction = 1 OR p.oper = 'I' AND direction = -1
              THEN CONCAT('WHERE id = ', p.table_id)
            END
          ) sql_text
        FROM protocol p
        INNER JOIN protocol_pos pos ON p.id = pos.prot_id
        WHERE p.id = p_prot_id
        ;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
        OPEN cur;
        read_loop: LOOP
            FETCH cur INTO p_sql_text;
            IF done THEN
                LEAVE read_loop;
            END IF;
            SET @exec_protocol_sql_text := p_sql_text;
            SET @disable_all_prot_trg = 1;
            -- SELECT @exec_protocol_sql_text;
            PREPARE c_sql FROM @exec_protocol_sql_text;
            EXECUTE c_sql;
            DEALLOCATE PREPARE c_sql;
            SET @disable_all_prot_trg = NULL;
        END LOOP;
        CLOSE cur;
    END$
    


    Die Prozedur set_prot_snapshot_id führt ein Rollback / Rollback von Protokolländerungen im ID-Bereich durch
    set_prot_snapshot_id
    DELIMITER $
    DROP PROCEDURE IF EXISTS set_prot_snapshot_id$
    CREATE PROCEDURE set_prot_snapshot_id(p_beg_prot_id BIGINT, p_end_prot_id BIGINT, direction INT) BEGIN
        DECLARE p_prot_id BIGINT;
        DECLARE done INT DEFAULT FALSE;
        DECLARE cur CURSOR FOR
          SELECT p.id
          FROM protocol p
          WHERE p.id >= p_beg_prot_id
           AND (p.id <= p_end_prot_id OR p_end_prot_id IS NULL)
          ORDER BY p.id * direction
          ;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
        OPEN cur;
        read_loop: LOOP
            FETCH cur INTO p_prot_id;
            IF done THEN
                LEAVE read_loop;
            END IF;
            CALL exec_protocol(p_prot_id, SIGN(direction));
            -- Если direction = -2, то откатываем базу с удалением протокола
            IF direction = -2 THEN
                DELETE FROM protocol WHERE id = p_prot_id;
            END IF;
        END LOOP;
        CLOSE cur;
    END$
    


    Die Prozedur set_prot_snapshot_date setzt / rollt Protokolländerungen über einen Zeitraum zurück

    set_prot_snapshot_date
    DELIMITER $
    DROP PROCEDURE IF EXISTS set_prot_snapshot_date$
    CREATE PROCEDURE set_prot_snapshot_date(p_beg_date TIMESTAMP, p_end_date TIMESTAMP, direction INT) BEGIN
        DECLARE beg_prot_id BIGINT;
        DECLARE end_prot_id BIGINT;
        SET beg_prot_id := (SELECT id FROM protocol WHERE date >= p_beg_date ORDER BY id LIMIT 1);
        SET end_prot_id := (SELECT id FROM protocol WHERE date <= p_end_date ORDER BY id DESC LIMIT 1);
        CALL set_prot_snapshot_id(beg_prot_id, end_prot_id, direction);
    END$
    


    Jetzt können wir leicht einen Verkaufsbericht für das letzte Datum bekommen:

    DELIMITER ;
    BEGIN;
    CALL set_prot_snapshot_date('2018-10-09 17:23:47', NULL, -1);
    SELECT NOW() report_time, d.date, SUM(p.amount * p.price) sum
    FROM docs d
    INNER JOIN doc_pos p ON d.id = p.doc_id
    GROUP BY d.date;
    ROLLBACK;
    

    + --------------------- + ------------ + ------ +
    | report_time | Datum | sum |
    + --------------------- + ------------ + ------ +
    | 2018-10-09 17:28:30 | 2018-07-17 | 4030 |
    + --------------------- + ------------ + ------ +
    

    Wie Sie sehen, ist der Bericht genauso ausgefallen wie in der Vergangenheit.
    Und seitdem wir haben ROLLBACK gemacht , jetzt ist es genauso einfach, einen neuen Bericht zu bekommen:

    SELECT NOW() report_time, d.date, SUM(p.amount * p.price) sum
    FROM docs d
    INNER JOIN doc_pos p ON d.id = p.doc_id
    GROUP BY d.date;
    

    + --------------------- + ------------ + ------ +
    | report_time | Datum | sum |
    + --------------------- + ------------ + ------ +
    | 2018-10-09 17:29:18 | 2018-07-16 | 2620 |
    | 2018-10-09 17:29:18 | 2018-07-18 | 840 |
    + --------------------- + ------------ + ------ +
    


    Welche Funktionen bietet die Protokollierung?

    1. Die Möglichkeit, Berichte zu jedem Zeitpunkt in der Vergangenheit zu erhalten, genau wie zu diesem Zeitpunkt.
    2. Suchen Sie nach dem Benutzer, der die Daten in der Datenbank "durcheinandergebracht" hat.
    3. Analysieren der Datenänderungshistorie. Zum Beispiel die Geschwindigkeit, mit der Dokumente im System weitergeleitet werden und deren Status sich ändert.
    4. Änderungen verwerfen. Wenn Sie beispielsweise ein Dokument löschen, anstatt zusätzliche Fragen zu stellen: „Möchten Sie es wirklich löschen?“, Können Sie die Änderung abbrechen.
    5. Erweiterung S. 4, Warenkorb, Stornierung von Änderungen, Rollback entsprechend der Änderungshistorie des Dokuments.


    UPD1: Leistungstest


    Einfügen von 10.000 Zeilen, 4 Felder pro Zeile
    1. Mit Triggern und Protokollierung - 6.89c
    2. Mit Triggern wird die Protokollierung programmgesteuert deaktiviert - 2.17s
    3. Ohne Trigger - 1.37c
    SQL-Abfragen
    DELIMITER $
    DROP PROCEDURE IF EXISTS speed_test$
    CREATE PROCEDURE speed_test(n INT)
    BEGIN
        DECLARE i INT DEFAULT 0;
        WHILE i < n DO
            INSERT INTO docs (num, date, warehouse, partner)
            VALUES (CONCAT('тест', i), DATE(NOW()), 'Склад Новосибирск', 'Радуга, ЗАО');
            SET i := i + 1;
        END WHILE;
    END$
    -- 1. с триггерами и протоколированием
    DELIMITER ;
    BEGIN;
    CALL speed_test(10000);
    ROLLBACK;
    MariaDB [test-habr]> DELIMITER ;
    MariaDB [test-habr]> BEGIN;
    Query OK, 0 rows affected (0.00 sec)
    MariaDB [test-habr]> CALL speed_test(10000);
    Query OK, 1 row affected (6.89 sec)
    MariaDB [test-habr]> ROLLBACK;
    Query OK, 0 rows affected (0.88 sec)
    -- 2. с триггерами, протоколирование отключено программно
    DELIMITER ;
    BEGIN;
    SET @disable_all_prot_trg = 1;
    CALL speed_test(10000);
    SET @disable_all_prot_trg = NULL;
    ROLLBACK;
    MariaDB [test-habr]> DELIMITER ;
    MariaDB [test-habr]> BEGIN;
    Query OK, 0 rows affected (0.00 sec)
    MariaDB [test-habr]> SET @disable_all_prot_trg = 1;
    Query OK, 0 rows affected (0.00 sec)
    MariaDB [test-habr]> CALL speed_test(10000);
    Query OK, 1 row affected (2.17 sec)
    MariaDB [test-habr]> SET @disable_all_prot_trg = NULL;
    Query OK, 0 rows affected (0.00 sec)
    MariaDB [test-habr]> ROLLBACK;
    Query OK, 0 rows affected (0.12 sec)
    -- 3. без триггеров
    DELIMITER ;
    DROP TRIGGER IF EXISTS docs_aft_ins_trg;
    BEGIN;
    CALL speed_test(10000);
    ROLLBACK;
    MariaDB [test-habr]> DELIMITER ;
    MariaDB [test-habr]> DROP TRIGGER IF EXISTS docs_aft_ins_trg;
    Query OK, 0 rows affected (0.00 sec)
    MariaDB [test-habr]> BEGIN;
    Query OK, 0 rows affected (0.00 sec)
    MariaDB [test-habr]> CALL speed_test(10000);
    Query OK, 1 row affected (1.37 sec)
    MariaDB [test-habr]> ROLLBACK;
    Query OK, 0 rows affected (0.12 sec)
    



    UPD2: alternative Systeme


    1. Systemversionierte Tabellen erschienen in MariaDB 10.3.4
    von den Profis: Die im SQL: 2011-Standard beschriebene native Lösung funktioniert wahrscheinlich effizienter als die Lösung für Trigger, ein
    deutliches Minus - Sie können nicht herausfinden, wer Änderungen an der Datenbank vorgenommen hat.
    2. PHP-Audit , eine Lösung ähnlich der meinen Bei Triggern werden Trigger von PHP
    aus den Profis generiert : Die Lösung ist auf Github
    aus den Minuspunkten wunderschön gestaltet : Das Vorhandensein einer separaten Protokolltabelle für jedes Ziel ermöglicht nicht, erstens alle Benutzeraktionen für den Zeitraum zu verfolgen und zweitens das Schreiben von Skripten für das Rollback nicht zu vereinfachen DB in bestimmten ten Zustand

    Jetzt auch beliebt: