Post-Save Data Processing Scripts

Introduction

SQL Spreads can post process the data that is updated in SQL Server for purposes such as cleaning, copying, validation, etc. The Data Post processing script is a SQL query that will be executed as the last step in the transaction that updates the database.

Post-Save processing scripts function similarly to the feature previously available
through the Document Settings dialog.

In this new version, you also have the flexibility to specify that the scripts should
only be executed for certain tables.

How to add a Post-Save script

These are the steps you need to follow to add a pre-load script into your SQL Spreads solution:

1. Click the Data Processing button in the SQL Spreads tab.
   

2. Enter a name for your new script.
   

3. Set the timing for the script execution to "After the data is saved to SQL Server."
   

4. Enter your SQL query.
   

5. Click OK.

Post-Save data processing examples

Example 1: 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.

There is also a possibility to add a validation inside a SQL Spreads document using the Data Post processing feature. 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).

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. The user can now correct the error and save the changes again.

Example 2: Creating an Audit log

This example shows how to use the Data Post processing feature to create an audit log for our [SQLSpreadsDemo].[dbo].[Forecast] table in SQL Server.

We first create a secondary audit table called Forecast_audit where all the changes will be logged.

Then, to be able to fetch the updated rows, we use the change tracking feature in SQL Spreads. When rows are updated or inserted, the last saved time is the same for all rows in the same update. To fetch the last updated or inserted rows, we find the datetime when the last update was done by the current user.

The updated rows will be fetched based on the timestamp and user and then inserted into the audit table. We also add one extra column that tells if this is an update or a new row that is inserted.

This is the Data Post processing script used for the audit of the changes in the SQL Server table:

-- Get time when last update was done by this user
DECLARE @LastModifiedOn DATETIME2(7)
DECLARE @CreatedOn DATETIME2(7)
SELECT TOP 1  @LastModifiedOn = [ModifiedOn], @CreatedOn =[CreatedOn] FROM [SQLSpreadsDemo].[dbo].[Forecast] WHERE [ModifiedBy] = @WindowsUser ORDER BY [ModifiedOn] DESC

-- Insert updated or inserted rows into audit table
INSERT INTO [SQLSpreadsDemo].[dbo].[Forecast_audit]
SELECT
      [YearMonth]
      ,[AccountNumber]
      ,[M1]
      ,[M2]
      ,[M3]
      ,[M4]
      ,[M5]
      ,[M6]
      ,[M7]
      ,[M8]
      ,[M9]
      ,[M10]
      ,[M11]
      ,[M12]
      ,[Comment]
      ,[ModifiedBy]
      ,[ModifiedOn]
      ,[CreatedBy]
      ,[CreatedOn]
      ,[UpdateType] = CASE WHEN @CreatedOn = @LastModifiedOn THEN 'INSERT' ELSE 'UPDATE' END
FROM 
	[SQLSpreadsDemo].[dbo].[Forecast]
WHERE  
	[ModifiedBy] = @WindowsUser AND
	[ModifiedOn] = @LastModifiedOn

Example 3: Update a secondary table or column

This example shows how to use the post processing feature to update the value in another column in the database, based on one of the updated column's values.

In this example, the user uses SQL Spreads to update the Issues table in SQL Server and sets the Status field using a Drop-down list. When the status is updated, we also want to update an IsCritical flag in another column in the table, based on the selected status.

The available statuses are stored in the table called [Status] together with a flag that tells if the status is critical. To update the IsCritical flag in the Issues table, we join the Status table to the Issues table.

We use the same technique here to fetch the updated rows as in the Audit example above. When rows are updated or inserted, the last saved time is the same for all rows in the same update. To fetch the last updated or inserted rows, we find the datetime when the last update was done by the current user.

-- Get time when last update was done by this user
DECLARE @LastModifiedOn DATETIME2
SELECT TOP 1  @LastModifiedOn = [LastChangedOn] FROM [SQLSpreadsDemo].[dbo].[TableA] WHERE [LastChangedBy] = @WindowsUser ORDER BY [LastChangedOn] DESC

-- Update the IsCritical flag depented on the selected status
UPDATE 
		[SQLSpreadsDemo].[dbo].[Issues]
SET 
		[IsCritical] = status.[IsCritical]
FROM                                                  -- We join the Issues table with the Status table to StatusID to get the IsCritical flag
		[SQLSpreadsDemo].[dbo].[Issues] AS issue
INNER JOIN
		[SQLSpreadsDemo].[dbo].[Status] status           
		ON status.Id = issue.StatusID
WHERE  
		A.[LastChangedBy] = @WindowsUser AND
		A.[LastChangedOn] = @LastModifiedOn