Trigger Scripts

Trigger scripts are attached to a database table or query. They run inside of the LabKey Server process using the Rhino JavaScript engine.

• Note that the current language version supported in our Rhino JavaScript environment for trigger scripts is ECMAScript 5.

• Validating data and rejecting it if appropriate
• Altering incoming data, such as calculating a value and storing it in a separate column
• Updating related tables by inserting, updating, or deleting from them
• Sending emails or calling other APIs

Topics

• Trigger Script Location
• Trigger Script Availability
• Script Execution
• Permissions
• Order of Execution
• Basic Process of Script Development
• Functions
• The require() Function
• Parameters and Return Values
• Console Logging
• Build String Values
• Example: Validation
• Example: Batch Tags for Sample Type Upload
• Use extraContext
• Example: Pass Data with extraContext
• LabKey JavaScript API Usage
• Example: Sending Email
• Shared Scripts / Libraries
• Invoking Java Code
• Examples from the Automated Tests
• Tutorial: Set Up a Simple Trigger Script

Trigger Script Location

Trigger scripts must be part of a deployed module, which must be enabled in every project or folder in which you want the trigger(s) to fire.

Trigger scripts are associated with the schema and table based on a naming convention. Within the module, the trigger script must be under a queries directory. If you are building a Java module or deploying it from source, nest this directory under resources. For a file based module, you can omit that layer. Within the queries directory, use a subdirectory whose name matches the schema name. The file must be named after it's associated table, and have a .js file extension. For example, a trigger script to operate on QUERY_NAME would be placed in:

Lists:
    MODULE_NAME/resources/queries/lists/QUERY_NAME.js 
Study Datasets:
    MODULE_NAME/resources/queries/study/QUERY_NAME.js
Sample Types:
    MODULE_NAME/resources/queries/samples/QUERY_NAME.js
Data Classes:
    MODULE_NAME/resources/queries/exp.data/QUERY_NAME.js
Custom Schemas:
    MODULE_NAME/resources/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.

Learn more about module directory structures here: Map of Module Files

Trigger Script Availability

Once the module containing your trigger scripts is deployed, it must be enabled in every project or folder where you want it to fire:

• Select the (Admin) > Folder > Management > Folder Type tab.
• Check the box for your module.
• Click Update Folder to enable.

Script Execution

The script will be initialized once per batch of rows. Any JavaScript state you collect will be discarded, and will not be available to future invocations.

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

Permissions

While a Platform Developer or Administrator must add any trigger script, when it is run, it will be run as the user initiating the insert (thus triggering the script with that user's permissions). In a JavaScript trigger there is no way to override the permissions check, meaning the script cannot use any resources that would be unavailable to any user eligible to insert data.

However, in JavaScript, you can call Java class functions which may be able to override permissions in a few ways, including using "LimitedUser" to limit specific 'additional' permissions, such as enforcing row uniqueness by checking a table that ordinary users can't access. Depending on the trigger content, some of the functionality could be moved to a Java class called from the trigger, which will allow bypassing some permissions. This can also make troubleshooting triggers a little easier as you can set breakpoints in the Java helper class but not in JS triggers.

Order of Execution

When multiple modules are enabled in the project or folder and several include trigger scripts for the same table, they will be executed in reverse module dependency order. For example, assume moduleA has a dependency on moduleB and both modules have trigger scripts defined for mySchema.myTable. When a row is inserted into myTable, moduleA's trigger script will fire first, and then moduleB's trigger script will fire.

Basic Process of Script Development

The basic process for adding and running scripts is as follows:

• Create and deploy the .module file that includes your trigger script.
• It is strongly recommended that you test triggers only on a staging or other test server environment with a copy of the actual data.
• Turn on the JavaScript Console: (Admin) > Developer Links > Server JavaScript Console.
• Enable the module in a folder.
• Navigate to the the module-enabled folder.
• Go to the table which has the trigger enabled (Admin) > Developer Links > Schema Browser > SCHEMA > QUERY.
• Click View Data.
• Insert a new record using (Insert Data) > Insert New Row.
• On the server's internal JavaScript console ( (Admin) > Developer Links > Server JavaScript Console), monitor which trigger scripts are run.

• Exercise other triggers in your script by editing or deleting records.
• Iterate over your scripts until you see the desired behavior before deploying for use with real data.

Functions

• require(CommonJS_Module_Identifier) - 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.
• init(event, errors) - invoked once, before any of the rows are processed. Scripts may perform whatever setup the need, such as pre-loading a cache.
• complete(event, errors) - invoked once, after all of the rows are processed

• beforeInsert(row, errors)
• beforeUpdate(row, oldRow, errors)
• beforeDelete(row, errors)
• afterInsert(row, errors)
• afterUpdate(row, oldRow, errors)
• afterDelete(row, errors)

Parameters and Return Values

• event - Either "insert", "update" or "delete", depending on the operation being performed.
• row - An object representing the row that is being inserted, updated or deleted. Fields of this object will represent the values of the columns, and may be modified by the script.
• errors - If any error messages are added to the error object, the operation will be canceled.
• errors.FIELD - For row-specific functions, associate errors with specific fields by using the fields' name as the property. The value may be either a simple string, or an array of strings if there are multiple problems with the field.
• errors[null] - For row-specific functions, use null as the key if the error is associated with the row in general, and not scoped to a specific field. The value may be either a simple string, or an array of strings if there are multiple problems with the row.
• return false - Returning false from any of these functions will cancel the insert/update/delete with a generic error message for the row.

Build String Values

String interpolation using template literals (substitution syntax like ${fieldName}) does not work in a trigger script. To build a compound string, such as using different fields from a row, build it using syntax like the following. Here we create both a header and detail row, which we can then send it as an email:

var message = "<h2>New Participant: " + row.ParticipantId + " Received</h2><p>Participant Id: " + row.ParticipantId + "<br>Cohort: " + row.cohort + "<br>Start Date: " + row.Startdate + "<br>Country: " + row.country + "</p>";

Note that the "Date" function name is reserved. You cannot reference a field named "Date" in a trigger script as that string is reserved for the "Date" function.

You also cannot use backticks (`) in a trigger script. If you do, you'll see logged errors about illegal characters.

Console Logging

A console API is provided for debugging purposes. Import it using require("console"). It exports a function as defined by the standard JavaScript console.

var console = require("console");
console.log("before insert using this trigger script");
...
console.log("after insert using this trigger script");

The output is available in the labkey.log file, and via JavaScript Console: (Admin) > Developer Links > Server JavaScript Console.

Example: Validation

This example shows how to apply the validation "MyField Must be a Positive Number" before doubling it to store in a "doubledValue" field:

var console = require("console");

function init(event, errors) {
  console.log('Initializing trigger script');
}

function beforeInsert(row, errors) {
  console.log('beforeInsert invoked for row: ' + row);
  if (!row.myField || row.myField <= 0) {
    errors.myField = 'MyField must be positive, but was: ' + row.myField;
  }
  row.doubledValue = row.myField * 2;
}

Example: Batch Tags for Sample Type Upload

When multiple samples are uploaded at one time, this trigger script tags each sample in the batch with the same 6 digit batch id, to function as a sample grouping mechanism.

// Assumes the sample type has a text field named 'UploadBatchId'.
var console = require("console");
var uploadBatchTag = '';

function init(event, errors) {
  console.log('Initializing trigger script');
  // Set a 6 digit random tag for this batch/bulk upload of samples.
  uploadBatchTag = Math.floor(100000 + Math.random() * 900000);
}

function beforeInsert(row, errors) {
  console.log('beforeInsert invoked for row: ' + row);
  row.uploadBatchId = uploadBatchTag;
}

Use extraContext

extraContext is a global variable that is automatically injected into all trigger scripts, enabling you to pass data between triggers in the same file and between rows in the same batch. It is a simple JavaScript object that is global to the batch of data being inserted, updated or deleted. The triggers can add, delete or update properties in extraContext with data that will then be available to other triggers and rows within the same batch. If a trigger is fired due to an ETL, the property "dataSource": "etl" will be in the extraContext object. This can be helpful to isolate trigger behavior specifically for ETL or other forms of data entry.

Using extraContext can accomplish two things:

1. The server can pass metadata to the trigger script, like {dataSource: "etl"}. This functionality is primarily used to determine if the dataSource is an ETL or if there are more rows in the batch.
2. Trigger scripts can store and read data across triggers within the same file and batch of rows. In the following example, the afterInsert row trigger counts the number of rows entered and the complete batch trigger prints that value when the batch is complete. Or for a more practical example, such a trigger could add up blood draws across rows to calculate a cumulative blood draw.

Example: Pass Data with extraContext

Example trigger that counts rows of an ETL insert batch using extraContext to pass data between functions.

var console = require("console");

function afterInsert(row, errors) {

    if (extraContext.dataSource === "etl") {
        console.log("this is an ETL");

        if (extraContext.count === undefined) {
            extraContext.count = 1
        }
        else {
            extraContext.count++;
        }
    }
}

function complete(event, errors) {
    if (extraContext.count !== undefined)
        console.log("Total ETL rows inserted in this batch: " + extraContext.count);
}

LabKey JavaScript API Usage

Your script can invoke APIs provided by the following LabKey JavaScript libraries, including:

• @labkey/api/ActionURL
• @labkey/api/Ajax
• @labkey/api/Filter
• @labkey/api/Message
• @labkey/api/Query
• @labkey/api/Security
• @labkey/api/Utils (only non-browser related functions)

Example: Sending Email

To send an email, use the Message API. This example sends an email after all of the rows have been processed:

var LABKEY = require("labkey");

function complete(event, errors) {
    // Note that the server will only send emails to addresses associated with an active user account
    var userEmail = "messagetest@validation.test";

    var msg = LABKEY.Message.createMsgContent(LABKEY.Message.msgType.plain, 'Rows were ' + event);
    var recipient = LABKEY.Message.createRecipient(LABKEY.Message.recipientType.to, userEmail);
    var response = LABKEY.Message.sendMessage({
        msgFrom:userEmail,
        msgRecipients:[recipient],
        msgContent:[msg]
    });
}

If you want to send a more formatted message using details from the row that was just inserted, use the msgType.html and something similar to:

var LABKEY = require("labkey");

function afterInsert(row, errors) {
    // Note that the server will only send emails to/from addresses associated with an active user account
    var userEmail = "messagetest@validation.test";
    var senderEmail = "adminEmail@myserver.org";

    var subject = "New Participant: " + row.ParticipantId;
    var message = "<h2>New Participant: " + row.ParticipantId + " Received</h2><p>Participant Id: " + row.ParticipantId + "<br>Cohort: " + row.cohort + "<br>Start Date: " + row.Startdate + "<br>Country: " + row.country + "</p>";

    var msg = LABKEY.Message.createMsgContent(LABKEY.Message.msgType.html, message);
    var recipient = LABKEY.Message.createRecipient(LABKEY.Message.recipientType.to, userEmail);
    var response = LABKEY.Message.sendMessage({
        msgFrom: senderEmail,
        msgRecipients: [recipient],
        msgSubject: subject,
        msgContent: [msg]
    });
}

Shared Scripts / Libraries

Trigger scripts can import functionality from other shared libraries.

Shared libraries should be located in a LabKey module as follows, 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, though not strictly required, to avoid namespace collisions. The directory structure depends on whether or not you are building from source.

When building the module from source:
    MODULE_NAME/resources/scripts/MODULE_NAME/SCRIPT_FILE.js

In a deployed module:
    MODULE_NAME/scripts/MODULE_NAME/SCRIPT_FILE.js

Learn more about module directory structure here: Map of Module Files

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 a 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");
}

Invoking Java Code

JavaScript-based trigger script code can also invoke Java methods. It is easy to use a static factory or getter method to create an instance of a Java class, and then methods can be invoked on it as if it were a JavaScript object. The JavaScript engine will attempt to coerce types for arguments as appropriate.

This can be useful when there is existing Java code available, when the APIs needed aren't exposed via the JavaScript API, or for situations that require higher-performance.

var myJavaObject = org.labkey.mypackage.MyJavaClass.create();
var result = myJavaObject.doSomething('Argument 1', 2);

Examples from the Automated Tests

A few example modules containing scripts are available in the modules "simpletest" and "triggerTestModule", which can be found on GitHub here:

• https://github.com/LabKey/testAutomation/tree/develop/modules/simpletest
• https://github.com/LabKey/testAutomation/tree/develop/modules/triggerTestModule

Even without attempting to install them on a live server, you can unzip the module structure and review how the scripts and queries are structured to use as a model for your own development.

The following example scripts are included in "simpletest":

• queries/vehicle/colors.js - a largely stand alone trigger script. When you insert a row into the Vehicle > Colors query, it performs several format checks and shows how error messages can be returned.
• queries/lists/People.js - a largely stand alone list example. When you insert/update/delete rows in the People list, this trigger script checks for case insensitivity of the row properties map.

Tutorial: Set Up a Simple Trigger Script

To get started, you can follow these steps to set up the "Colors.js" trigger script to check color values added to a list.

Related Topics

• Data Quality Control
• Transform Scripts
• Map of Module Files