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
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.
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?
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.
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.
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.
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?
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.
