SQL Triggers Interview Questions
3 questions with answers · SQL Interview Guide
AFTER and INSTEAD OF triggers, DML and DDL triggers, and when to use triggers vs constraints.
Why and when to use a trigger:
A trigger is used when you need the database itself to automatically enforce business rules or audit changes without relying on application code. Common use cases include maintaining an audit trail (logging who changed what and when), enforcing complex constraints that CHECK constraints cannot handle, and keeping denormalized summary tables in sync. The key reason to use a trigger over application-side logic is that it fires regardless of how the data is modified, whether through the app, a direct SQL script, or another process. However, triggers should be used sparingly because they add hidden complexity and can make debugging difficult.
What are the different types of triggers
Triggers are categorized by timing and event. By timing you have BEFORE triggers (fire before the DML operation executes) and AFTER triggers (fire after the operation completes). By event you have INSERT, UPDATE, and DELETE triggers. SQL Server also has INSTEAD OF triggers, which replace the triggering action entirely and are commonly used on views to make them updatable. Some databases like SQL Server also support DDL triggers that fire on schema changes like CREATE or DROP, and logon triggers that fire on user login events.
if we have multiple AFTER Triggers on table how can we define the sequence od the triggers
In SQL Server you can control the order of multiple AFTER triggers on the same table using the sp_settriggerorder stored procedure. It lets you designate one trigger to fire first and one to fire last, but any triggers in between will fire in an undefined order. So if you have more than two triggers and the middle ones must run in a specific sequence, the right solution is to consolidate them into fewer triggers where you can control the logic order explicitly.
-- Set the first trigger to fire first
EXEC sp_settriggerorder
@triggername = 'trg_AuditInsert',
@order = 'First',
@stmttype = 'INSERT';
-- Set another trigger to fire last
EXEC sp_settriggerorder
@triggername = 'trg_NotifyInsert',
@order = 'Last',
@stmttype = 'INSERT';Knowing the answers is half the battle
The other half is explaining them clearly under pressure.
Try a free mock interviewarrow_forward