Exam 70-762 All Questions

View all questions & answers for the 70-762 exam

Exam 70-762 topic 1 question 78 discussion

Actual exam question from Microsoft's 70-762

Question #: 78
Topic #: 1

DRAG DROP -
You have a trigger named CheckTriggerCreation that runs when a user attempts to create a trigger. The CheckTriggerCreation trigger was created with the
ENCRYPTION option and additional proprietary business logic.
You need to prevent users from running the ALTER and DROP statements or the sp_tableoption stored procedure.
Which three Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-
SQL segments to the answer area and arrange them in the correct order.
Select and Place:

Show Suggested Answer

Suggested Answer:

by Cageman at Jan. 9, 2020, 7:32 p.m.

Comments

Submit Cancel

Cageman

Highly Voted 5 years, 5 months ago

second must be: alter_tabel drop_table

upvoted 24 times

Nelly100

5 years, 2 months ago

Its correct as is... please don't mislead people here

upvoted 2 times

mch185

4 years, 5 months ago

hgi is right . DDL_TABLE8EVENTS includes CREATE_TABLE. it is not useful here

upvoted 2 times

...

hgi

5 years, 2 months ago

won't it prevent and the CREATE statement if DDL_TABLE_EVENTS is used? In the scenario it says only ALTER and DROP.

upvoted 9 times

...

Anette

5 years ago

I think you are right and To execute sp_tableoption requires ALTER permission on the table. Also, FOR DDL_TABLE_EVENTS (10018) covers the CREATE TABLE, ALTER TABLE and DROP TABLE Transact-SQL statements. But we are only interested in alter and drop not create.

upvoted 7 times

...

bsciprian

5 years, 4 months ago

Indeed, to execute sp_tableoption requires ALTER permission on the table. https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-tableoption-transact-sql?view=sql-server-ver15

upvoted 3 times

...

MML

Highly Voted 4 years, 11 months ago

In my opinion the answer should like this: 1) Disable Trigger CheckTriggerCreation .. 2) Create Trigger CheckTableChanges on Database for Alter_Table, Drop_Table 3) Enable Trigger CheckTriggerCreation ..

upvoted 7 times

Sebastian90

4 years, 7 months ago

Disable does not equal Drop, so if you do that it will throw an error that there is already a trigger with that name. Basically you should do this: 1)drop trigger 2)create trigger 3)enable trigger --this step is redundant because it already is enabled upon creation

upvoted 1 times

giuPigna

4 years, 7 months ago

No, check the name of the new trigger. 1. Disable existing trigger CheckTriggerCreation 2. Create new trigger CheckTABLEChanges (different name) for Alter_Table, Drop_Table 3. Enable existing trigger CheckTriggerCreation

upvoted 6 times

...

TheDUdeu

Most Recent 4 years, 6 months ago

Second statement should be alter drop all the rest is fine.

upvoted 1 times

...

stm22

4 years, 11 months ago

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-tableoption-transact-sql?view=sql-server-ver15 says "sp_tableoption requires ALTER permission on the table" so i think alter_table drop_table is the most accurate.

upvoted 2 times

...

stm22

4 years, 11 months ago

https://docs.microsoft.com/en-us/sql/relational-databases/triggers/ddl-triggers?view=sql-server-ver15#:~:text=Types%20of%20DDL%20Triggers,-Transact%2DSQL%20DDL&text=A%20special%20type%20of%20Transact,deleted%20by%20using%20DROP%20TABLE.: has example: CREATE TRIGGER safety ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT 'You must disable Trigger "safety" to drop or alter tables!' ROLLBACK;

upvoted 2 times

...

LeonLeon

4 years, 11 months ago

If you have to modify the definition of a DDL trigger, you can either drop and re-create the trigger or redefine the existing trigger in a single step. https://docs.microsoft.com/en-us/sql/relational-databases/triggers/implement-ddl-triggers?view=sql-server-ver15 When DDL triggers are created, they are enabled by default. https://docs.microsoft.com/en-us/sql/relational-databases/triggers/implement-ddl-triggers?view=sql-server-ver15 So, it’s Disable, Drop, Create in this example

upvoted 2 times

LeonLeon

4 years, 11 months ago

To execute sp_tableoption requires ALTER permission on the table. https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-tableoption-transact-sql?view=sql-server-ver15 So indeed, Create and Alter_table are enough instead of DDL_TABLE_EVENTS

upvoted 1 times

...

Oooo

4 years, 8 months ago

Please read the question properly. we do not want to enable or disable the trigger that we are creating. There is already an existing trigger which prevents us from creating a new trigger and as we have a requirement to create a new trigger, we are just disabling the other trigger and enabling it once we create our trigger.

upvoted 2 times

...