MCQs on Triggers | SQL Server

Learn SQL Server Triggers, their functionality, and practical implementation. Covering AFTER and INSTEAD OF Triggers, DDL Triggers, and essential best practices to manage database operations effectively.

---

MCQs on Triggers in SQL Server

Section 1: Introduction to Triggers

1. What is a trigger in SQL Server?
   • A) A stored procedure that is called explicitly
   • B) A database object automatically executed in response to an event
   • C) A manual database operation
   • D) None of the above
2. Triggers are associated with which database operations?
   • A) SELECT
   • B) INSERT, UPDATE, DELETE
   • C) JOIN
   • D) GROUP BY
3. What type of table stores the changes made during a trigger execution?
   • A) Temporary table
   • B) Virtual table
   • C) Static table
   • D) Persistent table
4. The virtual table INSERTED contains:
   • A) The data before the operation
   • B) The data after the operation
   • C) Both before and after data
   • D) Only metadata
5. Can multiple triggers be defined on the same table for the same event?
   • A) Yes
   • B) No
   • C) Only for UPDATE
   • D) Only for DELETE

---

Section 2: AFTER and INSTEAD OF Triggers

6. An AFTER trigger is executed:
   • A) Before the triggering operation
   • B) After the triggering operation
   • C) In place of the triggering operation
   • D) Independently of the triggering operation
7. What is the difference between AFTER and INSTEAD OF triggers?
   • A) AFTER triggers replace the original operation
   • B) INSTEAD OF triggers occur after the original operation
   • C) AFTER triggers occur post-operation, INSTEAD OF triggers replace the operation
   • D) No difference
8. In the following code, what does the trigger do?
   CREATE TRIGGER trgExample ON Employees AFTER INSERT AS PRINT 'New Employee Added';
   • A) Prevents employee insertion
   • B) Executes after an employee is inserted
   • C) Replaces the INSERT operation
   • D) Executes before an employee is inserted
9. INSTEAD OF triggers are used to:
   • A) Prevent an operation from occurring
   • B) Replace the execution of an operation
   • C) Log operations asynchronously
   • D) Perform an action after the operation
10. Which operations support INSTEAD OF triggers in SQL Server?
    • A) SELECT only
    • B) INSERT, UPDATE, DELETE
    • C) DDL commands
    • D) None

---

Section 3: DDL Triggers

11. DDL triggers are fired in response to:
    • A) Data manipulation commands (INSERT, UPDATE, DELETE)
    • B) Data definition commands (CREATE, ALTER, DROP)
    • C) Data retrieval commands (SELECT)
    • D) None of the above
12. What is the primary use of DDL triggers?
    • A) Enforcing referential integrity
    • B) Auditing and preventing schema changes
    • C) Creating temporary tables
    • D) Optimizing queries
13. Consider the following trigger:
    CREATE TRIGGER trgCreateTable ON DATABASE FOR CREATE_TABLE AS PRINT 'A table was created'; What does it do?
    • A) Tracks data manipulation
    • B) Logs the creation of tables in the database
    • C) Restricts table creation
    • D) Replaces the CREATE TABLE operation
14. Which statement is true about DDL triggers?
    • A) They only work at the server level
    • B) They work at both database and server levels
    • C) They are identical to DML triggers
    • D) They cannot log schema changes
15. How can you disable a DDL trigger in SQL Server?
    • A) Use ALTER TRIGGER
    • B) Use DISABLE TRIGGER
    • C) Drop the trigger
    • D) Use SET TRIGGER OFF

---

Section 4: Best Practices for Triggers

16. Which of the following is a best practice for triggers?
    • A) Use triggers for complex business logic
    • B) Avoid recursive triggers where possible
    • C) Use triggers to fetch data from external systems
    • D) Use triggers for reporting purposes
17. To prevent recursive triggers, you should:
    • A) Set ALLOW_RECURSIVE_TRIGGERS to ON
    • B) Set ALLOW_RECURSIVE_TRIGGERS to OFF
    • C) Avoid using AFTER triggers
    • D) Use nested stored procedures
18. Excessive use of triggers can lead to:
    • A) Improved database performance
    • B) Complexity and maintenance challenges
    • C) Faster query execution
    • D) Data inconsistency
19. What is a good alternative to triggers for complex workflows?
    • A) Cursors
    • B) Stored procedures or application logic
    • C) Views
    • D) Indexes
20. Which statement is true about trigger execution?
    • A) Triggers execute in isolation of transactions
    • B) Triggers execute within the transaction of the triggering statement
    • C) Triggers run asynchronously
    • D) Triggers cannot access virtual tables

---

Answer Key

Qno	Answer
1	B) A database object automatically executed in response to an event
2	B) INSERT, UPDATE, DELETE
3	B) Virtual table
4	B) The data after the operation
5	A) Yes
6	B) After the triggering operation
7	C) AFTER triggers occur post-operation, INSTEAD OF triggers replace the operation
8	B) Executes after an employee is inserted
9	B) Replace the execution of an operation
10	B) INSERT, UPDATE, DELETE
11	B) Data definition commands (CREATE, ALTER, DROP)
12	B) Auditing and preventing schema changes
13	B) Logs the creation of tables in the database
14	B) They work at both database and server levels
15	B) Use DISABLE TRIGGER
16	B) Avoid recursive triggers where possible
17	B) Set ALLOW_RECURSIVE_TRIGGERS to OFF
18	B) Complexity and maintenance challenges
19	B) Stored procedures or application logic
20	B) Triggers execute within the transaction of the triggering statement