Tag: Triggers

Build serverless APIs, including implementing triggers and bindings (AI-200 Exam Prep)

This post is a part of the AI-200: Developing AI Cloud Solutions on Azure  Exam Prep Hub.
This topic falls under these sections:
Connect to and consume Azure services (20–25%)
   --> Develop and implement Azure Functions
      --> Build serverless APIs, including implementing triggers and bindings


Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Overview

Azure Functions is a serverless compute service that allows developers to execute code in response to events without managing the underlying servers. For AI-enabled applications, Azure Functions can provide lightweight, scalable APIs and backend processing components that connect AI workloads to databases, messaging services, storage, and other Azure services.

For the AI-200 exam, an important area is understanding how to build serverless APIs using Azure Functions, particularly how triggers and bindings work together.

The key concepts include:

  • HTTP triggers
  • HTTP output bindings
  • Function routes
  • Authorization levels
  • Input bindings
  • Output bindings
  • Binding expressions
  • Multiple bindings
  • Trigger versus binding
  • Stateless serverless API design
  • Connecting Functions to other Azure services
  • Appropriate use of HTTP-triggered Functions

1. What Is Azure Functions?

Azure Functions is an event-driven serverless compute platform.

Instead of provisioning and maintaining virtual machines or application servers, you deploy individual functions that execute when an event occurs.

A function can be triggered by events such as:

  • HTTP requests
  • Azure Storage queue messages
  • Blob changes
  • Service Bus messages
  • Event Grid events
  • Event Hubs events
  • Timer schedules

For example, an AI application might expose an HTTP endpoint:

POST /api/summarize

The request could contain a document that needs to be summarized.

The HTTP-triggered Function could:

  1. Receive the request.
  2. Validate the input.
  3. Call an Azure AI service.
  4. Store the result in a database.
  5. Return the generated summary.

This allows the application to implement an API without maintaining a dedicated web server.


2. What Is a Trigger?

A trigger defines how a function is invoked.

Every Azure Function must have exactly one trigger.

For example:

HTTP request
|
v
HTTP trigger
|
v
Azure Function

The trigger provides the initial event or data that causes the function to execute.

Common triggers include:

TriggerFunction executes when…
HTTPAn HTTP request is received
TimerA scheduled time is reached
BlobA blob-related event occurs
QueueA queue message is available
Service BusA Service Bus message is available
Event GridAn Event Grid event is received
Event HubsEvents arrive in an Event Hub

For serverless APIs, the HTTP trigger is particularly important.


3. What Is an HTTP Trigger?

An HTTP trigger allows an Azure Function to execute when an HTTP request is received.

This makes HTTP-triggered Functions particularly useful for building:

  • REST APIs
  • Webhooks
  • Backend endpoints
  • AI inference APIs
  • Data-processing APIs
  • Lightweight microservices

For example:

Client
|
| POST /api/analyze
v
Azure Function
|
+----> Azure AI service
|
+----> Database
|
v
HTTP response

The HTTP trigger can respond to specific HTTP methods such as:

  • GET
  • POST
  • PUT
  • PATCH
  • DELETE

The supported methods are configured as part of the HTTP trigger.


4. HTTP Trigger Versus HTTP Output Binding

One of the most important concepts for the exam is distinguishing the trigger from the output binding.

The HTTP trigger receives the request:

HTTP request
|
v
HTTP trigger

The HTTP output sends the response:

Function
|
v
HTTP output
|
v
HTTP response

Therefore:

HTTP trigger = how the function is invoked

HTTP output = how the function sends an HTTP response

In most Azure Functions programming models, the function’s return value can be used to produce the HTTP response.


5. HTTP Routes

An HTTP-triggered Function has a URL endpoint.

By default, the route generally follows this pattern:

https://<APP_NAME>.azurewebsites.net/api/<FUNCTION_NAME>

For example:

https://my-ai-api.azurewebsites.net/api/analyze

You can customize the route.

For example, an API might use:

/api/products/{id}

A request such as:

GET /api/products/123

can cause the Function to receive:

id = 123

Route parameters are particularly useful when designing REST-style APIs.


6. HTTP Methods

An API endpoint should normally expose only the HTTP methods it actually needs.

For example:

GET /api/products/{id}
POST /api/products
PUT /api/products/{id}
DELETE /api/products/{id}

A Function can be configured to respond to specific methods.

This allows a single Function endpoint to implement appropriate REST operations.

For example:

GET /api/orders/123

could retrieve an order, while:

POST /api/orders

could create an order.

Exam tip: Don’t confuse the HTTP method with the trigger. The HTTP trigger causes the Function to execute; the configured HTTP methods determine which types of requests the endpoint accepts.


7. Authorization Levels

HTTP-triggered Functions can use authorization levels to control who can invoke the function.

Common authorization levels include:

Anonymous

No Function key is required.

Useful for:

  • Public endpoints
  • Public webhooks
  • APIs where authentication is handled elsewhere

However, anonymous does not mean that the endpoint should necessarily be considered secure. If sensitive operations are exposed, authentication and authorization should be implemented appropriately.

Function

A Function key is required.

This provides a simple mechanism for restricting invocation of the Function.

Admin

An administrative key is required.

This provides a higher level of access and should be used carefully.

Exam consideration: If a question asks for an HTTP endpoint that should require a Function key, Function authorization is the relevant setting.


8. What Are Bindings?

Bindings provide a declarative way for Azure Functions to connect to other services.

There are two primary types:

  • Input bindings
  • Output bindings

Bindings allow developers to avoid writing all of the connection and resource-management code themselves.

For example, instead of manually creating an Azure Storage client, authenticating to Storage, and retrieving a blob, a Function can use a blob input binding.

Conceptually:

Function
|
+---- Input binding ----> Azure Storage
|
+---- Output binding ---> Database

Bindings are optional.

A Function can have:

  • A trigger only
  • A trigger + input binding
  • A trigger + output binding
  • A trigger + multiple input/output bindings

9. Trigger Versus Input Binding

A trigger and an input binding are related but serve different purposes.

Trigger

Determines when the function executes.

Input binding

Provides additional data to the function.

For example:

HTTP request
|
v
HTTP trigger
|
v
Function
|
+---- Blob input binding
| |
| v
| Blob data

The HTTP request causes the Function to execute.

The blob input binding provides additional data.


10. Output Bindings

An output binding allows a Function to write data to another service.

For example, an HTTP Function could receive a request and write the result to Azure Storage.

HTTP request
|
v
Function
|
+----> Storage output binding

Another example:

HTTP request
|
v
AI processing Function
|
+----> Cosmos DB
|
+----> HTTP response

Output bindings can simplify integration with supported Azure services.


11. Multiple Bindings

A Function can use multiple bindings.

For example, an AI API might:

  1. Receive an HTTP request.
  2. Read customer information from Cosmos DB.
  3. Call an AI service.
  4. Write the result to Blob Storage.
  5. Return an HTTP response.

Conceptually:

                   +--> Cosmos DB input
                   |
HTTP request ---> Function ---> Blob Storage output
                   |
                   +--> HTTP response

The Function still has only one trigger, but it can have multiple additional bindings.


12. Binding Expressions

Binding expressions allow information from one binding to be used dynamically by another binding.

For example, suppose a queue message contains:

customer123

A binding expression could use that value to determine which resource should be accessed.

Conceptually:

Queue message
|
| customer123
v
Queue trigger
|
v
Binding expression
|
v
Customer-specific resource

This can reduce hardcoded configuration and make Functions more flexible.

Binding expressions commonly use curly-brace syntax such as:

{parameter}

13. Application Settings and Connection Information

Bindings commonly reference configuration values through application settings.

For example:

MyStorageConnection

could identify an application setting containing the connection information required by a storage binding.

This is preferable to hardcoding connection strings directly into source code.

For example, avoid:

connectionString = "DefaultEndpointsProtocol=..."

Instead, reference configuration:

connection = "MyStorageConnection"

The actual configuration can then be supplied through the Function App’s settings.

For production workloads, secrets should be managed securely, commonly using Azure Key Vault and managed identities where appropriate.


14. Building a Serverless API

A typical serverless API using Azure Functions can follow this architecture:

                  Client
                    |
                    | HTTPS
                    v
              HTTP Trigger
                    |
                    v
              Azure Function
             /      |       \
            /       |        \
           v        v         v
      Cosmos DB   Azure AI   Service Bus
         |          |           |
         +----------+-----------+
                    |
                    v
              HTTP Response

The Function acts as the lightweight API layer.

This architecture is particularly useful for AI applications because the Function can coordinate several backend services without requiring a traditional application server.


15. Example: AI Inference API

Consider an AI application that exposes:

POST /api/analyze

The request contains:

{
"text": "Customer feedback..."
}

The Function could:

  1. Receive the HTTP request.
  2. Parse the JSON.
  3. Validate the input.
  4. Send the text to an AI service.
  5. Store the result.
  6. Return JSON to the client.

The response might look like:

{
"sentiment": "positive",
"confidence": 0.94
}

The Function therefore acts as an API façade around the AI processing workflow.


16. Choosing Between HTTP Triggers and Other Triggers

The trigger should match how the workload is initiated.

Use an HTTP trigger when:

  • A client needs to call an API.
  • A web application needs an endpoint.
  • A webhook needs to invoke the Function.
  • An application needs synchronous request/response behavior.

Use a queue or messaging trigger when:

  • Work should be processed asynchronously.
  • Requests may arrive faster than they can be processed.
  • You need decoupling between components.
  • Long-running processing should not block an HTTP request.

For example:

HTTP API
|
v
Service Bus
|
v
Function
|
v
AI processing

may be preferable to:

HTTP API
|
v
AI processing
|
v
HTTP response

when AI processing could take significant time.


17. Synchronous Versus Asynchronous APIs

This distinction is important when designing serverless AI applications.

Synchronous

The client waits for the Function to complete.

Client
|
| Request
v
Function
|
| Process
v
Client receives response

This works well when processing is relatively quick.

Asynchronous

The API accepts the request and places work into a messaging system.

Client
|
v
HTTP Function
|
v
Service Bus
|
v
Processing Function
|
v
AI workload

The client doesn’t have to wait for the complete operation.

This architecture can improve resilience and scalability.


18. HTTP Function Response Codes

A well-designed API should return appropriate HTTP status codes.

Common examples include:

StatusMeaningExample
200OKSuccessful GET
201CreatedResource created
202AcceptedAsynchronous processing accepted
204No ContentSuccessful request with no response body
400Bad RequestInvalid input
401UnauthorizedAuthentication required
403ForbiddenAccess denied
404Not FoundResource doesn’t exist
409ConflictResource conflict
500Internal Server ErrorUnexpected server failure

For example, if an API accepts an AI processing request and queues it for asynchronous processing, a 202 Accepted response may be appropriate.


19. Error Handling

Serverless APIs should explicitly handle expected errors.

For example:

Request
|
v
Validate input
|
+---- Invalid ---> 400 Bad Request
|
v
Process request
|
+---- Resource missing ---> 404
|
+---- Unexpected failure -> 500
|
v
200 OK

Don’t expose sensitive internal information in error responses.

For example, avoid returning:

SQL connection string:
Server=...
Password=...

or detailed internal stack traces to clients.


20. Connection Management

An important practical consideration when developing HTTP-triggered Azure Functions is connection management.

Creating a new HTTP client or network connection for every Function invocation can lead to connection exhaustion and degraded performance.

Applications should use appropriate connection reuse patterns rather than repeatedly creating unmanaged HTTP clients.

This becomes particularly important for Functions that call:

  • Azure AI services
  • REST APIs
  • Databases
  • Storage
  • Other backend services

21. Serverless API Design Best Practices

Keep Functions focused

A Function should ideally have a clear responsibility.

Avoid creating one enormous Function that:

  • Validates requests
  • Performs database operations
  • Calls multiple AI models
  • Sends emails
  • Processes files
  • Publishes events
  • Performs unrelated business logic

Smaller, focused Functions are generally easier to test and maintain.

Use configuration instead of hardcoding

Store environment-specific configuration outside application code.

Protect sensitive APIs

Use appropriate authentication and authorization.

Validate requests

Don’t assume that incoming JSON is valid.

Return appropriate status codes

Use HTTP semantics consistently.

Design for retries

Backend services may retry operations. Functions should avoid unintended duplicate side effects.

Avoid unnecessary synchronous processing

If an operation can take a long time, consider an asynchronous architecture using messaging.

Reuse connections

Avoid connection exhaustion caused by creating network clients unnecessarily.


22. Important AI-200 Exam Distinctions

The following distinctions are especially important to remember.

ConceptWhat it does
TriggerCauses the Function to execute
HTTP triggerExecutes the Function when an HTTP request arrives
Input bindingProvides additional data to the Function
Output bindingWrites Function output to another resource
HTTP outputSends an HTTP response
RouteDefines the HTTP endpoint pattern
Authorization levelControls Function-level invocation authorization
Binding expressionDynamically resolves binding values
Application settingStores configuration used by the application/bindings

A particularly important exam rule is:

A Function has exactly one trigger, but it can have multiple input and output bindings.


23. Key Takeaways

For the AI-200 exam, remember these points:

  1. Azure Functions provides serverless compute.
  2. A trigger determines when a Function runs.
  3. Every Function has exactly one trigger.
  4. HTTP triggers are used to create serverless APIs and receive webhooks.
  5. HTTP output provides the response to an HTTP-triggered request.
  6. Input bindings provide additional data to a Function.
  7. Output bindings allow a Function to write to supported services.
  8. A Function can have multiple input and output bindings.
  9. Binding expressions allow dynamic values to flow between bindings.
  10. Application settings should be used for configuration rather than hardcoding secrets.
  11. HTTP methods and routes define how an HTTP API endpoint behaves.
  12. Asynchronous workloads can use messaging services rather than keeping HTTP requests open.
  13. Appropriate HTTP status codes should communicate success and failure conditions.
  14. Connection reuse is important for high-throughput HTTP Functions.
  15. For production applications, authentication, authorization, secure configuration, validation, and error handling are essential.

Practice Exam Questions

Question 1

A developer is building a serverless API that should execute whenever a client sends an HTTP POST request. Which Azure Functions feature should the developer use to initiate the Function?

A. HTTP trigger
B. HTTP output binding
C. Queue output binding
D. Timer trigger

Correct Answer: A

Explanation: An HTTP trigger causes an Azure Function to execute when an HTTP request is received. An HTTP output binding is used to produce the HTTP response, while a queue output binding sends data to a queue. A timer trigger executes according to a schedule.


Question 2

A Function receives an HTTP request and needs to write the resulting document to Azure Blob Storage without explicitly creating and managing a Blob Storage client in application code. What should the developer use?

A. HTTP trigger
B. Blob Storage output binding
C. Timer trigger
D. HTTP route parameter

Correct Answer: B

Explanation: An output binding provides a declarative way for a Function to write data to another supported Azure service. A Blob Storage output binding can write the Function’s output to a blob without requiring the developer to implement all of the storage interaction manually.


Question 3

A Function needs to retrieve additional data from Azure Storage after being invoked by an HTTP request. Which configuration best satisfies this requirement?

A. Configure two HTTP triggers.
B. Configure a second HTTP output binding.
C. Configure an HTTP trigger and an input binding.
D. Configure two Function authorization keys.

Correct Answer: C

Explanation: The HTTP trigger determines when the Function runs, while an input binding can provide additional data to the Function. A Function must have exactly one trigger, but it can have additional input bindings.


Question 4

An HTTP-triggered Function should only respond to requests using the POST method. What should the developer configure?

A. A storage input binding
B. A timer schedule
C. A custom output binding
D. The HTTP trigger’s allowed HTTP methods

Correct Answer: D

Explanation: The HTTP trigger can be configured with the HTTP methods to which it responds. Restricting the endpoint to POST prevents other HTTP methods from invoking that endpoint.


Question 5

A developer needs an HTTP Function endpoint with the following URL pattern:

/api/orders/12345

where 12345 represents an order identifier. What Azure Functions feature should be used to define the 12345 portion dynamically?

A. Route parameter
B. Output binding
C. Timer expression
D. Function key

Correct Answer: A

Explanation: HTTP route parameters allow portions of the URL to be captured and passed to the Function. A route such as /api/orders/{id} can capture 12345 as the id parameter.


Question 6

An Azure Function needs to receive an HTTP request, retrieve information from a database, write a result to storage, and return an HTTP response. How should the Function be configured?

A. Four triggers
B. One HTTP trigger with appropriate input/output bindings
C. One database trigger and three HTTP triggers
D. Four separate timer triggers

Correct Answer: B

Explanation: A Function has exactly one trigger. In this scenario, the HTTP request should be the trigger, while database and storage interactions can be implemented using appropriate bindings. The HTTP response is also produced by the HTTP output mechanism.


Question 7

A developer wants to prevent an HTTP-triggered Function from being publicly invokable without a Function key. Which authorization level should be used?

A. Anonymous
B. Public
C. Function
D. None

Correct Answer: C

Explanation: The Function authorization level requires a Function key when invoking the HTTP endpoint. Anonymous does not require a Function key. Authentication and authorization requirements should still be evaluated in the context of the overall application architecture.


Question 8

An AI API accepts a request and places the work into a queue for processing by another Function. The API should immediately tell the client that the request has been accepted for processing rather than waiting for the AI operation to finish. Which HTTP status code is most appropriate?

A. 404
B. 500
C. 201
D. 202

Correct Answer: D

Explanation: 202 Accepted is appropriate when a request has been accepted for processing but the processing has not completed. This pattern is useful for asynchronous AI workloads where the client shouldn’t have to maintain an open HTTP request while the backend performs potentially lengthy processing.


Question 9

A Function receives a queue message and uses information from that message to determine which blob should be accessed through another binding. Which Azure Functions feature can dynamically pass values between bindings?

A. Binding expressions
B. Authorization levels
C. HTTP methods
D. Function keys

Correct Answer: A

Explanation: Binding expressions allow values from trigger metadata, binding data, and other supported sources to be incorporated dynamically into binding configuration. This allows Functions to avoid hardcoding resource names and paths.


Question 10

An HTTP-triggered Function calls an external AI service. Under heavy load, the Function begins experiencing connection exhaustion because a new HTTP client is created for every invocation. What is the best approach?

A. Increase the HTTP response timeout indefinitely.
B. Disable the HTTP trigger.
C. Use an appropriate connection-reuse pattern rather than repeatedly creating HTTP clients.
D. Add another HTTP trigger to the same Function.

Correct Answer: C

Explanation: Repeatedly creating and disposing HTTP clients can contribute to connection exhaustion and poor performance. HTTP clients and connections should be managed using an appropriate reuse pattern for the runtime and language being used. Adding triggers or changing the response timeout does not address the underlying connection-management problem.


Final Exam Review

If you remember only a handful of concepts for this AI-200 topic, make them these:

Trigger = starts the Function.

Binding = connects the Function to another resource.

Input binding = brings data into the Function.

Output binding = sends data from the Function to another resource.

HTTP trigger = serverless API entry point.

HTTP output = response to the API caller.

One Function = exactly one trigger, potentially multiple bindings.

And for scenario questions, focus on why the Function is being invoked and what resources it needs to interact with. Those two questions usually reveal whether the correct answer involves a trigger, an input binding, an output binding, or an HTTP configuration.


Go to the AI-200 Exam Prep Hub main page

Design and implement controls for deployment pipelines, including branching policies, triggers in approvals, authentication tables, and code owners – Part 2 (DP-800 Exam Prep)

This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Secure, optimize, and deploy database solutions (35–40%)
   --> Implement CI/CD by using SQL Database Projects
      --> Design and implement controls for deployment pipelines, including branching policies, triggers in approvals, authentication tables, and code owners


Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

In Part 1, you learned how branching strategies, pull requests, branch protection policies, and Code Owners help organizations maintain secure and reliable SQL Database Projects. In this section, we focus on how deployment pipelines automatically execute, how approvals and authentication secure deployments, and how organizations protect production environments.


Pipeline Triggers

A pipeline trigger determines when a build or deployment pipeline starts.

Rather than requiring developers to manually start every pipeline, modern CI/CD systems automatically execute pipelines based on predefined events.

Common trigger types include:

  • Source code commits
  • Pull requests
  • Scheduled executions
  • Manual execution
  • Completion of another pipeline
  • Tag creation
  • Release approvals

Choosing the appropriate trigger helps balance automation with governance.


Continuous Integration Triggers

Continuous Integration (CI) pipelines usually start automatically after code changes.

Typical CI trigger:

Developer Commit
Git Repository
Automatic Build
Compile SQL Database Project
Run Validation
Publish Build Artifact

Benefits include:

  • Immediate feedback
  • Early detection of errors
  • Frequent validation
  • Consistent builds
  • Reduced integration problems

Common CI Trigger Events

Commit Trigger

The pipeline starts whenever a developer commits changes.

Example:

Commit to feature branch
Build Pipeline Starts

Useful for:

  • Early validation
  • Fast feedback
  • Developer productivity

Pull Request Trigger

Instead of triggering on every commit, organizations often build whenever a pull request is created or updated.

Example:

Feature Branch
Create Pull Request
Automatic Validation
Review

Benefits:

  • Ensures only validated code reaches protected branches
  • Prevents broken code from being merged
  • Supports branch protection policies

Scheduled Trigger

Some validation pipelines execute on a schedule.

Example:

Every Night
Run Full Test Suite

Useful for:

  • Long-running tests
  • Security scanning
  • Dependency validation
  • Performance testing

Manual Trigger

Certain deployments should never execute automatically.

Example:

Release Manager
Start Production Deployment

Manual triggers provide additional governance before production releases.


Continuous Delivery Triggers

Continuous Delivery (CD) pipelines move validated artifacts through multiple environments.

Example:

Build Artifact
Development
Testing
Staging
Production

Each stage may have different approval requirements.


Deployment Approvals

Approvals ensure that qualified personnel review changes before deployment.

Instead of automatically deploying to production, pipelines pause until an authorized user approves the release.

Example:

Deployment Ready
Approval Required
Manager Approves
Deployment Continues

Types of Deployment Approvals

Manual Approval

A designated reviewer manually approves deployment.

Common reviewers include:

  • Database Administrator
  • Development Lead
  • Security Team
  • Operations Team
  • Product Owner

Multi-Stage Approval

Different environments require different reviewers.

Example:

EnvironmentRequired Approval
DevelopmentNone
TestTeam Lead
StagingDBA
ProductionDBA + Operations Manager

This layered approval process minimizes production risk.


Conditional Approval

Approval requirements may depend on:

  • Database type
  • Environment
  • Change size
  • Security classification
  • Time of deployment

Example:

Production Deployment
Contains Schema Changes?
Yes
Require DBA Approval

Environment Protection

Modern DevOps platforms allow organizations to protect deployment environments.

Environment protection can require:

  • Manual approvals
  • Deployment windows
  • Authentication verification
  • Security policies
  • Health checks

Example:

Pipeline
Staging
Approval
Production

Only authorized deployments can proceed.


Deployment Gates

Deployment gates evaluate conditions before allowing deployment.

Common gates include:

  • Successful testing
  • Security scan completion
  • Vulnerability assessment
  • Performance validation
  • Business approval
  • Service availability

Example:

Security Scan Passed?
Yes
Continue Deployment

If any gate fails, deployment stops automatically.


Authentication in Deployment Pipelines

Authentication verifies the identity of the pipeline when accessing resources.

The deployment pipeline may need to access:

  • SQL Server
  • Azure SQL Database
  • Azure Key Vault
  • Azure Storage
  • Azure AI Services
  • Microsoft Fabric
  • Azure OpenAI
  • Azure Resource Manager

Secure authentication is essential because deployment pipelines often operate without human intervention.


Authentication Methods

Common authentication methods include:

  • Microsoft Entra ID (Azure AD)
  • Managed Identity
  • Service Principal
  • OAuth tokens
  • Personal Access Tokens (PATs)
  • SQL Authentication (legacy scenarios)

Microsoft recommends avoiding passwords whenever possible.


Service Principals

A Service Principal represents an application identity in Microsoft Entra ID.

Instead of using a person’s account, the deployment pipeline authenticates using its own identity.

Example:

Pipeline
Service Principal
Microsoft Entra ID
Azure SQL Database

Benefits:

  • Non-interactive authentication
  • Fine-grained permissions
  • Centralized identity management
  • Easy auditing
  • Supports automation

Managed Identity

A Managed Identity is the preferred authentication method for Azure-hosted services.

Instead of storing credentials, Azure automatically manages authentication.

Example:

Azure DevOps Agent
Managed Identity
Microsoft Entra ID
Azure SQL Database

Advantages include:

  • No stored passwords
  • Automatic credential rotation
  • Improved security
  • Simplified administration
  • Reduced risk of credential leakage

Managed Identity is increasingly emphasized across Microsoft certifications, including DP-800.


System-Assigned vs User-Assigned Managed Identity

System-Assigned Managed Identity

Characteristics:

  • Tied to one Azure resource
  • Automatically created
  • Automatically deleted with the resource
  • Ideal for single-resource scenarios

Example:

App Service
System Managed Identity
Azure SQL

User-Assigned Managed Identity

Characteristics:

  • Independent Azure resource
  • Shared across multiple services
  • Longer lifecycle
  • Reusable

Example:

Managed Identity
Web App
Azure Function
Azure SQL Database

Useful when multiple applications require the same identity.


Least Privilege Principle

Deployment identities should have only the permissions necessary to perform deployments.

Avoid granting:

  • sysadmin
  • db_owner (unless required)
  • Subscription Owner
  • Global Administrator

Instead, assign only the permissions needed.

Example:

Deployment pipeline requires:

  • ALTER TABLE
  • CREATE PROCEDURE
  • CREATE VIEW

It does not require:

  • DROP DATABASE
  • Server Administration
  • Security Administration

Following the principle of least privilege reduces the impact of compromised credentials.


Secrets Management

Pipelines often require sensitive information, such as:

  • Connection strings
  • API keys
  • Certificates
  • Tokens
  • Database credentials

Hardcoding these values in source control is a major security risk.


Azure Key Vault

Azure Key Vault is the recommended solution for storing secrets.

Instead of embedding credentials:

Pipeline
Azure Key Vault
Retrieve Secret
Deploy Database

Benefits include:

  • Centralized secret storage
  • Encryption at rest
  • Access auditing
  • Role-based access control
  • Automatic secret rotation
  • Integration with Azure DevOps and GitHub Actions

Secure Pipeline Variables

CI/CD platforms support secure variables that:

  • Encrypt values
  • Hide secrets in logs
  • Restrict access
  • Limit modification permissions

Examples include:

  • SQL connection strings
  • Azure subscription IDs
  • API tokens
  • Storage account keys

Sensitive values should never be committed to a Git repository.


Environment-Specific Configuration

Different deployment environments often require different configuration values.

Example:

EnvironmentDatabase
DevelopmentDevDB
TestingTestDB
StagingStageDB
ProductionProdDB

Pipelines should dynamically retrieve the correct configuration for each environment rather than hardcoding values.


Deployment Strategies

Different deployment strategies reduce downtime and deployment risk.

Common strategies include:

Incremental Deployment

Deploy only changed objects.

Advantages:

  • Faster deployments
  • Lower risk
  • Reduced downtime

Rolling Deployment

Deploy changes gradually across multiple instances.

Useful for:

  • High availability
  • Large distributed systems

Blue-Green Deployment

Maintain two production environments.

Blue Environment
(Current)
Switch
Green Environment
(New Version)

Advantages:

  • Minimal downtime
  • Fast rollback
  • Lower deployment risk

Canary Deployment

Deploy to a small subset of users first.

If successful:

5%
25%
50%
100%

This strategy helps identify issues before a full rollout.


Best Practices for Secure Deployment Pipelines

Microsoft recommends the following practices:

  • Automate builds whenever possible.
  • Require approvals for production deployments.
  • Use Microsoft Entra ID authentication.
  • Prefer Managed Identity over stored credentials.
  • Store secrets in Azure Key Vault.
  • Apply least privilege permissions.
  • Protect production environments with approval gates.
  • Separate development, test, staging, and production environments.
  • Monitor deployment history and audit logs.
  • Validate deployments before promotion to production.

DP-800 Exam Tips

For the exam, be prepared to identify when to use:

  • Pull request triggers versus commit triggers.
  • Manual approvals for production deployments.
  • Managed Identity instead of passwords or embedded credentials.
  • Service Principals for automated, non-interactive deployments.
  • Azure Key Vault for secure secrets management.
  • Environment protection rules to safeguard production resources.
  • Least privilege permissions for deployment identities.
  • Appropriate deployment strategies such as blue-green, rolling, or incremental deployments based on business requirements.

Part 2 Summary

In this section, you learned how organizations secure and automate SQL deployment pipelines through:

  • Pipeline triggers and CI/CD automation
  • Manual and conditional deployment approvals
  • Environment protection and deployment gates
  • Authentication using Microsoft Entra ID
  • Service Principals and Managed Identity
  • Least privilege access
  • Secrets management with Azure Key Vault
  • Secure pipeline variables
  • Environment-specific configuration
  • Common deployment strategies
  • Microsoft-recommended security and governance practices

Go to the DP-800 Exam Prep Hub main page

Design and implement controls for deployment pipelines, including branching policies, triggers in approvals, authentication tables, and code owners – Part 1 (DP-800 Exam Prep)

This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Secure, optimize, and deploy database solutions (35–40%)
   --> Implement CI/CD by using SQL Database Projects
      --> Design and implement controls for deployment pipelines, including branching policies, triggers in approvals, authentication tables, and code owners


Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

Microsoft expects SQL AI Developers to understand not only how to develop database solutions but also how to deploy them safely, consistently, and securely using modern DevOps practices.

Organizations rarely allow developers to deploy SQL changes directly into production. Instead, database changes pass through controlled deployment pipelines that validate the code, enforce security policies, require approvals, and ensure only authorized changes reach production.

Modern SQL development emphasizes:

  • Source-controlled database projects
  • Automated builds
  • Automated testing
  • Controlled deployments
  • Secure authentication
  • Governance through branching policies and approvals
  • Auditable deployment history

Understanding these concepts is essential for both the DP-800 exam and real-world enterprise database development.


What Are Deployment Pipeline Controls?

Deployment pipeline controls are rules and processes that ensure database changes move safely from development to production.

Instead of allowing developers to make direct changes to production databases, organizations require every change to follow a controlled workflow.

A typical workflow looks like this:

Developer
Feature Branch
Pull Request
Code Review
Automated Build
Unit Tests
Integration Tests
Approval
Deployment Pipeline
Development
Test
Staging
Production

Each stage reduces the risk of introducing errors into production.


Why Deployment Controls Matter

Without deployment controls, organizations often experience:

  • Accidental schema changes
  • Lost database objects
  • Unauthorized modifications
  • Production outages
  • Failed deployments
  • Data corruption
  • Compliance violations
  • Security risks

Deployment controls provide:

  • Consistency
  • Repeatability
  • Security
  • Governance
  • Auditability
  • Faster recovery
  • Higher software quality

For enterprise environments, these controls are considered mandatory.


SQL Database Projects and Deployment Pipelines

SQL Database Projects represent an entire database schema as source-controlled code.

Instead of modifying objects directly inside SQL Server Management Studio (SSMS), developers modify project files.

Example:

Tables
Customers.sql
Orders.sql
Products.sql
Views
SalesView.sql
Stored Procedures
usp_CreateOrder.sql
Functions
fn_TotalSales.sql

The deployment pipeline compares the project against the target database and generates the necessary deployment script automatically.

Benefits include:

  • Version history
  • Repeatable deployments
  • Easier collaboration
  • Automated validation
  • Reduced deployment risk

CI/CD Overview

CI/CD stands for:

Continuous Integration (CI)

Developers frequently merge changes into a shared repository.

Every commit automatically triggers:

  • Build validation
  • SQL compilation
  • Static code analysis
  • Unit testing
  • Artifact creation

Example:

Developer Commit
Git Repository
Automatic Build
Database Project Build
Validation
Package Generated

Continuous Delivery (CD)

Continuous Delivery automates deployments through multiple environments.

Example:

Development
QA
Staging
Production

Each deployment can require approvals before continuing.

Benefits include:

  • Faster releases
  • Fewer deployment errors
  • Repeatable deployments
  • Reliable rollback strategies

Understanding Branching Strategies

Branching is one of the most important deployment controls.

A branch is an independent line of development inside source control.

Instead of every developer modifying the main branch directly, developers work in isolated branches.

Example:

Main
├── Feature A
├── Feature B
├── Bug Fix
└── Feature C

Each branch is reviewed before merging.


Why Branching Is Important

Branching allows developers to:

  • Work independently
  • Prevent conflicts
  • Test safely
  • Review code
  • Protect production code
  • Isolate unfinished features

Without branching:

  • Developers overwrite one another’s work.
  • Unfinished code reaches production.
  • Rollbacks become difficult.

Common Branching Strategies

Several branching strategies are commonly used.


Feature Branch Workflow

The most common approach.

Each new feature receives its own branch.

Example:

Main
├── feature/AddOrders
├── feature/AddInvoices
├── feature/SearchCustomers

Advantages:

  • Easy code review
  • Simple testing
  • Low risk
  • Small pull requests

This is one of the most common approaches for SQL Database Projects.


GitFlow

GitFlow introduces several branch types.

Main
Develop
Feature Branches
Release Branches
Hotfix Branches

Typical workflow:

Main
Develop
Feature Branch
Develop
Release
Main

Advantages:

  • Strong release management
  • Good for large teams
  • Stable production releases

Disadvantages:

  • More complex
  • Additional branch management

Trunk-Based Development

Developers merge frequently into a single shared branch.

Main
Developer 1
Developer 2
Developer 3
Developer 4

Developers create very short-lived branches.

Advantages:

  • Small changes
  • Faster integration
  • Less merge complexity

Disadvantages:

  • Requires excellent automated testing
  • Requires disciplined developers

Branch Protection Policies

Branch protection prevents unsafe changes.

The main branch is typically protected.

Developers cannot:

  • Force push
  • Delete the branch
  • Merge without approval
  • Merge failed builds
  • Bypass policies

Example policy:

Main Branch
✓ Build must succeed
✓ Two reviewers required
✓ No direct commits
✓ Status checks pass
✓ Linked work item required
✓ Up-to-date before merge

These policies dramatically reduce deployment mistakes.


Common Branch Protection Rules

Organizations often require:

Required Pull Requests

Direct commits are blocked.

Developers must create a pull request.


Required Reviewers

Example:

Minimum Reviewers = 2

Multiple reviewers reduce errors.


Successful Build Required

If automated validation fails, merging is blocked.

Example:

Build Failed
Merge Blocked

Required Status Checks

Policies verify that:

  • Unit tests passed
  • Integration tests passed
  • Security scans completed
  • SQL build succeeded
  • Code quality passed

Only then is the merge allowed.


Prevent Force Push

Force pushes rewrite Git history.

Most organizations disable them for protected branches.


Prevent Branch Deletion

Important branches should never be accidentally removed.

Branch protection prevents deletion.


Pull Requests (PRs)

A pull request requests permission to merge one branch into another.

Example:

Feature Branch
Pull Request
Review
Approval
Merge

A pull request usually includes:

  • Description
  • Changed files
  • SQL object modifications
  • Reviewer comments
  • Build status
  • Test results

Benefits of Pull Requests

Pull requests improve quality by encouraging:

  • Peer review
  • Knowledge sharing
  • Early defect detection
  • Security review
  • Coding standard enforcement

For SQL projects, reviewers often examine:

  • Table changes
  • Index changes
  • Stored procedures
  • Permissions
  • Migration scripts
  • Performance impacts

Code Reviews

Code reviews help identify issues before deployment.

Reviewers commonly check:

Correctness

Does the SQL produce the expected results?

Performance

Are indexes appropriate?

Will queries scale?

Security

Are permissions appropriate?

Is SQL injection prevented?

Maintainability

Is the code readable?

Are naming standards followed?

Backward Compatibility

Will existing applications continue working?


Code Owners

One important governance feature is Code Owners.

A Code Owners file automatically assigns reviewers based on the files that change.

Example:

Tables/*
→ Database Team
StoredProcedures/*
→ Backend Team
Security/*
→ Security Team

When a developer modifies a protected object, the correct experts are automatically requested to review the change.

Benefits of Code Owners

Code Owners provide several advantages:

  • Automatic reviewer assignment
  • Faster review workflows
  • Consistent governance
  • Improved accountability
  • Better code quality
  • Subject matter expert validation
  • Compliance with organizational policies

For example:

  • Changes to security-related scripts can require approval from the security team.
  • Changes to database schema objects can require approval from database administrators.
  • Changes to deployment scripts can require DevOps team approval.

This ensures that critical database components are always reviewed by the appropriate personnel before deployment.


Best Practices for Branching and Pull Requests

Microsoft recommends following modern DevOps practices when managing SQL Database Projects.

Some recommended best practices include:

  • Create small, focused feature branches.
  • Keep branches short-lived.
  • Merge changes frequently.
  • Require pull requests for protected branches.
  • Require successful builds before merging.
  • Require automated tests before deployment.
  • Require peer reviews.
  • Protect the main branch from direct commits.
  • Use Code Owners for sensitive database objects.
  • Document pull requests with clear descriptions.
  • Resolve merge conflicts promptly.
  • Use descriptive branch names such as:
    • feature/AddCustomerSearch
    • bugfix/FixDeadlockIssue
    • hotfix/CorrectCustomerIndex

Following these practices improves collaboration, reduces deployment risk, and helps maintain a reliable, auditable database development process.


Part 1 Summary

In this first part, you learned the foundational deployment pipeline controls that are central to modern SQL DevOps and the DP-800 exam:

  • The purpose of deployment pipeline controls
  • The role of SQL Database Projects in CI/CD
  • Continuous Integration (CI) and Continuous Delivery (CD)
  • Common branching strategies (Feature Branch, GitFlow, and Trunk-Based Development)
  • Branch protection policies and why they matter
  • Pull requests and peer code reviews
  • Code Owners and automated reviewer assignment
  • Best practices for secure and reliable database development

Go to the DP-800 Exam Prep Hub main page

Create triggers (DP-800 Exam Prep)

This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Design and develop database solutions (35–40%)
   --> Implement programmability objects
      --> Create triggers


Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

Triggers are special types of stored procedures that automatically execute (or “fire”) in response to specific database events. Unlike stored procedures, which must be executed explicitly by a user or application, triggers are invoked automatically by SQL Server when certain Data Manipulation Language (DML), Data Definition Language (DDL), or logon events occur.

Triggers are commonly used to enforce complex business rules, maintain audit trails, synchronize related data, validate changes, and perform automated actions that occur whenever data or database objects are modified. While triggers are powerful, they should be used judiciously because they can add complexity and affect database performance if not carefully designed.

For the DP-800: Developing AI-Enabled Database Solutions certification exam, you should understand:

  • What triggers are
  • DML triggers
  • DDL triggers
  • AFTER and INSTEAD OF triggers
  • The inserted and deleted logical tables
  • Creating, altering, disabling, enabling, and dropping triggers
  • Nested and recursive triggers
  • Performance considerations
  • Best practices
  • AI-enabled database scenarios

Understanding triggers is important because they provide automatic execution of business logic while helping maintain data integrity and automate administrative tasks.


What Is a Trigger?

A trigger is a database object that automatically executes when a specified event occurs.

Triggers are associated with:

  • Tables
  • Views
  • Databases
  • SQL Server instances (for certain DDL and logon events)

Triggers cannot be executed directly using the EXEC statement.

Instead, SQL Server executes them automatically when the triggering event occurs.


Types of Triggers

SQL Server supports several types of triggers:

  • DML triggers
  • DDL triggers
  • Logon triggers

The DP-800 exam primarily focuses on DML and DDL triggers.


DML Triggers

Data Manipulation Language (DML) triggers fire when data is modified.

They respond to:

  • INSERT
  • UPDATE
  • DELETE

Typical uses include:

  • Auditing data changes
  • Enforcing business rules
  • Validating updates
  • Synchronizing tables
  • Recording historical information

AFTER Triggers

An AFTER trigger executes only after the triggering statement completes successfully.

Example:

CREATE TRIGGER trgCustomerAudit
ON Sales.Customers
AFTER INSERT
AS
BEGIN
INSERT INTO Sales.CustomerAudit
(
CustomerID,
AuditDate
)
SELECT
CustomerID,
GETDATE()
FROM inserted;
END;

The trigger records newly inserted customers after the insert operation succeeds.


INSTEAD OF Triggers

An INSTEAD OF trigger executes in place of the triggering action.

Example:

CREATE TRIGGER trgPreventDelete
ON Sales.Customers
INSTEAD OF DELETE
AS
BEGIN
PRINT 'Deleting customers is not permitted.';
END;

The DELETE statement never executes because the trigger replaces it.

INSTEAD OF triggers are commonly used on:

  • Views
  • Complex update scenarios
  • Custom validation logic

DDL Triggers

DDL triggers respond to schema changes.

Common events include:

  • CREATE TABLE
  • ALTER TABLE
  • DROP TABLE
  • CREATE PROCEDURE
  • ALTER PROCEDURE
  • DROP PROCEDURE

Example:

CREATE TRIGGER trgAuditDDL
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
PRINT 'A table was created.';
END;

DDL triggers help monitor or prevent unauthorized schema modifications.


Logon Triggers

Logon triggers execute when a user establishes a SQL Server session.

Typical uses include:

  • Restricting connections
  • Recording login activity
  • Enforcing security policies

Logon triggers are created at the server level and are not supported in Azure SQL Database.


The inserted Logical Table

Whenever rows are inserted or updated, SQL Server creates a temporary logical table named inserted.

It contains the new version of affected rows.

Example:

SELECT *
FROM inserted;

The inserted table exists only during trigger execution.


The deleted Logical Table

Whenever rows are deleted or updated, SQL Server creates a logical table named deleted.

It contains the original version of affected rows.

Example:

SELECT *
FROM deleted;

For UPDATE operations:

  • deleted contains old values.
  • inserted contains new values.

Auditing Changes

Triggers are frequently used to create audit trails.

Example:

CREATE TRIGGER trgAuditSalary
ON HumanResources.Employees
AFTER UPDATE
AS
BEGIN
INSERT INTO HumanResources.SalaryAudit
(
EmployeeID,
OldSalary,
NewSalary,
ChangeDate
)
SELECT
d.EmployeeID,
d.Salary,
i.Salary,
GETDATE()
FROM deleted d
INNER JOIN inserted i
ON d.EmployeeID = i.EmployeeID;
END;

This trigger records salary changes for auditing purposes.


Enforcing Business Rules

Triggers can prevent invalid operations.

Example:

CREATE TRIGGER trgNoNegativeInventory
ON Inventory.Products
AFTER UPDATE
AS
BEGIN
IF EXISTS
(
SELECT *
FROM inserted
WHERE Quantity < 0
)
BEGIN
RAISERROR
(
'Inventory cannot be negative.',
16,
1
);
ROLLBACK TRANSACTION;
END;
END;

The trigger rolls back the transaction if inventory becomes negative.


Multi-Row Operations

Triggers execute once per SQL statement, not once per affected row.

For example:

UPDATE Sales.Customers
SET City = 'Miami';

If 10,000 rows are updated, the trigger executes only once.

The inserted and deleted tables contain all affected rows.

Developers should always write triggers using set-based logic, not assumptions that only one row is affected.


Nested Triggers

A trigger can cause another trigger to fire.

Example:

  • Trigger A updates Table B.
  • Table B has Trigger B.
  • Trigger B executes automatically.

This behavior is called nested triggers.

SQL Server supports nested triggers up to a configurable limit.


Recursive Triggers

A recursive trigger fires itself either directly or indirectly.

Example:

  • Trigger updates its own table.
  • That update causes the same trigger to execute again.

Recursive triggers are disabled by default in many environments and should be used with caution to avoid infinite loops.


Enabling and Disabling Triggers

Disable a trigger:

DISABLE TRIGGER trgCustomerAudit
ON Sales.Customers;

Enable it:

ENABLE TRIGGER trgCustomerAudit
ON Sales.Customers;

Disabling a trigger preserves its definition while preventing it from firing.


Modifying a Trigger

Use ALTER TRIGGER.

Example:

ALTER TRIGGER trgCustomerAudit
ON Sales.Customers
AFTER INSERT
AS
BEGIN
PRINT 'Customer inserted.';
END;

Deleting a Trigger

Use:

DROP TRIGGER trgCustomerAudit;

Viewing Trigger Definitions

Developers can inspect a trigger using:

sp_helptext 'trgCustomerAudit';

Or:

SELECT OBJECT_DEFINITION
(
OBJECT_ID('trgCustomerAudit')
);

Triggers vs. Stored Procedures

FeatureTriggerStored Procedure
Executes automaticallyYesNo
Invoked by EXECNoYes
Responds to database eventsYesNo
Accepts parametersNoYes
Returns result setsNot intended for callersYes

Triggers vs. Constraints

FeatureTriggerConstraint
Enforces simple rulesPossibleYes
Enforces complex business logicYesLimited
Can reference multiple tablesYesLimited
Executes automaticallyYesYes

Constraints should generally be preferred for simple validation rules because they are simpler and often more efficient.


Performance Considerations

Triggers execute within the same transaction as the triggering statement.

Poorly designed triggers can:

  • Increase transaction duration
  • Increase locking
  • Reduce concurrency
  • Consume additional CPU resources
  • Introduce blocking
  • Increase deadlock risk

Best practices include:

  • Keep trigger logic simple.
  • Use set-based operations.
  • Avoid unnecessary queries.
  • Avoid long-running operations.
  • Minimize external dependencies.
  • Do not assume only one row is affected.

Security Considerations

Triggers can:

  • Audit sensitive changes
  • Prevent unauthorized updates
  • Enforce compliance policies
  • Record administrative activity
  • Restrict schema modifications using DDL triggers

Proper permissions should be applied because trigger code executes in the database context.


AI-Enabled Database Scenarios

Triggers can support AI-enabled database solutions by automating actions whenever data changes.

Examples include:

  • Recording changes that require new embeddings to be generated
  • Logging modifications to AI training datasets
  • Flagging rows for downstream vectorization processes
  • Updating AI metadata tables after inserts or updates
  • Capturing prompt history for auditing
  • Initiating workflows that prepare data for intelligent search or Retrieval-Augmented Generation (RAG)

Although triggers cannot directly invoke external AI services, they can populate work queues or status tables that downstream applications or services process.


Best Practices

  • Prefer constraints for simple validation.
  • Use triggers only when automatic behavior is required.
  • Write triggers using set-based logic.
  • Minimize execution time.
  • Avoid recursive logic unless absolutely necessary.
  • Test triggers with multi-row operations.
  • Document business rules implemented by triggers.
  • Avoid unnecessary nested trigger chains.
  • Monitor trigger performance.
  • Audit only the information that is required.

Common Exam Tips

For the DP-800 exam, remember these key points:

  • Triggers execute automatically in response to events.
  • DML triggers respond to INSERT, UPDATE, and DELETE statements.
  • DDL triggers respond to schema changes.
  • AFTER triggers execute after the triggering statement completes successfully.
  • INSTEAD OF triggers replace the triggering action.
  • inserted contains new row values.
  • deleted contains original row values.
  • Triggers fire once per statement, not once per row.
  • Use ALTER TRIGGER to modify a trigger.
  • Use DISABLE TRIGGER, ENABLE TRIGGER, and DROP TRIGGER to manage trigger lifecycle.

Practice Exam Questions

Question 1

A developer wants database logic to execute automatically whenever rows are inserted into a table. Which database object should be used?

A. Stored procedure

B. Trigger

C. View

D. Scalar function

Answer: B

Explanation: Triggers automatically execute in response to specified database events such as INSERT, UPDATE, or DELETE operations.


Question 2

Which type of trigger executes only after the triggering statement has completed successfully?

A. BEFORE trigger

B. INSTEAD OF trigger

C. AFTER trigger

D. LOGON trigger

Answer: C

Explanation: An AFTER trigger fires only after the triggering DML statement has completed successfully and any associated constraints have been processed.


Question 3

During an UPDATE operation, which logical table contains the original values of the modified rows?

A. inserted

B. updated

C. original

D. deleted

Answer: D

Explanation: During an UPDATE, the deleted logical table contains the original row values, while the inserted table contains the new values.


Question 4

Which trigger type replaces the original INSERT, UPDATE, or DELETE operation?

A. AFTER trigger

B. DDL trigger

C. INSTEAD OF trigger

D. Recursive trigger

Answer: C

Explanation: An INSTEAD OF trigger executes instead of the triggering statement, allowing custom processing or validation.


Question 5

A trigger is written assuming that only one row is updated at a time. Why is this a problem?

A. SQL Server executes one trigger for every row.

B. Triggers always execute asynchronously.

C. Triggers execute once per SQL statement and may process many affected rows.

D. UPDATE statements cannot affect multiple rows.

Answer: C

Explanation: SQL Server fires DML triggers once per statement, so developers must use set-based logic to correctly process all affected rows.


Question 6

Which statement disables a trigger while preserving its definition?

A. REMOVE TRIGGER

B. DROP TRIGGER

C. ALTER TRIGGER

D. DISABLE TRIGGER

Answer: D

Explanation: DISABLE TRIGGER prevents a trigger from firing without deleting it, allowing it to be re-enabled later.


Question 7

Which statement best describes a DDL trigger?

A. It responds to changes in table data.

B. It responds to schema modification events such as CREATE, ALTER, or DROP statements.

C. It executes only during user logins.

D. It replaces the execution of stored procedures.

Answer: B

Explanation: DDL triggers respond to schema-related events, making them useful for auditing or preventing structural database changes.


Question 8

Which object is generally preferred for enforcing a simple rule such as ensuring a value is greater than zero?

A. AFTER trigger

B. CHECK constraint

C. DDL trigger

D. Stored procedure

Answer: B

Explanation: CHECK constraints are simpler, easier to maintain, and generally more efficient than triggers for straightforward validation rules.


Question 9

Which statement correctly describes nested triggers?

A. They occur only with DDL triggers.

B. They allow a trigger to execute dynamic SQL.

C. They occur when one trigger causes another trigger to fire.

D. They are required whenever inserted and deleted tables are referenced.

Answer: C

Explanation: Nested triggers occur when the actions performed by one trigger cause another trigger to execute.


Question 10

How can triggers support AI-enabled database solutions?

A. They automatically generate embeddings by calling AI models directly.

B. They replace vector indexes.

C. They eliminate the need for application code.

D. They automatically detect data changes and populate work queues, audit tables, or status records that downstream AI processes use to generate embeddings, update indexes, or prepare RAG data.

Answer: D

Explanation: Triggers are well suited for detecting data changes and initiating downstream workflows by recording changes or updating processing queues. External applications or services can then consume these queues to perform AI-related tasks such as embedding generation or intelligent indexing.


Go to the DP-800 Exam Prep Hub main page