Post processing of data saved to SQL Server

Data Post Processing requires SQL Spreads Pro or higher plan

Intro

SQL Spreads can post process the data that is updated in SQL Server for purposes such as cleaning, copying, validation, etc. This is done by executing a Data Post processing script after the data has been saved to the database.

The Data Post processing script is a SQL query that will be executed as the last step in the transaction that updates the database.

To add a Data Post processing script, open Document Settings and click the Edit Post-Save SQL Query button.

How to run specific code for specific tables

To run specific code for different updates tables, there are two SQL Spreads parameters that will hold the tables affected for the current database update:

• @AffectedTables
  The parameter will contain a comma-separated list of the tables in SQL Server that are affected by the update.
  The format of the content in the parameter is:
  *[TableA],[TableB]

• @AffectedTables_WithDBAndSchema
  Will hold a comma separated list with the affected tables including the databse and schema name in the format:
  [DatabaseA].[dbo].[TableA],[DatabaseB].[dbo].[TableA]

These parameters can be easily inserted using the Insert dialog:

Example:

-- In this example we use the parameter @AffectedTables to run this code only when updates are done to the [ForecastLines] table. There is also another parameter that will also include the database and the schema: @AffectedTables_WithDBAndSchema

IF CHARINDEX('[ForecastLines]', @AffectedTables) > 0
-- Code to run when ForecastLines table is updated

Post 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