The main feature of ApexSQL Trigger is generation of triggers that capture and store data changes due to INSERT, UPDATE, and DELETE statements. This and the next article will discuss some techniques to customize trigger creation.
ApexSQL Trigger enables you to review and edit trigger creation script prior to its execution. Besides the possibility to modify the script that generates triggers, it is also possible to customize the template ApexSQL Trigger used to generate them. By customizing the template all triggers generated in the future would already have the necessary customizations thus avoiding manual modifications.
Using Template editor, new trigger templates can be created and existing templates can also be modified. Templates can be used selectively to generate triggers and the user is not limited to the use of only one template.
To access the trigger Template editor, select the Home tab on the main toolbar, and click the Edit template button:
The default template used to create a trigger generating script will be shown in the main workspace of the internal editor.
A trigger template uses the exact same paradigm as Active Server Pages as well as the same scripting language – Microsoft VBScript.
The default template consists of three sections – one section for each of audited SQL statements (INSERT, UPDATE and DELETE). Each of the three sections consists of the following subsections:
- Drop old trigger
- Generate statement auditing trigger
- Generate Watch – the Watches feature allow adding recognizable fields to any audit database change, which is helpful to those reading the reports
- Generate Lookup – the Lookups feature allows the ApexSQL Trigger report to show a meaningful value from a related database table, along with the value of the field that was audited
Before proceeding with modification of the trigger template, let’s mention that Template editor also uses includes (.inc files), which brings modularity into trigger generation by providing additional VBScript procedures and functions to shape final SQL triggers generating script
Take for example, the following VBScript function:
Function FieldNumericOrDecimal(objField)
FieldNumericOrDecimal = (Left(objField.SQL_Datatype,7) = "numeric" Or
Left(objField.SQL_Datatype,7) = "decimal")
End Function
As you can see it returns TRUE if passed parameter (objField) is Numeric or Decimal type, otherwise it returns FALSE which is something that is commonly needed during trigger generation. This and other common functions can be stored in .inc files and included in templates when needed and not copied around from template to template.
By default Template editor includes modules which support the default trigger template. However, multiple includes may be added to Template editor to expand functionality of the trigger template
Includes may be added, removed, and even edited with the internal Include editor.
After introducing the interface, Template editor, and template modularity, let’s continue with the actual production of triggers using the Template editor
April 17, 2013
