You need to ensure that tables are not dropped from your database. What should you do?

Question

Options

• ACreate a DDL trigger that contains COMMIT.
• BCreate a DML trigger that contains COMMIT.
• CCreate a DDL trigger that contains ROLLBACK.
• DCreate a DML trigger that contains ROLLBACK.

• A
  10% (5)
• B
  4% (2)
• C
  82% (41)
• D
  4% (2)

Explanation

DDL triggers can execute either when a DDL statement is executed or when the user logs on to the SQL Server instance. DDL triggers can be scoped at either the database or instance level. To scope a DDL trigger at the instance level, you use the ON ALL SERVER option. To scope a DDL trigger at the database level, you use the ON DATABASE option. The following is an example of a DDL trigger: CREATE TRIGGER tddl_tabledropalterprevent FOR DROP_TABLE, ALTER_TABLE PRINT 'You are attempting to drop or alter tables in production!' Almost all DDL commands run within the context of a transaction. Because a DDL trigger also runs within the same transaction context, any DDL statement running in the context of a transaction can be rolled back.

No community discussion yet for this question.