Skip to main content

This is an Archived topic. The solution is available in the Thinkstore inside the Software Factory.

Goal

The goal of this solution is to automatically add trace fields to all tables so the user can see who added the record or who made the last change. This solution adds these fields to a separate tab page on a form. This looks like this in the GUI:

 

 

You can choose at what moment you want the fields to be filled: During editing or after saving the record.

Solution

The solution consists of code for Dynamic Model (for adding the fields) and two Control Procedures (for filling the fields). Depending on the moment you want the fields to be filled (during editing or after saving), you can choose  either for a control procedure for defaults or for a control procedure for triggers.

Dynamic model add_trace_columns

This code adds trace columns to every table.

The trace fields are:

  • update_user

  • update_date_time

  • insert_user

  • insert_date_time

If you add the tag 'no_trace' to a table, no trace columns will be added to that table. The trace column are automatically translated in ENG and NL.

Important: Make sure that the Generation order has a low number, e.g. 10. The columns must be generated before the generation of the program objects.

The code in this topic is created in the Software Factory 2020.2.

Control procedure default_fill_trace_columns

This control procedure fills the trace column with the current user and date through defaults.

Template default_fill_trace_columns

This template fills the trace column with the current user and date.

Control procedure trigger_fill_trace_columns

This control procedure fills the trace column with the current user and date by the trigger. Insert triggers and update triggers have different templates.

Template trigger_fill_trace_columns_insert

This template fills the trace columns with the current user and date after an insert.

Template trigger_fill_trace_columns_update

This template fills the update trace columns with the current user and date after an update. If the update trigger is nested in another trigger, the trace columns will not be updated.

In the attachment you will find the code of this solution.

 

Super functionality and a good step in the direction of traceability.

I initially preferred using the triggers, but found it too cumbersome as I'd have to create for each table two triggers and adapting the template_trigger_fill_trace_columns_insert.sql and template_trigger_fill_trace_columns_update.sql code for each tabel.

Using the default was much easier. Create one default-control_procedure (at Functionality) and assign this to all defaults. Then just generate and deploy to the database.

But first the trace-columns must be added to the tables in the database. For this create a Dynamic model (control procedure) and place the code from dynamic_model_add_trace_columns.sql underneath the code-tab. In order to get the trace-columns in the database you have to deploy them to the database using the Deployment > Creation until the Execute source code. This appends to each table the trace-columns.

Something I'm still thinking about is, is adding an extra column that indicates if a record is active or not. Deleted records can then be inactive instead of removed. Another addition could be to keep historic versions of a record. But I'm afraid that this would require me to create additional triggers.

We currently use the trigger variant. One downfall is that when you use triggers combined with system versioning it creates an additional record in the history table, which is probably unintended.

Now that I'm writing this, I'm aware that the <table>_history also contains a valid_from, valid_to timestamp which could be retrieved as trace date. The username could still be set via the default.

Another interesting thing is that this template maybe can be replaced with a default feature for the insert:

 

Nice! For the update trigger you could also use “if trigger_nestlevel(@@procid) = 1”.

This would update the trace columns for each trigger you enter the first time.  

There is a small performance issue with the control procedure default fill trace colums.

In the dynamic code the default is turned on for the tables which have trace colums, but this is done without project_id and project_vrs_id, and also without a check if the use_deaults = 0. This causes the procedure to be extremely slow.

Old code:

update t
set use_defaults = 1
from tab t
where exists (select 1
              from #tab t2
              where t2.tab_id = t.tab_id)

 

Suggestion for the improvement:
 

update t
set use_defaults = 1
from tab t
where t.use_defaults = 0
and exists (select 1
              from #tab t2
              where t2.project_id = t.project_id
              and t2.project_vrs_id = t.project_vrs_id
              and t2.tab_id = t.tab_id)

 

Terms & ConditionsCookie settings