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

Triggers vs. Constraints

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