All 7 Triggers questions
More in Triggers
What is the difference between an AFTER trigger and an INSTEAD OF trigger? 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
Back to results for “Error Handling”
SQL Server › Triggers

What is a trigger in SQL Server, and what kinds of events can cause one to run automatically?

Beginner

Answer

A trigger is a special kind of stored procedure that automatically runs in response to a specific event, such as an INSERT, UPDATE, or DELETE happening on a table, or certain data definition events like creating a new table, letting you automatically enforce rules or perform additional actions whenever that event occurs.
CREATE TRIGGER trg_LogOrderChanges
ON Orders
AFTER UPDATE
AS
BEGIN
  INSERT INTO OrderAuditLog (OrderId, ChangedDate)
  SELECT OrderId, GETDATE() FROM inserted;
END;
Real-world example An order management system automatically logs every time an order is updated using a trigger, capturing a complete audit history without requiring the application code to remember to log each change manually.

Common follow-ups: What is the difference between the inserted and deleted pseudo tables available inside a trigger?;Can a single table have multiple triggers for the same event?

Change Data Capture (CDC);Error Handling with TRY CATCH
All 7 Triggers questions