I've built an Anchor Model that contains several attributes for an anchor. Some of these attributes can contain NULL values in the source data. My perception is that Anchor Modeling does not insert NULL values in the Anchor Model tables.
The behavior I would expect from loading the latest views is that when an attribute has a NULL value the Instead Of triggers will handle the NULL value, by not inserting a record in the specific Anchor Model table. On the contrary I get a NOT NULL violation.
What is the best way to handle loading data through the Latest Views when attributes sometimes contain NULL values?
In the mean time I've created a stored procedure that dynamically builds and executes the merge statements per attribute/knot table, based on metadata of target table. I first check whether the attribute/knot key is present in the target table: when not matched insert, when matched and value changed update; or when historized insert with new ChangedAt datetime. This works quite well and supports reuasabilty and automation, next step will be creating dynamic SQL for merge statements of ties and anchors.
Re: NOT NULL violation when inserting in latest views
I see what the error is now. It's in the update trigger that the problem arises. I had this notion of letting an update to NULL translate to a logical delete in concurrent-reliance-temporal modeling. On second thought, this would probably just add confusion, so I think I can just add a not null condition in the trigger, similar to the one in the insert trigger (and leave deletes for DELETE statements).
As a workaround, it is possible to use the following approach with an isnull wrapper in the WHEN MATCHED section of your MERGE:
Can anyone think of any objections to why the update trigger should not ignore null values? Will it break anyone's code?