The correct answers are indicated below, along with explanations describing how SQL Server triggers enforce business rules within relational database systems.
1.
Which type of trigger fires after the triggering SQL statement completes successfully?
A.
INSTEAD OF trigger
B.
AFTER trigger
C.
Recursive trigger
D.
DDL trigger
The correct answer is B.
An AFTER trigger executes after a data modification statement such as INSERT, UPDATE, or DELETE successfully completes.
AFTER triggers are commonly used to:
Enforce business rules
Audit data modifications
Validate data integrity
Update related tables
In contrast, an INSTEAD OF trigger replaces the original operation entirely. INSTEAD OF triggers are frequently used on views to control how updates to a view propagate to underlying base tables.
2.
Which trigger type allows you to replace the original INSERT, UPDATE, or DELETE operation?
A.
AFTER trigger
B.
DML trigger
C.
INSTEAD OF trigger
D.
DDL trigger
The correct answer is C.
An INSTEAD OF trigger intercepts a data modification operation and allows the developer to provide alternative logic.
For example, when a user attempts to update a complex view that joins multiple tables, an INSTEAD OF trigger can distribute the update across the appropriate base tables.
INSTEAD OF triggers therefore act as a mechanism for implementing controlled update logic on views or enforcing complex business rules.
3.
If a trigger detects invalid data during an INSERT operation, what is the recommended technique to cancel the operation?
A.
ROLLBACK TRANSACTION
B.
DELETE FROM inserted
C.
TRUNCATE TABLE
D.
DISABLE TRIGGER
The correct answer is A.
The recommended way to cancel an invalid data modification inside a trigger is to use:
ROLLBACK TRANSACTION
This cancels the entire data modification statement and maintains transactional consistency.
For example:
IF EXISTS(
SELECT 1
FROM inserted
WHERE order_total < 20
)
BEGIN
RAISERROR('Order total must be at least $20',16,1)
ROLLBACK TRANSACTION
END
Modern SQL Server trigger design emphasizes using transaction control rather than manually deleting rows after they have been inserted.
4.
When an UPDATE trigger fires in SQL Server, how are the inserted and deleted tables used?
A.
Only inserted contains rows
B.
Inserted contains new values and deleted contains old values
C.
Both contain identical rows
D.
Only deleted contains rows
The correct answer is B.
When an UPDATE operation occurs:
The deleted table contains the original row values.
The inserted table contains the updated row values.
Triggers frequently compare these tables to detect data changes.
Example pattern:
SELECT *
FROM inserted i
JOIN deleted d
ON i.product_id = d.product_id
This pattern allows the trigger to determine what data changed and enforce business rules accordingly.
5.
Why should SQL Server triggers be written using set-based logic instead of assuming a single row?
A.
Triggers only support batch processing
B.
Triggers cannot access table data
C.
Triggers execute outside transactions
D.
A single SQL statement may modify multiple rows
The correct answer is D.
SQL Server triggers fire once per statement, not once per row. This means that a single INSERT, UPDATE, or DELETE statement may affect multiple rows.
Because of this behavior, triggers must always use **set-based logic** that works with multiple rows stored in the inserted and deleted tables.
Incorrect trigger design might assume only one row exists:
SELECT @price = price FROM inserted
Correct trigger design evaluates all rows:
IF EXISTS(
SELECT 1
FROM inserted
WHERE price < 20
)
ROLLBACK TRANSACTION
Writing triggers in a set-oriented manner ensures that business rules remain correct regardless of how many rows a SQL statement modifies.