Follow-On Topics

Related Topics

# About the Trigger Tab

Click the image to view full size.

You use the Trigger tab to configure the trigger that activates this task:

The dialog has two tabbed panes:

About the Configure Tab

The Configure tab provides configuration options to enable King Task Centre to automatically create the necessary T-SQL Script for the trigger. The script created may be viewed by clicking on the Script tab. The Configure tab contains the following:

|  | Trigger Type — The following options are provided for selection:

|  | UPDATE — When this trigger type is selected, the trigger is fired when a column in the selected table is updated.

|  | INSERT — When this trigger type is selected, the trigger is fired when a new row is added to the table.

|  | DELETE — When this trigger type is selected, the trigger is fired when an existing row is deleted from the table.

|  | Fire Trigger — The following options are provided for selection:

|  | AFTER — This is the default trigger option which fires the trigger after the table has been updated.

|  | INSTEAD OF — When this trigger option is selected, the trigger fires and performs its task instead of the table being updated.

|  | Run Task once for each Trigger — If this option is selected then the Task is run once for each trigger created. Numerous updates can therefore be made to the table but the trigger still fires only once. No mapping of results set columns to Task Parameters is available, but any changes to the database that caused the trigger to fire may be accessed through King Task Centres memory feature.

|  | Run Task once for each Row in Results Set in — When the trigger is fired, an Inserted and a Deleted table are produced containing a record set depending on the updates made to the original table. Selecting this option enables you to select which of the tables is to be used for running the Task.

|  | Inserted Table — If this table option is selected then the Task runs once for each row created in the Inserted table when the SQL trigger was fired. Each row represents the addition of a new row in the original table, and in addition includes any updated rows if the `UPDATE` trigger type has been previously selected.

|  | Deleted Table — If this table option is selected, the task runs once for each row created in the Inserted table when the SQL trigger was fired. Each row represents the editing or deletion of an existing row in the original table.

|  | Map Columns to Task Parameters — Displays a list of variables created for the Task. The Columns column contains a drop down list of columns for selection, from the Inserted and Deleted tables created by the SQL Trigger, to be mapped against the variable. The variable may then be used to expose the selected column value to subsequent Steps. NOTE: If using SQL Server 2008 then the `datetimeoffset`, `time`, `date`, `datetime2`, and`xml` data types are also displayed for selection. Also, if the value for a bit field in the database is anything other than `0` (zero), then King Task Centre returns the value for `VARIANT_TRUE` which is `-1`.

|  | Only Fire Trigger when a selected Column is updated — Selecting this option activates the Columns table displayed below it.

|  | Columns — Displays a list of columns available in the selected database table. A check box is provided to the left of each column name for selecting / deselecting the column. The trigger is then fired when any of the selected columns is updated.

|  | Filter Table — Selecting this option activates the Filter button so that it may be selected.

|  | Filter — Clicking on the Filter button opens the Selecting the Table Filter Expression dialog to enable a new expression to be entered.

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

About the Script Tab

|  | The Script tab is provided to enable the T-SQL Script for a trigger to be manually created.

|  | When the tab is first selected it displays the T-SQL Script automatically created for the trigger through the Configure tab using the default options selected in the tab.

|  | Selecting the Edit button switches the tab to script mode so that the T-SQL Script may be manually edited.

|  | Once the script mode has been selected it cannot be reversed and the trigger may only be edited by updating the T-SQL Script.

Copyright © 2018 Quadrant Software B.V. All rights reserved.