SQL Triggers MCQ Questions and Answers
Triggers MCQs : 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.
11. The variables in the triggers are declared using
A. -
B. @
C. #
D. $
View Answer
Ans : B
Explanation: Example : declare @empid int; where empid is the variable.
12. The default extension for an Oracle SQL*Plus file is:
A. .txt
B. .html
C. .css
D. .sql
View Answer
Ans : D
Explanation: The default extension for an Oracle SQL*Plus file is .sql
13. What are the different in triggers?
A. Define, Create
B. Drop, Comment
C. Insert, Update, Delete
D. All of the mentioned
View Answer
Ans : C
Explanation: Triggers are not possible for create, drop.
14. Which prefixes are available to Oracle triggers?
A. : new only
B. : old only
C. Both A and B
D. None of the above
View Answer
Ans : C
Explanation: Both A and B prefixes are available to Oracle triggers.
15. Triggers ________ enabled or disabled
A. Can be
B. Cannot be
C. Ought to be
D. Always
View Answer
Ans : A
Explanation: Triggers can be manipulated
16. Trigger is special type of __________ procedure.
A. Function
B. Stored
C. View
D. Table
View Answer
Ans : B
Explanation: Triggers are used to assess/evaluate data before or after data modification using DDL and DML statements.
17. Point out the correct statement.
A. Triggers are database object
B. Three types of triggers are present in SQL Server
C. A DDL trigger is an action programmed to execute when a data manipulation language (DML) event occurs in the database server
D. None of the mentioned
View Answer
Ans : A
Explanation: Triggers are special type of stored procedure that automatically executes when a DDL or DML statement associated with the trigger is executed.
18. How many types of triggers are present in SQL Server?
A. 2
B. 3
C. 4
D. 5
View Answer
Ans : C
Explanation: In Sql Server we can create four types of triggers Data Definition Language (DDL) triggers, Data Manipulation Language (DML) triggers, CLR triggers and Logon triggers.
19. How many types of DML triggers are present in SQL Server?
A. 4
B. 5
C. 6
D. 7
View Answer
Ans : B
Explanation: We have two types of DML triggers-AFTER and INSTEAD OF.
20. Point out the wrong statement.
A. We can have an INSTEAD OF insert/update/delete trigger on a table that successfully executed
B. DML Triggers are used to evaluate data after data manipulation using DML statements
C. INSTEAD OF triggers cause their source DML operation to skip
D. AFTER triggers cause their source DML operation to skip
View Answer
Ans : D
Explanation: INSTEAD OF triggers cause their source DML operation to skip and they just execute the code provided inside them.
Discussion