SQL Triggers MCQ Questions and Answers
This section focuses on the "Triggers" in SQL. These Multiple Choice Questions (mcq) should be practiced to improve the SQL skills required for various interviews (campus interview, walk-in interview, company interview), placement, entrance exam and other competitive examinations.
1. Which of the following specifies when the trigger will be executed?
A. BEFORE
B. AFTER
C. INSTEAD OF
D. All of the above
View Answer
Ans : D
Explanation: {BEFORE | AFTER | INSTEAD OF} − This specifies when the trigger will be executed. The INSTEAD OF clause is used for creating trigger on a view.
2. Which of the operation are not specifies in triggers?
A. Alter
B. UPDATE
C. INSERT
D. DELETE
View Answer
Ans : A
Explanation: {INSERT [OR] | UPDATE [OR] | DELETE} − This specifies the DML operation
3. How can we specifies a row-level trigger?
A. Using ON ROW
B. Using FOR EACH COL
C. Using FOR EACH ROW
D. Using OR ROW
View Answer
Ans : C
Explanation: [FOR EACH ROW] − This specifies a row-level trigger, i.e., the trigger will be executed for each row being affected. Otherwise the trigger will execute just once when the SQL statement is executed, which is called a table level trigger.
4. OLD and NEW references are available for table-level triggers
A. TRUE
B. FALSE
C. Can be true or false
D. Can not say
View Answer
Ans : B
Explanation: OLD and NEW references are not available for table-level triggers, rather you can use them for record-level triggers
5. Which of the following are Benefits of Triggers?
A. Enforcing referential integrity
B. Auditing
C. Imposing security authorizations
D. All of the above
View Answer
Ans : D
Explanation: All of the above are Benefits of Triggers.
6. Which of the following triggers fire in response to the LOGON event that's raised when a user's session is being established?
A. Recursive Triggers
B. Multiple Triggers
C. Logon Trigger
D. Both A and C
View Answer
Ans : C
Explanation: Logon triggers carry out stored procedures in response to a LOGON event. This event happens when a user session is established with an instance of SQL Server.
7. How many types of recursion occure when Recursive triggers enable?
A. 2
B. 3
C. 4
D. 5
View Answer
Ans : A
Explanation: Recursive triggers enable the following types of recursion to occur:Indirect recursion and Direct recursion.
8. In which type of recursion the application updates table T1. This fires trigger TR1, updating table T1. Because table T1 was updated, trigger TR1 fires again, and so on?
A. Indirect recursion
B. Direct recursion
C. Both A and B
D. None of the above
View Answer
Ans : B
Explanation: Indirect recursion:With indirect recursion, an application updates table T1. This fires trigger TR1, updating table T2. Trigger T2 then fires and updates table T1.
Direct recursion :In direct recursion, the application updates table T1. This fires trigger TR1, updating table T1. Because table T1 was updated, trigger TR1 fires again, and so on.
9. SQL Server allows for Transact-SQL stored procedures, triggers, and batches to refer to tables that don't exist at compile time. This ability is called?
A. Indeferred Name Resolution
B. Deferred Name Permissions
C. Deferred Name Resolution
D. Indeferred Name Permissions
View Answer
Ans : C
Explanation: SQL Server allows for Transact-SQL stored procedures, triggers, and batches to refer to tables that don't exist at compile time. This ability is called deferred name resolution.
10. Trigger isn&aspo;t called recursively when?
A. If an INSTEAD OF trigger defined on a table runs a statement against the table that would ordinarily fire the INSTEAD OF trigger again
B. When an INSTEAD OF trigger defined on a view runs a statement against the view that would ordinarily fire the INSTEAD OF trigger again
C. Both A and B
D. None of the above
View Answer
Ans : C
Explanation: For both a and b Trigger isn&aspo;t called recursively.
Discussion