Trigger Scripts

Trigger scripts are attached to a database table or query. Trigger scripts are different from "transformation scripts", which are attached to an assay design and are intended for transformation/validation of incoming assay data.

Trigger scripts can be configured to run on a per-row basis whenever there is a insert/update/delete event on the table (with a few exceptions). They are called on a per-HTTP request basis (or in other contexts like ETLs), either before or after the request is executed. Typical uses for trigger scripts are (1) to alter incoming data or (2) to set off cascading changes in other tables.

Topics:

• Trigger Script Location
• Order of Execution
• Shared Scripts / Libraries
• Script Execution
• Functions
• Parameters and Return Values
• Sample Script #1
• Sample Script #2

Trigger Script Location

The trigger script attached to a particular table needs be placed in the folder associated with the table's schema. The script must be named after its associated table or query. For example, a QUERY_NAME.js script would be placed in:

Lists:
    MODULE_NAME/queries/lists/QUERY_NAME.js 
Data Classes:
    MODULE_NAME/queries/exp.data/QUERY_NAME.js
Study Datasets:
    MODULE_NAME/queries/study/QUERY_NAME.js  
Custom Schemas:
    MODULE_NAME/queries/SCHEMA_NAME/QUERY_NAME.js

where MODULE_NAME, SCHEMA_NAME and QUERY_NAME are the names of the module, schema and query associated with the table. If you are building a java module or deploying your module from source, place the scripts within the /resources/ subdirectory of the module tree.

Order of Execution

When multiple trigger scripts are defined in different modules for the same table/dataset, they will be executed in reverse module dependency order. For example, assume module A has a dependency on module B and both modules have trigger scripts defined for myTable. When a row is inserted into myTable, module A's trigger script will fire first, and then module B's trigger script will fire.

Shared Scripts / Libraries

Trigger scripts can pull in functionality from other shared libraries.

Shared libraries should be located in a LabKey module in the following directory:

MODULE_NAME/scripts/MODULE_NAME/SCRIPT_FILE.js

where MODULE_NAME is the name of the module and SCRIPT_FILE is the name of the js file. The second occurrence of MODULE_NAME is recommended to avoid namespacing collisions.

In the example below, the 'hiddenVar' and 'hiddenFunc' are private to the shared script, but 'sampleFunc' and 'sampleVar' are exported symbols that can be used by other scripts.

shared.js (located at: myModule/resources/scripts/myModule/shared.js)

var sampleVar = "value";
function sampleFunc(arg)
{
    return arg;
}

var hiddenVar = "hidden";
function hiddenFunc(arg)
{
    throw new Error("Function shouldn't be exposed");
}

exports.sampleFunc = sampleFunc;
exports.sampleVar = sampleVar;

To use a shared library from an trigger script, refer to the shared script with the "require()" function. In the example below, 'require("myModule/shared")' pulls in the shared.js script defined above.

myQuery.js (located at: myModule/resources/queries/someSchema/myQuery.js)

var shared = require("myModule/shared");
   
function init() {
    shared.sampleFunc("hello");
}

You can also pull in functionality from the following LabKey JavaScript libraries:

• LABKEY.ActionURL
• LABKEY.Ajax
• LABKEY.Filter
• LABKEY.Message
• LABKEY.Query
• LABKEY.Security
• LABKEY.SecurityPolicy
• LABKEY.Utils (only non-browser related functions)

To send an email:

var LABKEY = require("labkey");

function sendEmail()
{
    var userEmail = "messagetest@validation.test";
    // need a user to send email to/from
    LABKEY.Security.createNewUser({
        email: userEmail,
        sendEmail: false,
        containerPath: "/Shared/_junit"
    });

    var msg = LABKEY.Message.createMsgContent(LABKEY.Message.msgType.plain, "Hello World");
    var recipient = LABKEY.Message.createRecipient(LABKEY.Message.recipientType.to, userEmail);
    var response = LABKEY.Message.sendMessage({
        msgFrom:userEmail,
        msgRecipients:[recipient],
        msgContent:[msg]
    });
}

Note: The server-side Ajax methods are synchronous. These methods return immediately and the success/failure callbacks aren't necessary. The returned object is the same as the first parameter to either the success or the failure callbacks. To determine if the method call was successful, check the returned object for an 'exception' property.

Console API

A console API is provided for debugging purposes. Access using "require('console')":

var console = require("console");
console.log("** evaluating shared.js script");

The require() Function

The parameter to require() is a CommonJS module identifier (not to be confused with a LabKey module) without the ".js" extension. The path is absolute unless it starts with a "./" or "../" in which case it is relative. Relative CommonJS module identifiers can't be used by trigger scripts, but they can be used by other shared server-side scripts in the "scripts" directory.

Script Execution

The script will be evaluated once per request. In other words, any state you collect will disappear after the request is complete.

If your script runs for more than 60 seconds, the script will be terminated with an error indicating that it timed out.

Functions

• init(event, errors)
• complete(event, errors)
• The init and complete functions are called once before or after insert/update/delete for a set of rows.
• The event parameter is one of "insert", "update" or "delete".
• The errors object for the init and complete functions is an array of error objects.
• beforeInsert(row, errors)
• beforeUpdate(row, oldRow, errors)
• beforeDelete(row, errors)
• afterInsert(row, errors)
• afterUpdate(row, oldRow, errors)
• afterDelete(row, errors)
• Use these functions to transform and/or validate data at the row or field level before or after insert/update/delete.

Parameters and Return Values

• row - The row that is being inserted, updated or deleted.
• row.FIELD - A field in the row being inserted, updated, or deleted. Modifiable by the script.
• errors - During the update/insert/delete process, you may add a message to this parameter to indicate that a field or an entire row has an error. Can be an array of errors. When any error messages are added to the error object, the insert/update/delete will be canceled.
• errors.FIELD - The field that has the error. Can be an array to indicate that many fields have errors.
• errors[null] - If you assign an error message to the null property of error, the message is returned for the entire row.
• return false - Returning false from any of these functions will cancel the insert/update/delete with a generic error message for the row.

function beforeInsert(row, errors)
{
    console.log("beforeInsert got triggered");
    console.log("row is: " + row);
    row.Email = "example@example.com";
    console.log("edited row is: " + row);
}

Sample Scripts #1

The sample module, testtriggers.module, shows how to attach a trigger script to a table/query.

To run the sample:

Sample Scripts #2

Other sample scripts are available in the module "simpletest", which can be downloaded here: simpletest.zip.

To add the module, copy simpletest/ into <LabKey_Home>/externalModules/ and then enable the module in your project.

The following sample scripts are available:

• simpletest/scripts/simpletest/Debug.js - a shared script
• simpletest/scripts/simpletest/ScriptValidationExports.js - a shared script
• simpletest/scripts/validationTest/... - contains many trigger scripts that utilize the shared scripts and the LabKey libraries.
• simpletest/queries/vehicle/colors.js - a largely stand alone trigger script
• simpletest/queries/lists/People.js - a largely stand alone list example

Related Topics

• Availability of Server-side Trigger Scripts
• Transformation Scripts