ApexSQL Trigger trigger Template editor – Auditing customization

As explained in the previous article one of the main features of ApexSQL Trigger is the customization of templates used to generate triggers. The previous article has introduced the interface of Template editor and the template modularity. Now the focus will be on the actual trigger generating process

How does the template actually work? It is a SQL script with an additional VBScript code enclosed within “{%” and “%}” tags. When processed, the tagged code turns into SQL statements, based on selections and actions the user has made in ApexSQL Trigger’s main UI (table selection, trigger types, watches, and lookups)

Essentially, each template is a mini VBScript program that uses variables from a current ApexSQL Trigger session (databases, tables, etc.) to create SQL script as output

Take for example the following template code:

CREATE TRIGGER 
{%Audit.Print "[" 
 & objTable.Owner 
 & "].[" 
 & StartTriggerNameInsert & objTable.Name 
 & "]"%}
ON {%Audit.Print TableFullName%}

After being applied to the table named “Customers”, it becomes:

CREATE TRIGGER [dbo].[tr_U_AUDIT_Customers]
ON [dbo].[Customers]

After getting to know how trigger generation works, let’s try to modify a trigger creation template and make some customization

Template customization – The customization we will show is modifying the template so that a generated trigger fires only if a certain condition is met – for example, if an updated value is greater than specified (e.g. 100)

Since the UPDATE statement related trigger is to be modified, the code to modify is located in the UPDATE section of the template. The location where an additional condition will be added is after default specified conditions:

By inserting the following code, the trigger to be generated is instructed to check both text and numeric values, and to fire in case a new numeric value is greater than 100:

When such modified template is applied to an ApexSQL Trigger project, the Create triggers operation will display the following SQL in the Script dialog (the “Amount” is the money type column of a sample database):

Newly created triggers will fire as expected – only in case the new numeric value of an updated field is greater than 100. The INSERT statement is captured regardless of the new value as shown in this ApexSQL Trigger Standard report

Verifying generated triggersAfter applying changes to the template, the resulting code can be verified and checked for errors by executing the Parse or the Process command

The Parse will look for mismatched script tags (“{%” and “%}“) and help avoid potentially hard-to-debug errors

The Process will indicate the error in VBScript segments of the template and its exact location. It does not check SQL segments of the template for errors (outside of the script tags – “{%” and “%}“)

Summary

We have described the purpose of the Template editor, the modularity of the editor and the default template, as well as how to apply template customizations. The template itself is very complex since it utilizes both a SQL and a VBScript, it removes old triggers, generates new ones, generates Watches and Lookups. Therefore, on every manual modification of the template, it is recommended to test changes prior to usage on the production database