Solved

Call default on editing

  • 9 June 2021
  • 8 replies
  • 158 views
R
Rank
Userlevel 5
Badge +15

I'm trying to convert some trigger logic to default logic, because I would like to use the 'system versioned tables'. Having triggers causes a lot of records in the history table, which isn't necessary.

I'm currently using a trigger to update the 'update_user' and 'update_date_time', and also an update_counter for preventing concurrent editing. 

I've tried to set up a default which sets the update_user and update_date_time. Problem is that it's only fired when a column has default logic enabled and the value is changed. What I want is that the default is also triggered when the user starts editing. Or maybe after he's done editing. Then I would like to update the update user / datetime. When inserting a record this happens (e.g. @cursor_from_col_id is null), but when editing it isn't.

A work around is passing all table columns to the default, but I don't want this because it's unneccesary and a performance killer.

Any suggestions on this case, preventing unneccesary records in the history table?

icon

Best answer by Erwin Ekkel 10 June 2021, 13:24

View original

8 replies

E
Rank
Userlevel 6
Badge +16

Why would you want to modify the 'update_user' and 'update_date_time when nothing was changed? You could just fill these 2 columns when another column is modified triggering the default. 
 

select @update_user = dbo.tsf_user()     , @update_date_time = getdate()
R
Rank
Userlevel 5
Badge +15

The default isn't triggered when the checkbox ‘default input’ and/or ‘default output’ isn't enabled of a column:

In the example above it's only triggered when you leave the (editable) ‘name’ field. It's not triggered when for example leaving the field ‘phone_number’, although it is changed.

So it is only possible when all checkboxes are enabled, but I think that is unneccesary and is performance wise not desireable.

I was hoping the default was triggered when going into edit mode, just like it does when adding a new rule, but unfortunately it doesn't.

 

E
Rank
Userlevel 6
Badge +16

What GUI(s) are you using? Did you try it with the default/input output activated and did it have a huge performance difference? 

 

R
Rank
Userlevel 5
Badge +15

We're using the WebGUI as GUI, but for development purposes (debugging) we also use the Windows GUI. In an early stage of our product development we've encountered some serious performance degradation by passing to much parameters in default, layout and context logic. Our strategy is to pass only the parameters that are needed.

E
Rank
Userlevel 6
Badge +16

Indeed the default is only triggered when modifying a column that is active in the default. You could create a view instead of the actual table. And have an instead of trigger on the view to do the update including the date/user column on the table.

Using the default with deactivated columns is, like you stated, not an option to set the date/user column.

A
Rank
Userlevel 6
Badge +10

Hi @René W I’m curious to hear which solutions you’ll end up with! We have the same situation with system-versioned tables and multiple triggers cluttering the system-versioned data, of which also the update_user/update_date_time and update_counter. Only difference is that we’re using Universal GUI. 

Two things I could mention from our situation:

  • We discussed using Defaults too, but as we have these fields for logging purposes it is important to us to have update_date_time equal the DB transaction time. Since Defaults are set upon entering edit mode or upon editing another column, we didn’t see Defaults as a fitting option. Saving/committing of the record could be done quite a bit later than the defaulted date_time.
  • We have it on the backlog to try and combine the 2 update triggers, this saves a row in the system-versioned table for every commit.

Are there other alternatives to consider?

R
Rank
Userlevel 5
Badge +15

The topic is marked as answered, but in my opinion it isn't. There is unfortunately no answer for my question I think.

A
Rank
Userlevel 6
Badge +10

@René W Two updates from our side on this topic:

  1. We indeed merged the update_user/update_date_time and update_counter triggers into a single one, that saves a record for each change.
  2. We also added a pre-filter on the view that by default hides the trigger-invoked changes, basically by hiding records for which tsf_valid_from = tsf_valid_to. Although this technically doesn't reduce the nr of records in the history table, it does reduce the clutter in the view.

Reply


Terms & ConditionsCookie settings