SQL-Server Triggers - Quiz Explanation
The correct answers are indicated below, along with text that explains the correct answers.
1.
By default, how many levels can triggers be nested?
Please select the best answer.
A.
1.
B.
32
C.
16
D.
Zero; they cannot be nested.
The correct answer is C.
By default, triggers can be nested up to 16 levels. A is incorrect because although you can use the
sp_configure
system stored procedure to allow only one level, this is not the default. B is incorrect because you can never have more than 16 levels. D is incorrect because this is not the default level, even though it can be set by using the
sp_configure
system stored procedure.
2.
When does the Deleted temporary trigger table become populated?
Please select the best answer.
A.
With DELETE triggers only
B.
With DELETE and INSERT triggers
C.
With DELETE, UPDATE, and INSERT triggers
D.
With DELETE and UPDATE triggers
The correct answer is D.
DELETE and UPDATE triggers populate the Deleted special table. A is incorrect because UPDATE triggers also populate the Deleted table. B is incorrect because INSERT triggers do not populate the Deleted table. C is incorrect because INSERT triggers do not populate the Deleted table.
3.
John creates a trigger on the Sales table.
He is trying to enforce the business rule that no order can be created (inserted) with a total less than $20. However, it can be updated by a supervisor, if necessary. What must his code do to the Sales table if the test inside the trigger tries to insert an order with a total of $15?
Please select the best answer.
A.
Delete the row from the Inserted table.
B.
Delete the row from the Sales table.
C.
Do nothing; the trigger will do it automatically.
D.
Delete the row from both the Inserted and the Sales tables.
The correct answer is B.
He needs to delete the data from the Sales table if the rule fails. A is incorrect because the Inserted table can never have any action performed on it other than a
SELECT
statement. C is incorrect because the trigger cannot know what to do if John doesn’t code for it. D is incorrect because, again, the Inserted table can only have data selected from it.
4.
If you insert data into the Category table that has an INSERT trigger on it, what happens to the data when the trigger fires?
Please select the best answer.
A.
The data resides in the Inserted and the Category tables.
B.
The data resides in only the Inserted table.
C.
The data resides in only the Category table.
D.
The data resides in the Inserted and Deleted tables.
The correct answer is A.
The data resides in the Inserted and the Category tables. B is incorrect because the data also resides in the Category table. C is incorrect because the data also resides in the Inserted table. D is incorrect because only UPDATE triggers affect both the Inserted and Deleted tables.
5.
If you delete data from the Category table that has a DELETE trigger on it, what happens to the data when the trigger fires?
Please select the best answer.
A.
The data resides in the Deleted table and is removed from the Category table.
B.
The data resides in the Inserted and Deleted tables.
C.
The data is deleted from the Category table only.
D.
The data resides in only the Deleted table.
The correct answer is D.
The data resides only in the Deleted table. The data is not actually deleted from the Category table yet. A is incorrect because the data is not removed from the Category table. B is incorrect because only UPDATE triggers affect both the Inserted and the Deleted tables. C is incorrect because the data is not removed from the Category table.