Solved

Add a default constraint on (trace) columns

  • 23 May 2019
  • 6 replies
  • 209 views

Userlevel 4
Badge +5
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

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.
View original

6 replies

Badge
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
Userlevel 4
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):


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.
Userlevel 6
Badge +6
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?
Userlevel 4
Badge +5
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.
Userlevel 4
Badge +1
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.
Userlevel 6
Badge +6
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.

Reply