Post processing of data saved to SQL Server
    • Dark
      Light
    • PDF

    Post processing of data saved to SQL Server

    • Dark
      Light
    • PDF

    Article Summary


    SQLSpreadsProLogo.png
    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.
    Post Processing of data 7.1

    Some details about Data Post processing
    • The Data Post processing SQL query is executed in the same transaction as the update of the data.
    • If the Data Post processing SQL query fails to execute then the updates in SQL Server will be rolled back and not saved to the database.
    • If an error occurs, you can show a warning message to the user, and let them correct the data and save it again.

    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:
    Post Processing Query Insert Parameters

    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
    
    Temporal tables in SQL Server

    Another way to create an audit log within SQL Server is to use a Temporal table, please see this article for more details about SQL Server's Temporal Tables.

    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
            
    

    Was this article helpful?