SQL Server trigger types: Judge the statement: "SQL Server does not support the BEFORE trigger."
-
ACorrect
-
BIncorrect
-
CTrue only before SQL Server 2012
-
DTrue for tables but not for views
-
EDepends on database compatibility level
Answer
Correct Answer: Correct
Explanation
Introduction / Context: Trigger timing varies by DBMS. Some platforms support BEFORE and AFTER triggers; others provide different semantics. Knowing which types exist in SQL Server is a frequent certification and interview topic.
Given Data / Assumptions:
- We focus on Microsoft SQL Server T-SQL triggers.
- We include DML triggers on tables/views and INSTEAD OF triggers.
Concept / Approach: SQL Server supports AFTER triggers (which fire after the DML statement completes but within the same transaction) and INSTEAD OF triggers (which replace the firing DML on a view or table). SQL Server does not implement a BEFORE trigger that executes prior to the DML statement while still allowing the statement to proceed automatically.
Step-by-Step Solution:
AFTER trigger: CREATE TRIGGER trg ON dbo.T AFTER INSERT AS ...INSTEAD OF trigger: CREATE TRIGGER trg ON dbo.V INSTEAD OF UPDATE AS ...No BEFORE keyword or equivalent timing exists.Verification / Alternative check: Compare with other DBMSs: Oracle and PostgreSQL support BEFORE row-level triggers; MySQL supports BEFORE triggers. The absence in SQL Server is well documented.
Why Other Options Are Wrong:
- Incorrect: Conflicts with current T-SQL capabilities.
- Version/compatibility caveats: No version introduces true BEFORE triggers.
- Tables vs. views: Timing support does not change by object type in SQL Server.
Common Pitfalls: Mistaking INSTEAD OF triggers for BEFORE triggers; they do not “pre-validate then proceed automatically”—they replace the operation and must perform the intended changes explicitly.
Final Answer: Correct