How Do Triggers Work in SQL and When Should They Be Used?
💡 Concept Name
SQL Triggers – Special procedures that automatically execute in response to specific events (INSERT, UPDATE, DELETE) on a table or view.
📘 Quick Intro
Triggers are useful for enforcing business rules, logging changes, and automating actions in a database. They can be fired before or after data modification events occur.
🧠 Analogy / Short Story
Imagine a store’s security alarm system. When someone opens the door (event), the alarm (trigger) is automatically activated to perform an action like notifying the guard. Similarly, SQL triggers act immediately when a predefined action takes place on data.
🔧 Technical Explanation
- Triggers are bound to a table or view and respond to INSERT, UPDATE, or DELETE operations.
- BEFORE Triggers: Execute before the data modification occurs.
- AFTER Triggers: Execute after the data has been modified.
- INSTEAD OF Triggers: Used to override standard data modification behavior (mainly on views).
- Triggers can reference the inserted/deleted pseudo-tables to access changed rows.
💻 Real Code Example
-- Trigger to log inserts into Employees table
CREATE TABLE EmployeeLog (
LogID INT PRIMARY KEY IDENTITY,
EmployeeID INT,
ActionTime DATETIME DEFAULT GETDATE()
);
CREATE TRIGGER trgAfterInsert
ON Employees
AFTER INSERT
AS
BEGIN
INSERT INTO EmployeeLog (EmployeeID)
SELECT EmployeeID FROM inserted;
END;
❓ Interview Q&A
Q1: What is a trigger in SQL?
A: A database object that runs automatically in response to specific table events like INSERT, UPDATE, or DELETE.
Q2: What are BEFORE and AFTER triggers?
A: BEFORE triggers run before data modification; AFTER triggers run after changes are committed.
Q3: Can triggers be used to validate data?
A: Yes, especially BEFORE triggers to prevent invalid changes.
Q4: What is an INSTEAD OF trigger?
A: It replaces the original DML action and is commonly used on views.
Q5: Can a trigger call another trigger?
A: Yes, but it may lead to cascading behavior and should be used cautiously.
Q6: Are triggers part of a transaction?
A: Yes, they execute as part of the transaction and can cause rollbacks if errors occur.
Q7: Can a table have multiple triggers?
A: Yes, but order of execution may depend on the database system.
Q8: How do you reference the new data in a trigger?
A: Use the pseudo-tables inserted and deleted.
Q9: What are common use cases for triggers?
A: Auditing, enforcing complex business rules, and automatic field updates.
Q10: When should you avoid triggers?
A: When they cause performance bottlenecks or obscure business logic that should reside in the application layer.
📝 MCQs
Q1. Which SQL event can trigger a trigger?
- SELECT
- INSERT
- WHERE
- FROM
Q2. What is the purpose of a trigger?
- Run scheduled jobs
- Backup data
- Automatically execute on data events
- Index data
Q3. Which pseudo-table stores new data rows?
- newdata
- modified
- inserted
- committed
Q4. What does an AFTER trigger do?
- Runs before action
- Ignores change
- Runs after data change
- Deletes rows
Q5. Can triggers be used on views?
- No
- Yes, with INSTEAD OF
- Only on temp tables
- Only on updates
Q6. What is a use case for BEFORE triggers?
- Join optimization
- Backup creation
- Data validation
- View creation
Q7. What happens if a trigger fails?
- Continues silently
- Triggers next query
- Transaction may roll back
- Deletes trigger
Q8. Can a trigger modify the same table it’s on?
- No
- Yes, always
- Yes, but with caution
- Only with MERGE
Q9. What type of trigger replaces the DML action?
- AFTER
- INSTEAD OF
- BEFORE
- ON DELETE
Q10. Why should triggers be used carefully?
- They replace indexes
- They delete logs
- They can impact performance
- They are deprecated
💡 Bonus Insight
Triggers can be powerful for automation and auditing, but overuse can make systems harder to maintain and debug. Always document trigger logic clearly.
📄 PDF Download
Need a handy summary for your notes? Download this topic as a PDF!