Microsoft SQL Server Trigger Tool

Introduction

The Microsoft SQL Server Trigger tool uses the Microsoft® SQL Server™ trigger functionality to create a trigger on a table of a specified SQL data source. When a specified data modification is attempted, such as an attempt to add or delete a table row, the trigger then causes a task to run.

The nominated data source can be local to BPA Platform or on a remote server which is either part of the same network or, if forming part of a partner-hosted instance of BPA Platform, external to it with no direct connectivity.

The trigger creates virtual tables for inserted or updated, and deleted rows which can be mapped to task variables.

If a Database Query (ODBC) or Database Query (OLEDB) step is used in the task to retrieve a recordset from the SQL data source, then the Memory feature can be used in a subsequent step to memorise all of the data into a Repository.

Features

• Monitors SQL Server data sources locally or on a remote server
• Remote Data Relay support
• A trigger is activated when any row is updated or deleted, or a new row inserted
• A trigger is activated when specified columns are updated
• Values of updated or deleted rows, or new rows in the virtual tables may be mapped to variables for use in other task steps
• Fire triggers after an event, or instead of

White Paper - Microsoft SQL Server Trigger

The Microsoft SQL Server Trigger white paper introduces the tool.

Download White Paper

Technical Summary

Working with other Tools

The Microsoft SQL Server Trigger tool is independent of any other steps used in a task. It does not directly consume objects outputted by other task steps, nor does it expose objects that can be directly consumed by other task steps. However, Microsoft SQL Server Trigger data is mapped to task variables which can be used by subsequent task steps.

Microsoft SQL Server Trigger Tool Dependencies

The following products, technologies, protocols, or systems are required by the Microsoft SQL Server Trigger tool:

• Microsoft SQL Server 2012 or higher — These versions support the use of triggers
• ODBC Compliant Data Source
• Microsoft SQL Server Trigger Agent — The agent must be installed on the server hosting the SQL Server data source that the Microsoft SQL Server Trigger tool will use.

Supported SQL Server Versions

Supported SQL Server editions are listed below:

• Microsoft SQL Server 2014
  Express, Evaluation, Standard, Web, Developer, Business Intelligence, and Enterprise editions
  32- or 64-bit
• Microsoft SQL Server 2016
  Express, Evaluation, Standard, Developer, and Enterprise
  64-bit
• Microsoft SQL Server 2017
  Express, Evaluation, Standard, Enterprise, Developer, and Web Edition
  64-bit
• Microsoft SQL Server 2019
  Express, Evaluation, Standard, Enterprise, Developer, and Web Edition
  64-bit
• Microsoft SQL Server 2022
  Express, Evaluation, Standard, Enterprise, Developer, and Web Edition
  64-bit

Note that the following SQL Server platforms are also supported:

• On-premise
• SQL Server failover cluster
• SQL Server on cloud VM (i.e. Azure)

Azure SQL (SQLaaS) and Azure SQL Managed Instances are not supported at the time of writing.

Architecture

The Microsoft SQL Server Trigger Agent is a service that sits between BPA Platform and Microsoft SQL Server. It is installed onto a supported SQL Server instance (see Supported SQL Server Versions) and is responsible for both maintaining the triggers in SQL Server and for handling the running of a task in BPA Platform once a trigger has been fired.

When a trigger is fired within SQL Server, a connection is made to the Agent service which then launches the relevant task on the BPA Platform Server.

The diagrams below provide high-level architectural overviews of the Microsoft SQL Server Trigger Agent and Microsoft SQL Server Trigger tool with BPA Platform and SQL Server in a number of commonly used deployments.Other variations may be supported. For further information, contact your Codeless Platforms account manager.

Example On-Premise Architecture

The diagram shows an overview of BPA Platform and the Microsoft SQL Server Trigger Agent and Microsoft SQL Server Trigger tool in an on-premise deployment. This includes configurations where the BPA Platform server is located in the same network or domain as the SQL Server instance, either on-premise itself or on a cloud-based virtual machine.

This example assumes that each component exists within the same network or domain.

Example On-Premise Architecture with SQL Server Failover Cluster

BPA Platform also supports the use of triggers on a SQL Server failover cluster, enabling the firing of triggered tasks from whichever server in the cluster is active at the time. A Microsoft SQL Server Trigger Agent must be installed on each node, starting with the active node. However, because all are monitoring the same SQL Server instance, the BPA Platform client sees only a single Agent registration.

Again, this example assumes that each component exists within the same network or domain.

Example Cloud Architecture

The following example shows a deployment of BPA Platform and the Microsoft SQL Server Trigger Agent and Microsoft SQL Server Trigger tool where the SQL Server instances are hosted in the cloud, for example SQL Server on an Azure Virtual Machine.

In this diagram, BPA Platform is shown as being installed in an on-premise environment however a cloud-hosted installation is also supported.

As with the previous examples, this assumes that each component exists within the same network or domain.

Example Hybrid Architecture

A hybrid deployment can be created where Microsoft SQL Server Trigger Agents can be deployed for SQL Server databases which are either located on-premise or in the cloud (except for Azure SQL (SQLaaS) and Azure SQL Managed Instance).

In this deployment type, all components are assumed to reside within the same network or domain.

Note that BPA Platform can additionally be installed on a cloud-based virtual machine within that same domain.

Example Partner-Hosted Cloud BPA Platform with On-Premise SQL Server Architecture

BPA Platform 2023 and higher supports the use of SQL Server Triggers from a cloud partner-hosted installation of BPA Platform to individual on-premise SQL Server instances where there is no direct network connectivity available.

The following diagram describes the architecture between three BPA Platform Server instances hosted in the cloud and three distinct SQL Server instances on individual remote server sites potentially for different customers. This can be scaled as required.

Where BPA Platform is hosted in the cloud by a partner for example, and SQL Server exists on-premise in a customer’s external network, connectivity can be established using our Remote Data Relay. This enables secure communication between the customer’s BPA Platform Server instance and their on-premise SQL Server instance.

The Remote Data Relay can be most effectively used in environments where multiple cloud-hosted BPA Platform Server instances reside on a single Windows Server and connectivity is required to one or more customer data sources located in one or more external networks.

The Microsoft SQL Server Trigger Agent must be installed on the remote Server hosting the SQL Server data source. The Remote Data Relay is then configured to create the connection between that data source and the individual hosted BPA Platform Server instance.

Microsoft SQL Server Trigger Agent Configuration

The Microsoft SQL Server Trigger Agent must be installed on the server hosting the required SQL Server data source using the BPA Platform Installation Console. It can be installed alongside other BPA Platform components or as a standalone installation on a remote server.

The configuration of the Microsoft SQL Server Trigger Agent takes place in the product Configurator.

You open this part of the interface from the Event Agents node – expand Event Agents > Microsoft SQL Server Trigger in the items list.

When Agent configurations are added, they will appear under this node.

About the Prerequisites Node

The Component Prerequisites must be met for any further configuration of the Agent to take place. Where the expected requirements are detected, the status of each prerequisite is denoted by a green tick, else a red cross is shown.

SQL Server Editions shows whether a supported version of SQL Server is detected on the machine hosting the Microsoft SQL Server Trigger Agent.

The SQL Server Browser service is part of the SQL Server installation. This must be installed and its service running on the default port 1434.

The Refresh button can be used at any time to update the current status of either prerequisite.

About the Configuration Node

A new SQL Server Instance Trigger Configuration is created in the Configuration node.

The Agent Instance Name is used to identify the Agent Instance from other instances registered with the BPA Platform Server instance if applicable. The default value can be left unchanged, or you can enter a custom name if required.

Configurator detects the SQL Server name automatically, displaying the device name of the host machine on which the Microsoft SQL Server Trigger Agent is installed.

Select the SQL Server Instance on which the Trigger Agent is to be registered. If multiple instances are detected these are shown here.

Save the SQL Server Instance Trigger Configuration settings to progress to the creation of the Agent Instance node where the registration of the agent instance is completed.

About the Agent Instance Prerequisites Node

The Component Prerequisites in this node must be met for any further configuration of the Agent to take place. Where the expected requirements are detected, the status of each prerequisite is denoted by a green tick, else a red cross is shown.

If all prerequisites are met and a Windows Failover SQL Server cluster is not detected, navigation to this node is skipped and you are taken directly to the Registration Settings section.

Agent Instance Prerequisites Node – Standard Configuration

Agent Instance Prerequisites Node – Failover SQL Server cluster detected

SQL Server Instance Connectable

The Configurator must be able to connect to the SQL Server instance. If the SQL Instance Connectable appears as not met, ensure that the credentials entered in the previous step are correct and that all services are running.

SQL Server OLE Automation

The SQL Server OLE Automation feature is part of the SQL Server deployment and must be enabled for Agent registration to take place. You can select Modify to enable OLE Automation if this prerequisite is detected as not met and OLE Automation is not enabled. The option to disable OLE Automation is also located here.

Agent Permissions (present after configuration)

The Agent Permissions prerequisite checks that the SQL Server Trigger Agent is accessible from the local supported edition of SQL Server. Select Modify to restart the Agent service if this prerequisite is not met or changes have been applied to the SQL Server instance. This is only present for a standard SQL Server Trigger Agent deployment.

Domain Administrator (SQL Server cluster configuration only)

The Configurator uses the logged in Windows user to modify failover cluster settings, therefore the user must have the correct domain privileges.

SQL Server Failover Group (SQL Server cluster configuration only)

The SQL Server Failover Group ensures the accessibility of the clustered SQL Server instance and verifies that the resource is owned by the node performing the initial configuration for the clustered SQL Server Trigger Agent. If the SQL Server resource role is not owned by the server node undergoing the Agent configuration, this prerequisite
will be marked with a red cross.

Cluster Service (SQL Server cluster configuration only)

The Cluster Service must be running as well as the node being set as Active in Window Failover Cluster Manager.

About the Agent Instance Service Settings Node (SQL Server cluster configuration only – Active and Failover nodes)

Windows domain credentials are required for running the SQL Server Trigger Agent service. This manages the BPA Platform Agent resource inside the cluster and establishes connectivity between it and the targeted BPA Platform Server when a resource failover is carried out.

You must ensure the following user prerequisites are met before configuring the Service Settings.

• Cluster SQL Server instance database engine connectivity.
• Read/Write access to the SQL Server databases that the SQL Server Trigger Agent needs to monitor.
• Log on as service right is granted on all SQL Server cluster nodes.

About the Agent Instance Settings Storage Settings Node (SQL Server cluster configuration only – Active node)

The Storage Settings node lists all available cluster volumes (excluding CSVs) on the cluster. The SQL Server Trigger Agent requires storage placement on a SQL-defined volume for the correct storage of trigger queue data. Installing the Agent on a non-SQL-defined volume will result in an error.

For easy configuration, we recommend using the Use SQL Server Instance Volume checkbox, providing the SQL Server configuration and data is contained on a single cluster volume.

About the Agent Instance Registration Settings Node

Irrespective of the type of SQL Server Trigger Agent configuration, you must complete the Registration Settings.

This registers the Trigger Agent to the server instance of BPA Platform on which tasks are to be triggered.

Enter the Server address of the machine hosting BPA Platform. This can be local to the SQL Server Trigger Agent installation or on a remote server.

By default, the Configurator assumes the local computer is also the BPA Platform Server. Click Save to complete the configuration if this is the case.

If this is a remote agent and the BPA Platform server instance against which it will be registered exists on a different machine within the same network or domain, enable Change Settings.

Enter the Server hostname where the relevant BPA Platform Server instance is installed.

The Agent Name value should be left unchanged.

If the BPA Platform Server instance against which the Trigger Agent will be registered is on a cloud-based server where no direct network or domain connectivity is readily available, you must use our Remote Data Relay.

Selection of Remote Data Relay enables the Open Relay Setup option. Click this to open the Remote Data Relay Setup dialog.

Configuration of the Remote Data Relay for a Microsoft SQL Server Trigger Agent connection is described within the product help.

The final step of the Agent configuration is to select Save. This will open the Authentication dialog.

Enter the credentials of the BPA Platform Administrator account for the server instance where the Agent is to be registered. The Administrator user must not be logged into another instance of the BPA Platform client elsewhere when trying to save the credentials.

Once the agent is registered, it appears as such in the left-hand tree structure under Microsoft SQL Server Trigger Agent > Configuration.

If additional Agent instances have been configured, these additionally appear. Selection of the Agent Instance node displays a summary of that Agent’s individual configuration.

Registered Agents

You can view all agents including Microsoft SQL Server Trigger Agents registered to a particular BPA Platform Server instance in the Client resources tree by going to System > Agents.

Each Agent is listed and displays its Agent Instance name and the computer on which it resides.

If your Agent is registered to a SQL Server cluster, you will notice the associated computer name is neither of the cluster nodes; instead it is the fully qualified domain name (FQDN) of the cluster resource group and the SQL Server instance.

Global Configuration

The Microsoft SQL Server Trigger Agent constantly polls its monitored database for any changes. In high-traffic databases where inserts, updates, and deletes happen frequently, this may not be suitable. You can adjust the polling interval here.

You open this window from the resources tree — expand System > Tools > Event and double-click Microsoft SQL Server Trigger in the items list.

From here, change the following:

• MS SQL Agent — A list of all agents that are registered with the BPA Platform server
• Agent Synch Polling Interval — The number of seconds for how frequently the agent checks the monitored database for changes

Step Configuration

The Microsoft SQL Server Trigger step triggers a task to run when a monitored table in an SQL database is updated — whether new, updated, or deleted records.

To add a new Microsoft SQL Server Trigger step to an existing task, you either:

• Click and drag the Microsoft SQL Server Trigger icon from the Task Browser to the task Design area.

– OR –

• From the task’s Design tab, right-click on empty space and select New > Event > Microsoft SQL Server Trigger.

For a detailed description of how to create new tasks, refer to the product help.

About the General Tab

The General tab is used to Name and describe (Description) this task step.

About the Main Tab

You use the Main tab to configure the database connection for the trigger that activates this task.

Selecting the SQL Server Instance to Trigger On

You must choose the SQL Server instance and Microsoft SQL Server Trigger Agent to trigger this task:

• Server — Select the server hosting the instance of SQL Server:
• Select Server — Contains those SQL Servers that have the Microsoft SQL Server Trigger Agent installed
• Select Server Instance — Contains SQL Server instances if more than one is installed on the server
• Details — Details for the connected SQL Server
• Credentials — Add the relevant credentials for the SQL Server instance (Login Name and Password)
• To use the currently logged in Windows user’s credentials, enable Use Trusted Connection
• Task impersonation is also supported when you enable Use Trusted Connection
NOTE: If you are using the Remote Data Relay, you must configure your Microsoft SQL Server Trigger tool step using the BPA Platform Client on the same machine as the SQL Server instance you are monitoring. This is because the required permissions for the step cannot be ascertained using the Remote Data Relay. This affects step configuration only.

You use the Main tab to configure the database connection for the trigger that activates this task.

Selecting the SQL Server Instance to Trigger On

You must choose the SQL Server instance and Microsoft SQL Server Trigger Agent to trigger this task:



About the Trigger Tab

You use the Trigger tab to configure the trigger that activates this task. You can choose to use the provided options to create the trigger or manually write the trigger script.

Configuring the Trigger



Configure your trigger by selecting the following:

• Trigger Type — You can create triggers for events that occur in the nominated database table:
  • UPDATE — Trigger the task to run when a specified column is updated in the database
  • INSERT — Trigger the task to run when a new record is added to the database table
  • DELETE — Trigger the task to run when an existing record is deleted from the database table
• Fire Trigger — You can choose when this trigger is fired when combined with the Trigger Type and the Trigger Order (see Specifying the Trigger Order):
  • AFTER — “After” triggers wait for the event (update, insert, delete) to finish before processing the trigger
  • INSTEAD OF — “Instead of” triggers cancels the event (update, insert, delete) in favour of processing
    the trigger

If you are creating a Trigger Type of UPDATE or INSERT, you can also Only Fire Trigger when a selected Column is updated / inserted. To do so, enable this parameter and select the relevant column(s) from the table underneath.

Running Triggers and Tasks

A monitored event may result in multiple records being updated, inserted, or deleted, for example, importing a CSV file containing new customer records. For such cases, you can choose how often the task is triggered to run:

• Run Task once for each Trigger — The task is run once for each Trigger Type fired, that is, numerous updates, inserts, or deletes can be made to the table but the trigger only fires once.
• Run Task once for each Row in Results Set in — When the specified trigger is witnessed, each affected record is written to an Inserted Table (UPDATE and INSERT trigger types) or Deleted Table (DELETE only), and the task fired once.

  For either of the above tables, you can then Map Columns to Task Parameters for use in later task steps. All available task variables are displayed automatically in the table. For those task variables required by this task step, map them to the relevant the database Columns. Leave those task variables that are not required by this task step as (unmapped).

Adding a Filter Expression

You can further narrow down the event that triggers this task by adding a Filter. Enable Filter Table and click Filter:



Drag-and-drop any Column to the Logical T-SQL Expression area to build your expression.

NOTE: At the time of writing, the use of wildcards in the filter expression is not supported. Nor can you make use of task or global variables here.

For example, in a Sales Orders database, a report needs generating every time an order over a set value is received. The following expression is therefore created for the trigger Inserted.ITEMS_NET > = 5000.00 to cover occurrences where the value is equal to or exceeds £5000.00.

Returning the Row Count

SET NOCOUNT ON determines whether the row count is returned in the recordset. If enabled, SET NOCOUNT ON is included in the trigger’s T-SQL script (go to the Script tab; it should be before the DECLARE statements). By default, the statement is not included in the trigger script (effectively acting as SET NOCOUNT OFF) therefore the row count is not included in the recordset.

Scripting the Trigger

If required, you can use the Script tab to either manually create the trigger or edit the trigger created in the Configure tab.



Click Edit to write or edit the T-SQL script manually.

NOTE: If a trigger has either been created or edited in the Script tab, you must continue to make changes in the Script tab. You can no longer use the Configure tab to adjust this task step.