The Microsoft SQL Server Trigger 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. step triggers a task to run when a monitored table in an SQL database is updated — whether new, updated, or deleted records.

Prerequisites

Before adding a Microsoft SQL Server Trigger task step, ensure you have met the following:

Microsoft SQL Server Trigger Agent

You must install the Microsoft SQL Server Trigger Agent on the SQL Server instance you are monitoring.

As each SQL Server instance can contain multiple databases, you must point the agent at the correct database. It must also be registered to the BPA Platform client processes its tasks.

1. Download the BPA Platform 2024 installation from our Partner Area.
2. Unzip the file to your preferred location on the BPA Platform computer.
3. Run self-setup.exe to launch the Installation Console.

   As part of the initial launch process, required installation files are extracted to C:\Windows\Installer. You must ensure this folder is not deleted as the Installation Console requires these files for any further installations, upgrades, or component un-installs.

   

   Click the image to view full size.

4. (Optional) By default, all files are installed to C:\Program Files (x86)\Codeless Platforms\BPA Platform. To change the destination folder, click Modify and select the relevant folder.

   If you are installing onto the same computer hosting the BPA Platform server, you cannot change the default folder paths so this can be skipped.

5. (Optional) By default, the Installation Console uses C:\iwtemp as a temporary folder during installation. To change the temporary folder, click Modify and select the relevant folder.

   If you are installing onto the same computer hosting the BPA Platform server, you cannot change the default folder paths so this can be skipped.

6. Click Install for the Microsoft SQL Server Trigger Agent feature.
7. If you are installing onto the same computer hosting the BPA Platform server, this step is skipped.

   Enter your BPA Platform product key and click Next.

   Note that the key is case-sensitive.

   If you do not have a product key, contact your account manager or business partner.

8. Click Yes to start the installation.

   Progress is displayed at the bottom of the console.

9. When complete, you can choose whether to immediately configure the Agent or do this at a later stage. It is recommended you configure this now as you cannot create tasks using the Microsoft SQL Server Trigger tool until then.

   Click Launch to start the Configurator You use the Configurator to install third-party software that may be required by available tools. Connection to required agents are also configured here. Note that you can only launch the Configurator if logged into the machine with Administrator rights..

10. If any of the Microsoft SQL Server Trigger Agent prerequisites are not met, the Configurator opens to Event Agents > Microsoft SQL Server Trigger Agent > Prerequisites so these can be resolved first.
    1. The Configurator checks for software which the Microsoft SQL Server Trigger Agent requires. If any are missing or not configured properly, a is displayed. Click Install or Modify as appropriate to resolve the problem.
    2. Click Next to move to the Event Agents > Microsoft SQL Server Trigger Agent > Configuration node.
11. If all prerequisites are met, the Configurator opens to Event Agents > Microsoft SQL Server Trigger Agent > Configuration.

    Add the server and database details that this agent is monitoring. You can also have additional Microsoft SQL Server Trigger Agents that monitor separate database instances on the same computer:

    1. Ensure Add a new SQL Server Agent Instance is enabled.
    2. If required, provide a new unique Agent Instance Name. Although the database name is appended to the Agent Instance Name, it is useful from a task builder's point of view to have unique names.
    3. By default, the Configurator assumes the local computer where the agent is installed hosts the database to monitor. If not, enter the hostname for the computer hosting the SQL Server databases.
    4. Select the SQL Server Instance for this agent instance to monitor.
    5. Click Save.
12. The Configurator also checks for software which the Microsoft SQL Server Trigger Agent requires. If any are missing or not configured properly, a is displayed. Click Install or Modify as appropriate to resolve the problem.
13. Click Next.

14. You must register the Microsoft SQL Server Trigger Agent with the BPA Platform server.

    By default, the Configurator assumes the local computer is also the BPA Platform server. Click Save to complete the configuration.

    If this is a remote agent, do the following:

    1. Enable Change Settings.
    2. Enter the Server hostname where the relevant BPA Platform client is installed
    3. If required, provide a new unique Agent Name. Although the computer name is appended to the Agent Name, it is useful from a task builder's point of view to have unique names.
    4. Click Save.
15. Enter the Administrator's password to confirm the Agent registration.

No further configuration is required for the Agent in the Configurator so you can close it down.

(Optional) Setting the Polling Interval for the Microsoft SQL Server Trigger Agent

If the monitored database experiences a high volume of inserts, updates, and deletes, you can just how often the Microsoft SQL Server Trigger Agents polls the database for changes — the default is to constantly check. To adjust the polling interval, do the following:

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

2. For the relevant agent, change the polling interval to suit.
3. Click OK to save the changes.

Microsoft SQL Server User

The Microsoft SQL Server Trigger tool requires its own credentials to create triggers on tables. If using a non-server administrator user account, you must grant the following permissions to allow access to the database and be able to create triggers:

1. To browse the server, the user account must be granted the VIEW ANY DEFINITION permission.

   Use the following SQL script to grant VIEW ANY DEFINITION:

   USE [master]
GRANT VIEW ANY DEFINITION TO user]

2. To view a list of databases, there must be a database user created and mapped to the non-server administrator account for each database to be viewed.

   To create and map database users, either use the following SQL:

   USE [databasename]
CREATE USER [username] FOR LOGIN [username]
GO

   Or do the following in SQL Server Management Studio (tree-browser view):

   1. Servername > Security > Logins.
   2. Double-click the login.
   3. Choose the User Mapping page; select Map for all databases the logged in user needs to see.
3. To view tables in a database, the user account must be granted the SELECT permission for the schema that owns the table.
4. To create the trigger on the table, the user account must be granted the ALTER permission for the schema that owns the table.

   Use the following SQL script to grant SELECT and ALTER permissions for a user account on a schema:

   USE [databasename]
GRANT SELECT, ALTER ON SCHEMA::[schemaname] TO [user]

   Or do the following in SQL Server Management Studio (tree-browser view):

   1. Servername > Databases > Databasename > Security > Schemas.
   2. Double-click the schema.
   3. Select the Permissions page.
   4. Click Search and add the user to the Users or roles list.
   5. Select Grant for the Alter and Select permissions.

Creating a Task Variable to Track the Database Event

BPA Platform variables are a means of passing information between task steps, or even tasks themselves.

The Microsoft SQL Server Trigger tool uses variables to capture and store the modified data at the time the trigger fires. Both task and global variables Variables are created and used extensively throughout and can either have fixed values or values that are dynamically populated when a task is run. Global variables are available for all tasks in this BPA Platform installation. Once created, global variables can be used by any task. are suitable for this.

Although you can create task variables Variables are created and used extensively throughout and can either have fixed values or values that are dynamically populated when a task is run. Task variables are only available for the specific task they are created for. Users with Task Administrator rights can create task variables. while in the Task Designer, it is recommended you create any global variables now.

Where Should I Use a Microsoft SQL Server Trigger Step?

The Microsoft SQL Server Trigger tool is used to start a task based on an event occurring in a specified database table, such as, insert, updated, and delete.

You would then follow the step with a tool that can manipulate or extract the data for use later in the task.

For a detailed list of those tools the Microsoft SQL Server Trigger tool can interact with, go to Working with Other Tools.

Adding a New Task Step

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

1. Open the relevant task (if creating a new task, see How to Create a Basic Task).
2. 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.

3. In the General tab, Name your task step.
4. Go to the Main tab and enter the database details for the Microsoft SQL Server Trigger Agent to monitor.
   How Do I Do This?

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

   

   Click the image to view full size.

   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

   You can only create a trigger on a single database table; click Browse to select the required database table:

   

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

   ►

   First

   ►

   Last

   ►

   No order specified

   For more information, go to the main Main tab topic.

5. Go to the Trigger tab and configure the database operations that trigger this task to run.
   How Do I Do This?

   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.

   

   Click the image to view full size.

   ►

   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:

   ■

   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. To do so, enable this parameter and select the relevant column(s) from the table underneath.

   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).

   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.

   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.

   For more information, including how to use the Script tab, go to the main Trigger tab topic.

6. Click  to save the task step.