Post processing - Adding a Custom Data Validation
You can always create a custom validation on the database side by adding a Check Constraint to your column in SQL Server., but there is also a possibility to add a validation inside a SQL Spreads document.
To add a custom data validation in SQL Spreads, we use a feature called Post Save SQL query. A Post Save SQL Query is an SQL statement that will be executed right after the data is written to the database (but before the update transaction is committed).
You can use that feature to do a data check, and if it is not valid, you can raise an error that is shown to the user, and the data will not be written to the database (the transaction that updates the data will be rolled back).
You’ll find the Post Save SQL query in the Document Settings > Other settings tab:
Below is an example that will check that the text entered into the Name column is not empty:
IF (SELECT COUNT([Name]) FROM [ABCFruitStore].[dbo].[FruitTrees] WHERE LEN(Name) = 0) > 0 RAISERROR('The column Name cannot be empty',16,1);
If the Name column is empty, an error is raised and the message "The column Name cannot be empty" will be shown to the user and the changes will not be written to the database.