Solved

Add a default constraint on (trace) columns

  • 23 May 2019
  • 8 replies
  • 348 views
K.Bakkenes
Rank
Userlevel 3
Badge +9
Is it possible to add a 'DEFAULT' constraint to columns via the TSF? Currently we fill trace columns via insert and update triggers but this results in unwanted trigger actions.

The insert trigger will update the same table and thereby triggers the update trigger. Which in return executes the whole update trigger. (See code below)
We have multiple places where this trigger nesting causes errors or wrong results.

If we could add a DEFAULT constraint to the insert columns the code of the insert trigger won't be necessary anymore.

icon

Best answer by Jasper 7 June 2019, 10:23

View original
1 Attachment

8 replies

G
Rank
Userlevel 1
Badge +1
If this is the only purpose of your insert en update triggers you could use the following code to find if the trigger is triggered by another trigger.

if (select trigger_nestlevel(object_id('[table]_ti')) ) > 0
or
(select trigger_nestlevel(object_id('[table]_tu')) ) > 1
begin
return
end

In case of your example the following code should be added to your update trigger:

if (select trigger_nestlevel(object_id('[action_overview_ti')) ) > 0
or
(select trigger_nestlevel(object_id('[action_overview_tu')) ) > 1
begin
return
end
K.Bakkenes
Rank
Userlevel 3
Badge +9
I think that's an ugly work-around. Performance-wise it's also better to prevent an unnecessary update trigger, just because the insert trace columns need to be filled.

I still would like the feature to add a default constraint on a table column. Which should result in something like this (but then for the columns insert_user and insert_date_time):


If the above possibilty is added we can remove the update on trace columns for the insert trigger and only keep the update in the update trigger.
1 Attachment
Jasper
Rank
Userlevel 7
Badge +11
We do support default constraints, but only using literal values. I totally agree that it would be a nice addition to also support SQL functions, so please submit an idea for that!

We have multiple places where this trigger nesting causes errors or wrong results.


What errors are these nested triggers causing?
K.Bakkenes
Rank
Userlevel 3
Badge +9
Hi Jasper,

for example: The table product has a product_id as PK and resource_id as FK. In the insert trigger of product a record must be inserted into the table resource. After the resource is created the resource_id column must be updated to the product table. In the update trigger of product a gross price must be created using the resource_id from the product table.
If the update on the trace columns is prior to the insert (and update) of the resource_id column then the update trigger will fail because resource_id will be empty while creating a gross price.
Robert Jan de Nie
Rank
Userlevel 5
Badge +5
I think that's an ugly work-around. Performance-wise it's also better to prevent an unnecessary update trigger, just because the insert trace columns need to be filled.

I still would like the feature to add a default constraint on a table column. Which should result in something like this (but then for the columns insert_user and insert_date_time):



You'll have my vote straight away! In general, I would like to be able to use the power of the database more. This is a great example.
Jasper
Rank
Userlevel 7
Badge +11
Hi Jasper,

for example: The table product has a product_id as PK and resource_id as FK. In the insert trigger of product a record must be inserted into the table resource. After the resource is created the resource_id column must be updated to the product table. In the update trigger of product a gross price must be created using the resource_id from the product table.
If the update on the trace columns is prior to the insert (and update) of the resource_id column then the update trigger will fail because resource_id will be empty while creating a gross price.

You can of course solve this by taking a possible empty resource_id into account in the update trigger, but in general we always recommend doing updates from within a trigger on the same table at the end of the trigger to prevent any side-effects.
Frank Wijnhout
Rank

Hey Kevin,

You have to realize that database defaults don't work for update statements. It only works for insert statements where the field with the default is not in the column list.

With this knowledge, is it still something you really need?

Frank Wijnhout
Rank

Another issue is the use of user defined functions in database defaults, like tsf_user(). Changing these functions is not possible when used in a database default. So, changing the function would mean a database upgrade. Personally I wouldn’t want that.

As a work around we'll have to create a wrapper function which calls the actual function. Not really a nice solution.

Reply


Terms & ConditionsCookie settings