All 7 Triggers questions
More in Triggers
What is a trigger in SQL Server, and what kinds of events can cause one to run automatically? How do you access the old and new values of a row that was just updated from inside a trigger? Why should triggers generally be designed to handle multiple affected rows at once rather than assuming only a single row was changed? What are some alternatives to using a trigger for tasks like auditing or enforcing business rules, and when might those alternatives be preferable? What are the performance implications of using triggers heavily on a busy, high transaction volume table? How do nested and recursive triggers work in SQL Server, and what precautions should be taken to avoid unexpected behavior or infinite loops?
Advertisement
SQL Server › Triggers

What is the difference between an AFTER trigger and an INSTEAD OF trigger?

Beginner

Answer

An AFTER trigger runs once the triggering action, like an insert or update, has already completed, letting you perform additional actions based on the change that just happened, while an INSTEAD OF trigger completely replaces the original triggering action, giving you full control to perform custom logic instead of the default behavior that would have otherwise occurred.
CREATE TRIGGER trg_PreventDelete
ON Orders
INSTEAD OF DELETE
AS
BEGIN
  UPDATE Orders SET IsDeleted = 1
  WHERE OrderId IN (SELECT OrderId FROM deleted);
END;
Real-world example An order system uses an INSTEAD OF DELETE trigger to implement soft deletes, marking orders as deleted with a flag instead of actually removing them from the table, preserving the historical record while still appearing deleted to the application.

Common follow-ups: Why would a team choose soft deletes over actually removing rows from a table?;Can an INSTEAD OF trigger be created on a view as well as a table?

Data Types & Schema Design;Views
All 7 Triggers questions