Using stored procedures in Process Automation

Stored procedures are a set of SQL statements that are stored and executed on the database server. In the context of iMIS, stored procedures (executed by Process automation triggers) update the underlying tables whenever a trigger is executed.

Important! Custom stored procedures are only supported for self-hosted clients.

For example, you want to create a Trigger that updates a table every time a contact’s Paid Through date is updated. In this case, the stored procedure is triggered based on an updated database property.

The workflow for using stored procedures is as follows:

  1. Setting up the Trigger
  2. Creating the query and filters
  3. Defining the Conditions
  4. Defining the Actions

Setting up the Trigger

Do the following to create the trigger:

  1. Go to RiSE > Process automation.
  2. Select Add new task.
  3. Enter a Name and optional Description.
  4. Select Add trigger:
    1. From the Type field, choose On database change.
    2. Select Name from the Table drop-down.
    3. From the Trigger on field, enable Row updated and choose Specific column.
    4. From the Column drop-down, select PAID_THRU.
    5. Click Save.

      6. The window displays a message stating the TriggerID property is the ID. This property can now be used as a data source in the Actions and Conditions of the scheduled task.

  5. Click Save & Exit.

Creating the required query and filter

The query is required to have the following configuration:

  • The TriggerID property displayed next to the Table drop-down added as a filter.
  • The filter value equals "@TriggerID".

    • Important! The @TriggerID must be enclosed in double quotes: "@TriggerID"

  • The In search? field set to No in the query filter.

Without these settings, the trigger will not properly execute.

Setting up a trigger

Do the following to create the source query and filter:

  1. Go to RiSE > Intelligent Query Architect > New > Query.
  2. (Summary tab) Enter a Name.
  3. (Sources tab) Add the NetContactData source.
  4. (Filters tab) Add the following filters:
    1. [NetContactData] Id
      • Function - None
      • Comparison - Equal
      • Value - Constant "@TriggerID"
    2. [NetContactData] Paid Through
      • Function - None
      • Comparison - Equal
      • Value - Dynamic Current Date

  5. Click Save As to save the query to a shared folder.

Now the query can be used as a Condition in the task to further filter the results. If a query is used in the Conditions of a task, the Actions of the task are only performed if the query returns at least one row.

For example, the Conditions can be used to ensure the Actions do not run if the Paid Through date is in the future by making sure the query only returns rows with a Paid Through date in the past.

Additional notes about using the "@TriggerID" in queries

If the table has more than one Trigger property, add multiple filters in the form of:

  • "@TriggerId1"
  • "@TriggerId2"

For example, Order_Lines has a composite Trigger property of ORDER_NUMBER and LINE_NUMBER.

Editing the trigger

Reference the trigger properties in the query by adding two filters:

  • [CsOrderLines] Order Number = "@TriggerId1"
  • [CsOrderLines] Line Number = "@TriggerId2"

Defining the Conditions of the scheduled task

Conditions determine whether the task will be executed or not. Do the following to define the Condition:

  1. Go to RiSE > Process automation.
  2. Open the scheduled task for edit.
  3. From the Conditions tab, select Add data source:
    1. Enter a Data source name.

      2. Note: Spaces are not allowed in the Data source name field.

    2. Select the query.
    3. Click Save.

  4. Click Save.

Defining the Actions of the scheduled task

The actions are where you define what you want to happen. In this case, the Action must be set to Run a stored procedure. If there is a Trigger defined that has a Trigger ID, the Action will display the Enable TriggerIDs as parameters checkbox.

Do the following to define the Action:

  1. Go to RiSE > Process automation.
  2. Open the scheduled task for edit.
  3. From the Actions tab, select Add action:
    1. From the Type drop-down, choose Run a stored procedure.
    2. Enter a Stored procedure name.
    3. Enable the Enable TriggerIDs as parameters option.
    4. Click Save.

  4. Be sure to enable the scheduled task.

  5. Click Save & Exit.

Now, anytime the Paid Through date is updated for a contact, the stored procedure updates the associated table.

What's next?