The Microsoft SQL Server TriggerClosedThe 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.

Note: The installation cannot begin while the BPA Platform client is open if installed on the same computer. Ensure you close it before continuing.

  1. Download the BPA Platform 2020 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.

    Installation Console

    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.

  1. 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 ConfiguratorClosedYou 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..

  2. 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.
  3. 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.
  4. 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.
  5. Click Next.
  6. 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.
  7. 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.
  1. For the relevant agent, change the polling interval to suit.
  2. 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 variablesClosedVariables 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 variablesClosedVariables 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, do the following:

  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.
From the task's Design tab, right-click on empty space and select New > Event > Microsoft SQL Server Trigger.
  1. In the General tab, Name your task step.
  2. Go to the Main tab and enter the database details for the Microsoft SQL Server Trigger Agent to monitor.
  3. Go to the Trigger tab and configure the database operations that trigger this task to run.
  4. Click  to save the task step.