Using DataStar With SqlServer¶
The SqlServer scripting engine using a MERGE to apply updates and deletes. This can cause problems with referential integrity if a delete is applied to a table that is referenced by a foreign key constraint. It is possible to trigger custom scripts that get included before and after a block of components, which can disable constraints within the session and re-enable after the component scripts have been executed.. This can be achieved by adding a scripts in your templates folder that is triggered before and after for example:
You can then use the
ALTER TABLE <TableName> NOCHECK CONSTRAINT ALL; in the disable script and the
ALTER TABLE <TableName> NOCHECK WITH CHECK CHECK CONSTRAINT ALL; in the enable. These statements only apply within the transaction scope, so in the event of an error will be rolledback.
Please be aware that when using DataStar with columns that require XML substitutions (see section on Xml Lookups) the first time the script is extracted any document formatting will be removed. This may result in DataStar applying an update when the script is first run against the same database target despite there being no functional changes, this is due to the removal of the XML formatting. This only occurs once when the formatting is removed, subsequent executions would show no updates (this same behaviour applies to Oracle).