Muokkaa

Jaa


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. After you define one or more stored procedures, triggers, or 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 do 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: Azure Cosmos DB for NoSQL client library for .NET
Java Quickstart: Build a Java app to manage Azure Cosmos DB for NoSQL data
JavaScript Quickstart: Azure Cosmos DB for NoSQL client library for Node.js
Python Quickstart: Azure Cosmos DB for NoSQL client library for Python

Important

The following code samples assume that you have already have client and container variables. If you need to create those variables, refer to the appropriate quickstart for your platform.

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, see How to write stored procedures.

The following examples show how to register and call a stored procedure by using the Azure Cosmos DB SDKs. For the source for this stored procedure, saved as spCreateToDoItem.js, see Create items using stored procedures.

Note

For partitioned containers, when you run a stored procedure, you must provide a partition key value in the request options. Stored procedures are always scoped to a partition key. Items that have a different partition key value aren't visible to the stored procedure. This principle also applies to triggers.

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 pretriggers

The following examples show how to register and call a pretrigger by using the Azure Cosmos DB SDKs. For the source of this pretrigger example, saved as trgPreValidateToDoItemTimestamp.js, see Pretriggers.

When you run an operation by specifying PreTriggerInclude and then passing the name of the trigger in a List object, pretriggers are passed in the RequestOptions object.

Note

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

The following code shows how to register a pretrigger 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 pretrigger 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. For the source of this post-trigger example, saved as trgPostUpdateMetadata.js, see Post-triggers

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. For the source of this user-defined function example, saved as udfTax.js, see How to write user-defined functions.

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: