How to register and use stored procedures, triggers, and user-defined functions in Azure Cosmos DB

APPLIES TO: NoSQL

The API for NoSQL in Azure Cosmos DB supports registering and invoking stored procedures, triggers, and user-defined functions (UDFs) written in JavaScript. Once you've defined one or more stored procedures, triggers, and user-defined functions, you can load and view them in the Azure portal by using Data Explorer.

You can use the API for NoSQL SDK across multiple platforms including .NET v2 (legacy), .NET v3, Java, JavaScript, or Python SDKs to perform these tasks. If you haven't worked with one of these SDKs before, see the "Quickstart" article for the appropriate SDK:

SDK Getting started
.NET v3 Quickstart: Build a .NET console app to manage Azure Cosmos DB for NoSQL resources
Java Quickstart: Build a Java app to manage Azure Cosmos DB for NoSQL data
JavaScript Quickstart: Use Node.js to connect and query data from Azure Cosmos DB for NoSQL account
Python Quickstart: Build a Python application using an Azure Cosmos DB for NoSQL account

How to run stored procedures

Stored procedures are written using JavaScript. They can create, update, read, query, and delete items within an Azure Cosmos DB container. For more information on how to write stored procedures in Azure Cosmos DB, see How to write stored procedures in Azure Cosmos DB article.

The following examples show how to register and call a stored procedure by using the Azure Cosmos DB SDKs. Refer to Create a Document as the source for this stored procedure is saved as spCreateToDoItem.js.

Note

For partitioned containers, when executing a stored procedure, a partition key value must be provided in the request options. Stored procedures are always scoped to a partition key. Items that have a different partition key value will not be visible to the stored procedure. This also applied to triggers as well.

The following example shows how to register a stored procedure by using the .NET SDK v2:

string storedProcedureId = "spCreateToDoItems";
StoredProcedure newStoredProcedure = new StoredProcedure
   {
       Id = storedProcedureId,
       Body = File.ReadAllText($@"..\js\{storedProcedureId}.js")
   };
Uri containerUri = UriFactory.CreateDocumentCollectionUri("myDatabase", "myContainer");
var response = await client.CreateStoredProcedureAsync(containerUri, newStoredProcedure);
StoredProcedure createdStoredProcedure = response.Resource;

The following code shows how to call a stored procedure by using the .NET SDK v2:

dynamic[] newItems = new dynamic[]
{
    new {
        category = "Personal",
        name = "Groceries",
        description = "Pick up strawberries",
        isComplete = false
    },
    new {
        category = "Personal",
        name = "Doctor",
        description = "Make appointment for check up",
        isComplete = false
    }
};

Uri uri = UriFactory.CreateStoredProcedureUri("myDatabase", "myContainer", "spCreateToDoItem");
RequestOptions options = new RequestOptions { PartitionKey = new PartitionKey("Personal") };
var result = await client.ExecuteStoredProcedureAsync<string>(uri, options, new[] { newItems });

How to run pre-triggers

The following examples show how to register and call a pre-trigger by using the Azure Cosmos DB SDKs. Refer to the Pre-trigger example as the source for this pre-trigger is saved as trgPreValidateToDoItemTimestamp.js.

Pre-triggers are passed in the RequestOptions object, when executing an operation, by specifying PreTriggerInclude and then passing the name of the trigger in a List object.

Note

Even though the name of the trigger is passed as a List, you can still execute only one trigger per operation.

The following code shows how to register a pre-trigger using the .NET SDK v2:

string triggerId = "trgPreValidateToDoItemTimestamp";
Trigger trigger = new Trigger
{
    Id =  triggerId,
    Body = File.ReadAllText($@"..\js\{triggerId}.js"),
    TriggerOperation = TriggerOperation.Create,
    TriggerType = TriggerType.Pre
};
Uri containerUri = UriFactory.CreateDocumentCollectionUri("myDatabase", "myContainer");
await client.CreateTriggerAsync(containerUri, trigger);

The following code shows how to call a pre-trigger using the .NET SDK v2:

dynamic newItem = new
{
    category = "Personal",
    name = "Groceries",
    description = "Pick up strawberries",
    isComplete = false
};

Uri containerUri = UriFactory.CreateDocumentCollectionUri("myDatabase", "myContainer");
RequestOptions requestOptions = new RequestOptions { PreTriggerInclude = new List<string> { "trgPreValidateToDoItemTimestamp" } };
await client.CreateDocumentAsync(containerUri, newItem, requestOptions);

How to run post-triggers

The following examples show how to register a post-trigger by using the Azure Cosmos DB SDKs. Refer to the Post-trigger example as the source for this post-trigger is saved as trgPostUpdateMetadata.js.

The following code shows how to register a post-trigger using the .NET SDK v2:

string triggerId = "trgPostUpdateMetadata";
Trigger trigger = new Trigger
{
    Id = triggerId,
    Body = File.ReadAllText($@"..\js\{triggerId}.js"),
    TriggerOperation = TriggerOperation.Create,
    TriggerType = TriggerType.Post
};
Uri containerUri = UriFactory.CreateDocumentCollectionUri("myDatabase", "myContainer");
await client.CreateTriggerAsync(containerUri, trigger);

The following code shows how to call a post-trigger using the .NET SDK v2:

var newItem = { 
    name: "artist_profile_1023",
    artist: "The Band",
    albums: ["Hellujah", "Rotators", "Spinning Top"]
};

RequestOptions options = new RequestOptions { PostTriggerInclude = new List<string> { "trgPostUpdateMetadata" } };
Uri containerUri = UriFactory.CreateDocumentCollectionUri("myDatabase", "myContainer");
await client.createDocumentAsync(containerUri, newItem, options);

How to work with user-defined functions

The following examples show how to register a user-defined function by using the Azure Cosmos DB SDKs. Refer to this User-defined function example as the source for this post-trigger is saved as udfTax.js.

The following code shows how to register a user-defined function using the .NET SDK v2:

string udfId = "Tax";
var udfTax = new UserDefinedFunction
{
    Id = udfId,
    Body = File.ReadAllText($@"..\js\{udfId}.js")
};

Uri containerUri = UriFactory.CreateDocumentCollectionUri("myDatabase", "myContainer");
await client.CreateUserDefinedFunctionAsync(containerUri, udfTax);

The following code shows how to call a user-defined function using the .NET SDK v2:

Uri containerUri = UriFactory.CreateDocumentCollectionUri("myDatabase", "myContainer");
var results = client.CreateDocumentQuery<dynamic>(containerUri, "SELECT * FROM Incomes t WHERE udf.Tax(t.income) > 20000"));

foreach (var result in results)
{
    //iterate over results
}

Next steps

Learn more concepts and how-to write or use stored procedures, triggers, and user-defined functions in Azure Cosmos DB: