Used by
MySQL - 5.7 - x64

There are several tables with approximately the following count. and field types. The number of fields and field types in different tables is different.

The approximate composition of the tables:

  • pol_1 - DATE,
  • pol_2 - ENUM,
  • pol_3 - TEXT,
  • pol_4 - DATE,
  • pol_5 - TEXT,
  • pol_6 - DATE,
  • pol_7 - TEXT,
  • pol_8 - int,
  • pol_9 - int,
  • pol_10 - TEXT,
  • pol_11 - TEXT,
  • pol_12 - LONGTEXT,
  • pol_13 - int,
  • pol_14 - int,
  • pol_15 - int,
  • pol_16 - int,
  • pol_17 - int,
  • pol_18 - TEXT,
  • pol_19 - TEXT.

Those. in fact, each table uses the following field types:

  • DATE
  • ENUM
  • TEXT
  • INT
  • LONGTEXT

It is required to track insert / update / delete events for each record of each field.

Do I understand correctly that in fact, it is necessary to make a separate journal for each field type, where data from different tables will flow? See screen.

QUESTION

  1. How to organize journaling?
  2. What would the example of the trigger (s) look like for such a task?

enter image description here

  • one
    You can make one table and write there (table-field-old-new-time). You can simply enter the validity field in each table, which is true if the field is valid, and false if not, and create a new record instead of changes, and mark the old one with an invalid one. You can use other logging options. - Akina
  • @Akina "You can make one table and write there (table-field-old-new-time)." - old-new-time - do you mean so for each field? Old-new - text? - koverflow
  • one
    Why not? because any type can be reversibly serialized into a text ... and the initial type is determined by the properties of the table field. - Akina
  • one
    And why id? IMHO extra field. - Akina
  • one
    idblog again - too much. Sense from him - zero. You also have a table field. - Akina

0