Setting up a Microsoft SQL Server Trigger Step

The Microsoft SQL Server Trigger Tool can be used to start a task in TaskCentre. In the trigger step a set of conditions are specified which, when met, trigger the start of a task.

How does this work?

Before the Microsoft SQL Trigger Tool can be used in a task the TaskCentre Microsoft SQL Trigger Agent must be installed on the machine that hosts the Business Application’s database.

When a task is created that contains an Microsoft SQL Trigger Tool step information is passed by TaskCentre to the agent. The agent inserts a SQL trigger script into the database. This trigger script is automatically maintained in the business application database.

MS-SQL-Server-Trigger-Establishing-the-Trigger

The trigger script runs with Microsoft SQL Server and detects changes in data in tables on the business application. These changes will typically have occurred based on users of the business application updating data in the business application.

MS-SQL-Server-Trigger-Event-occurs

When a change is recorded in the database, the trigger script fires and the key information about the change is passed to the agent. These changes are stored locally to the agent and the agent picks them off one at a time and fires the relevant task in TaskCentre.

How is a task step set-up?

Before the task step is configured if any key information is to be made available to other steps in the task then variables must be created to hold the results. Variables are created in the Task Browser under the environment tab by right clicking the Variables node and selecting ‘New’.

MS-SQL-Server-Trigger-New-Variable

The variable is given a recognisable name and optionally a description.

MS-SQL-Server-Trigger-Variable-General-Tab

The variable should be created with a type of ‘Variant’, ‘Parameter’ should be selected and ‘In, Out’ selected as the parameter attributes.

The Scope is normally set to ‘Task Instance’ which means that data held in the variable only exists while this task is running. If ‘Task’ is selected for the Scope then the data held in the variable is persisted across different runs of the task.

More than one variable can be defined for use in the task.

MS-SQL-Server-Trigger-Variable-Details-Tab

In the task the MS SQL Server Trigger tool is dragged from the browser onto the task planner.

MS-SQL-Server-Trigger-General-Tab

An appropriate name for the step is entered and a description can be added to describe in plain language what the step is doing.

MS-SQL-Server-Trigger-Main-Tab

The server that the agent is installed on must be selected from the list.

In order to create the trigger script on the database, credentials with the correct permissions must be provided. These can be directly entered in the Login Name and Password fields or ‘Use Trusted Connection’ can be selected. If it is selected then the currently logged in users credentials are used (provided task impersonation has not been selected on the task General tab under the options button. If impersonation is in use then the Windows user specified will be used instead).

The trigger is created on a table in SQL Server.The table must be selected from the list.

It is possible that more than one trigger has been added to the table and the ‘Trigger Order’ options allow choice of the order in which they will fire. This is not normally required.

On the Trigger tab the type and conditions of the trigger are specified.

MS-SQL-Server-Trigger-Trigger-Tab

Triggers can be fired when an update to existing data occurs, when a new row is inserted or rows deleted, or a combination of these.

Normally triggers are fired ‘After’ the update/insert/delete has occurred. Running the trigger ‘Instead of’ the update/insert/delete is usually not allowed by the business application vendor because they interfere with the logic of the application. They are usually only usable on bespoke applications. If they are used the update, insert or delete is cancelled and the trigger is run instead.

When a change is made to the data in a table more than one row in the table may be changed.Most often you will require a task to run for each row changed. In this case ‘Run Task once for each Row in Result Set’ is selected.

MS-SQL-Server-Trigger-Run-Trigger-options

Two tables of data are maintained depending on the Trigger Type.

Update and Insert Trigger Types update the Inserted Table. The two trigger types update the Inserted Table differently as follows:

Trigger TypeDeleted.ColumnNameInserted.ColumnName
UpdateValue prior to updateValue after update
InsertNo valueValue after insert

The Delete Trigger Type updates the Deleted Table. It updates the table as follows:

Trigger TypeDeleted.ColumnName
DeleteValue prior to update

If a single task run is required when multiple rows are updated then ‘Run Task once for each Trigger’ must be selected. If it is selected it is not possible for the step to output data from the trigger to variables.

Normally when ‘Run Task once for each Row in Result Set’ is selected key data from the changed table will be used in subsequent steps in the task. This is done by mapping columns in the table to TaskCentre variables.

MS-SQL-Server-Trigger-Mapping

Clicking on the ‘Columns’/’(unmapped)’ will display a drop down list of the columns available in the table. The key columns can be selected and mapped into the Task Parameter Variables previously defined at the start of this exercise.

If you want to further refine the conditions under which the trigger is fired the ‘Only Fire Trigger when a selected Column is updated / inserted’ checkbox can be selected. In which case the columns available will be displayed and can be selected.

This can be further refined by selecting the ‘Filter Table’ checkbox and pressing the ‘Filter’ button to open the Table Filter Expression dialog. This dialog allows entry of a T-SQL expression (knowledge of T-SQL is required). The columns available for inclusion in the expression are displayed in the left hand pane and can be dragged into the expression, e.g. ‘Inserted.COLUMN_3=1’ will cause the trigger to only fire when the column called COLUMN_3 in the table has a value inserted equal to 1.

The Script tab is for expert users.It allows the script that has been automatically generated to be edited by pressing the ‘Edit’ button.

MS-SQL-Server-Trigger-Script-Tab