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:
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 code shows how to call a stored procedure by using the Python SDK:
import uuid
new_id= str(uuid.uuid4())
# Creating a document for a container with "id" as a partition key.
new_item = {
"id": new_id,
"category":"Personal",
"name":"Groceries",
"description":"Pick up strawberries",
"isComplete":False
}
result = container.scripts.execute_stored_procedure(sproc=created_sproc,params=[new_item], partition_key=new_id)
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 call a pretrigger using the Python SDK:
item = {'category': 'Personal', 'name': 'Groceries',
'description': 'Pick up strawberries', 'isComplete': False}
result = container.create_item(item, pre_trigger_include='trgPreValidateToDoItemTimestamp')
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 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
}
The following code shows how to register a user-defined function using the .NET SDK v3:
await client.GetContainer("database", "container").Scripts.CreateUserDefinedFunctionAsync(new UserDefinedFunctionProperties
{
Id = "Tax",
Body = File.ReadAllText(@"..\js\Tax.js")
});
The following code shows how to call a user-defined function using the .NET SDK v3:
var iterator = client.GetContainer("database", "container").GetItemQueryIterator<dynamic>("SELECT * FROM Incomes t WHERE udf.Tax(t.income) > 20000");
while (iterator.HasMoreResults)
{
var results = await iterator.ReadNextAsync();
foreach (var result in results)
{
//iterate over results
}
}
The following code shows how to register a user-defined function using the Java SDK:
Σύντομα διαθέσιμα: Καθ' όλη τη διάρκεια του 2024 θα καταργήσουμε σταδιακά τα ζητήματα GitHub ως μηχανισμό ανάδρασης για το περιεχόμενο και θα το αντικαταστήσουμε με ένα νέο σύστημα ανάδρασης. Για περισσότερες πληροφορίες, ανατρέξτε στο θέμα: https://aka.ms/ContentUserFeedback.