What should you do?

You have a Microsoft SQL Server database named DB1. Nested and recursive triggers for DB1 are disabled. There is an existing DDL trigger named TableAudit in the database. The trigger captures the name of each table as the table is created.

You define a policy for the database by using SQL Server policy-based management. The policy requires that all table names use the prefix table. You set the evaluation mode for the policy to On change: prevent.

You observe that developers are able to add new tables that do not include the required prefix in the table name.

You need to ensure the policy is enforced.

What should you do?
A . Disable the TableAudit trigger.
B . Enable nested triggers.
C . Change the policy evaluation mode to On Schedule.
D . Enable recursive triggers.

View Answer

Answer: B

Explanation:

If the nested triggers server configuration option is disabled, On change: prevent will not work correctly. Policy-Based Management relies on DDL triggers to detect and roll back DDL operations that do not comply with policies that use this evaluation mode. Removing the Policy-Based Management DDL triggers or disabling nest triggers, will cause this evaluation mode to fail or perform unexpectedly.

References: https://docs.microsoft.com/en-us/sql/relational-databases/policy-based-management/administer­servers-by-using-policy-based-management