What is the Microsoft SQL Server Trigger Tool
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 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.
Microsoft SQL Server Trigger Tool Features
- 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
White Paper - Microsoft SQL Server Trigger
Microsoft SQL Server Trigger Tool 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 SQL Server
Supported SQL Server Versions
Supported SQL Server editions are listed below:
- Microsoft SQL Server 2012
Express, Standard, Web, Workgroup, Parallel Data Warehouse, Fast Track, Datacenter, Evaluation, Business Intelligence, Developer, and Enterprise editions
32- or 64-bit - 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
About the Microsoft SQL Server Trigger Agent
The Microsoft SQL Server Trigger Agent is a service that sits between BPA Platform and SQL Server. It is installed onto any SQL Server instance and is responsible for both maintaining the triggers in SQL Server and for handling the running of a task once a trigger has been fired.
When a SQL Server trigger is fired, a connection is made to the Agent service which then launches the relevant task.
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. An 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.
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.
Open the Microsoft SQL Server Trigger Configuration dialog box by either:
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.
- 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 enter the following details for the step:
- Name — Enter a meaningful name for the stepTIP: If this task instance makes use of two or more Microsoft SQL Server Trigger steps, ensure the Name used is unique for each individual step.
- Description — If required, enter a description of this 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
Selecting the Database Table to Trigger On
You can only create a trigger on a single database table; click Browse to select the required database table:
Specifying the Trigger Order
SQL Server triggers do not have an execution order; SQL Server executes triggers in a random order, sometimes depending on trigger creation date, sometimes using another valid order. Two types of triggers exist — AFTER and INSTEAD OF — for more information about these specific trigger types, see About the Trigger Tab.
If multiple tasks have AFTER triggers triggering on the same SQL Server table, you can state when this particular trigger from this task, at task run-time, is executed:
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.
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.
