[Question] Changing the default value of sys_period upon INSERT INTO

[ALee008]

Dear nearform team,

i have a question regarding the default behavior of the sys_period column upon inserting new rows in an already versioned table. I found these two issues discussing this topic: #6 and #12

Classic temporal tables have worked for our use case up until now. New assets start on creation time, so the default behavior worked just fine. But we are not now challenged with inserting new assets that shall be valid from some future date.

From the discussions in mentioned issues I understand that this will not be feature in the versioning() function. Two possible workarounds come to mind:

1. disable the versioning trigger on the asset table. insert the new data with a sys_period starting in the future. enable the versioning trigger.
2. Use set_system_time to set the system to a date in the future. insert the new data. reset set_system_time.

Could you share your thoughts about which of these workarounds make sense regarding data integrity when using the versioning function?

Thank you and kind regards,
ALee

[bilalshareef]

Hello @ALee008,

Your usecase looks slightly different from the one discussed in #6.
In the other issue, they are trying to migrate some live existing data to temporal tables with creation dates retained. In your case, you are trying to insert some new data to your table which shall be valid from a future date.

If there aren't going to be any regular inserts/updates while you are inserting your future data, then I think both of your solutions shall work work without any data integrity issues.

If it is the other way where your regular inserts/updates might happen while you are inserting your future data, then you might have to look for another solution where both these types of operations go hand-in-hand.

Hope this helps.

[simoneb]

@ALee008 can you please clarify what you mean with:

But we are now challenged with inserting new assets that shall be valid from some future date.

Specifically, I'm not sure how this concept of validity applies and interacts with the purpose of this extension.

[ALee008]

Hey @bilalshareef , hey @simoneb,

thank you for your replies.

@simoneb: You are right. I come to realize, that this extension is not meant to used like this. This is a domain driven topic and should not be mixed with the technical aspect like "temporal tables".

We have to find a different solution. Junction tables perhaps.

[simoneb]

Thanks for the follow up, indeed I had suspicions that you were mixing up some domain concept with the purpose of this extension, but I wan't sure. Thanks for clarifying and taking the time to open and update this issue.