DP-800 practice question 2 of 15

You have an Azure SQL database That contains database-level Data Definition Language (DDL) triggers, including a trigger named ddl_Audit. You need to prevent ddl_Audit from firing during the next deployment. The trigger object must remain in place. Which Transact-SQL statement should you use?

Full set: 61 questions
Answer options
A: ALTER TRIGGER
B: ALTER DATABASE
C: ALTER SERVER AUDIT SPECIFICATION
D: DISABLE TRIGGER Correct answer
E: ALTER DATABASE AUDIT SPECIFICATION
Correct Answer:
D. DISABLE TRIGGER
Explanation

The DISABLE TRIGGER Transact-SQL statement is specifically designed to make a trigger inactive without removing its definition from the database. This directly meets the requirement to prevent the ddlAudit trigger from firing during a deployment while ensuring the trigger object remains in place. After the deployment is complete, the trigger can be re-enabled using the ENABLE TRIGGER statement. This provides a controlled way to manage trigger execution during maintenance or deployment windows.

Why Incorrect

A. ALTER TRIGGER is used to modify the code or definition of the trigger, not to enable or disable its execution.
B. ALTER DATABASE is used for modifying database-level properties and does not provide an option to disable a specific trigger.
C. ALTER SERVER AUDIT SPECIFICATION is a command related to the SQL Server Audit feature, which is separate from DDL triggers.
E. ALTER DATABASE AUDIT SPECIFICATION is also part of the SQL Server Audit feature and is not used to manage DDL triggers.

References

1. Microsoft. (2024). DISABLE TRIGGER (Transact-SQL). Microsoft Learn. Retrieved from https://learn.microsoft.com/en-us/sql/t-sql/statements/disable-trigger-transact-sql?view=sql-server-ver16. The documentation states, "Disables a trigger. A disabled trigger still exists as an object in the current database, but it does not fire when any Transact-SQL statements on which it was programmed are run." This perfectly describes the required action.

2. Microsoft. (2024). DDL Triggers. Microsoft Learn. Retrieved from https://learn.microsoft.com/en-us/sql/relational-databases/triggers/ddl-triggers?view=sql-server-ver16. This page describes the management of DDL triggers, including enabling and disabling them as part of their lifecycle.

Scroll to Top