Skip to main content
Solved

Add a default constraint on (trace) columns

K.Bakkenes
Captain
Forum|alt.badge.img+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.

Best answer by Jasper

K.Bakkenes wrote:
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
1 Attachments
Did this topic help you find an answer to your question?

8 replies

G
Vanguard
Forum|alt.badge.img+1
  • gariensVanguard
  • Vanguard
  • 16 replies
  • May 24, 2019
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
Captain
Forum|alt.badge.img+9
  • K.BakkenesCaptain
  • Author
  • Captain
  • 41 replies
  • May 24, 2019
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 Attachments
Robert Jan de Nie
R
2 people like this
  • Robert Jan de Nie
    Robert Jan de Nie
  • R
    Roald
Jasper
Superhero
  • JasperSuperhero
  • 678 replies
  • May 28, 2019
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
1 person likes this
  • K.Bakkenes
    K.Bakkenes
K.Bakkenes
Captain
Forum|alt.badge.img+9
  • K.BakkenesCaptain
  • Author
  • Captain
  • 41 replies
  • May 28, 2019
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
Thinkwise blogger
Forum|alt.badge.img+5
K.Bakkenes wrote:
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.
K.Bakkenes
1 person likes this
  • K.Bakkenes
    K.Bakkenes
Jasper
Superhero
  • JasperSuperhero
  • 678 replies
  • Answer
  • June 7, 2019
K.Bakkenes wrote:
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.
K.Bakkenes
1 person likes this
  • K.Bakkenes
    K.Bakkenes
Frank Wijnhout
Thinkwise blogger

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
Thinkwise blogger

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

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings