Save what happens during the session

  1. 12 months ago

    Emile S

    18 Jun 2019 Europe (France, Strasbourg)

    SQLite, but can (eventually) be any other DB engine.

    A crazy idea comes to my mind yesterday or this morning (who cares ?):

    is it included in SQLite a feature that can save what the user do during the session (obviously at the end of the session) ?

    Or, must I do it all by myself ? Say store in a file - whatever - every change the user do at the moment it do it:
    Store the previous Record _and_ the new one WHEN the user make a change,
    Store any newly added REcord (at add time, of course),
    This may lead to a base to a “Who does what ?”.

    The idea comes from some reports I do not understand (errors that happens only on Windows).

    I have an application who is running three mornings a week, but for no apparent reason, fill service TABLEs with a Record (where only one Record is “allowed” / needed; a copyright Record that happears 7 times for example when in another t-service TABLE, I have 12 entries instead of 1…).
    That application store window location and some other properties, but sometimes something go bersek and the values are wrong: the window opens elsewhere (out of the screen monitor).

  2. Scott G

    18 Jun 2019 Xojo Pro

    You might want to look into triggers to monitor inserts, updates and deletes.

  3. Dave S

    18 Jun 2019 San Diego, California USA

    if you want a log of "adds","changes" and deletions, the as Scott indicated define triggers attached to every table in your database, and have them insert records into a "log" table

  4. James D

    18 Jun 2019 Testers, Xojo Pro Europe (Switzerland)

    Most of the applications I have built over the past few years have included an audit feature.

    Each table has a corresponding XXX_<table_name> containing all the attributes of the table being audited excluding the id field, plus record_id, logged_at, action, user, ip_address and application attributes. Where every CRUD operation is recorded.

    There is also an activity log where every significant non CRUD operation is recorded as well, such as requesting a report be generated and so on. And of course there is an error log as well.

    There is also an option to export this data with certain fields, such as account numbers, client names, balances, anonymized. So it can be sent to people like myself who are not allowed to see production data. The anonymization is two way, so that if I refer to account ZXC345, the business users know exactly which production record I’m talking about.

    This has evolved over time because banks never ever allow me have access to their production environment.

  5. Jean-Yves P

    18 Jun 2019 Testers, Xojo Pro Europe (France, Besançon)

    I made a simplier approach using only one table for the logs

    CREATE TABLE __user_logs (
      id_user_log BIGSERIAL NOT NULL DEFAULT nextval('__user_logs_id_user_log_seq'::regclass),
      log_date_time TIMESTAMP ,
      table_involved VARCHAR ,
      record_id_involved INT8 ,
      action VARCHAR ,
      id_user INT4 ,
      id_group INT4 

    each CRUD action is logged in this table, from any table input.
    you can also monitor what you need more if needed.

    as James did, I may add an IP Address log in this table.

  6. Emile S

    19 Jun 2019 Europe (France, Strasbourg)

    Thanks all.

or Sign Up to reply!