This post is in response to a discussion on the Taskcentre LinkedIn Group. In the discussion, I’ve suggested using a “Helper” task in order to limit the number of triggers on a database, improving performance on the SQL Database.

SQL Trigger
First to define what a SQL Trigger is. There’s a lot of information on MSDN regarding SQL Triggers. To shorten this down, basically it’s a Stored Procedure which runs every time an event happens on the table (such as INSERT/UPDATE/DELETE).

Performance Issues
Single triggers work well, but as you create more and more triggers, this can impact performance. Lets take for example, the Sales Header table on Microsoft Dynamics NAV. If you are unsure of the data structure, this table is used to store the following unposted sales documents:

• Quote
• Order
• Invoice
• Credit Memo
• Blanket Order
• Return Order

Imagine you had some logic in a taskcentre task which ran for each document type. This could mean that you have 6 triggers which run for every update!

Improving Performance
In order to improve performance, I make a “Helper” task which runs once. Then taskcentre uses the logic to decide which tasks to run. This moves the overhead from SQL (which you want to be nice and fast) and taskcentre can chug away doing all the complex work.

As you can see from the SQL Trigger, this trigger will run the Taskcentre Task when the status changes from “Open” to “Released”. I’ve also filtered the document type to Quote/Order/Return Order too in order to prevent the taskcentre task running too often. In theory you don’t need these filters and you could handle these in taskcentre, but it depends on the business logic which you are trying to implement.

It’s worth noting that the trigger will still run for every single update. It just wont start the taskcentre task unless it matches the criteria.

Decisions in Taskcentre
As you could see from the trigger, the variable “DocumentType” is set to match the Document Type in the updated record. Using the decision step as shown, the next series of tasks to run can be selected. In the example to the left, I’ve chosen the Document Type of 0 which is “Quote” (Dynamics Nav stores all “Option” data types as integers).

Below shows the completed task. Each step calls a VB Script task which in turn calls another task. (I’ll write about that at some time in the future)