Backend interview prep

Start

SQL Triggers Interview Questions

Master SQL Triggers with interview-focused questions covering BEFORE, AFTER, INSTEAD OF triggers, INSERT, UPDATE, DELETE triggers, row vs statement triggers, auditing, validation, performance, and enterprise production best practices.

Introduction

A Trigger is a database object that automatically executes when a specified database event occurs.

Unlike Stored Procedures,

Triggers

  • Execute Automatically
  • Cannot be directly called
  • Respond to database events

Typical events include

  • INSERT
  • UPDATE
  • DELETE

Triggers are widely used in

  • Banking
  • Healthcare
  • ERP
  • Insurance
  • Financial Systems
  • Auditing

They are one of the most frequently asked SQL interview topics.

Trigger Architecture

flowchart LR

Application --> InsertUpdateDeleteTrigger["INSERT / UPDATE / DELETE --> Trigger --> Business Logic --> Database"]

Sample Employee Table

ID Name Salary Department
101 John 90000 IT
102 Alice 85000 HR

Audit Table

Employee_ID Action Action_Time

1. What is a Trigger?

Answer

A Trigger is a special database program that automatically executes when a specified event occurs on a table or view.

Unlike Stored Procedures,

Triggers execute automatically.

Trigger Workflow

INSERT

↓

Trigger Fires

↓

Business Logic

↓

Transaction Continues

2. Why are Triggers used?

Triggers are commonly used for

  • Auditing
  • Data Validation
  • Logging
  • Business Rules
  • Notifications
  • Maintaining Data Integrity

3. Which database events can fire Triggers?

Common events

  • INSERT
  • UPDATE
  • DELETE

Some databases also support

  • DDL Triggers
  • LOGON Triggers

Trigger Events

Database Events

├── INSERT

├── UPDATE

└── DELETE

4. What are BEFORE Triggers?

A BEFORE Trigger executes

before

the database operation.

Useful for

  • Validation
  • Data Modification
  • Business Rules

BEFORE Trigger Example

CREATE TRIGGER CheckSalary
BEFORE INSERT
ON Employee
FOR EACH ROW
BEGIN
    IF NEW.salary < 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT='Invalid Salary';
    END IF;
END;

5. What are AFTER Triggers?

An AFTER Trigger executes

after

the database operation completes successfully.

Useful for

  • Auditing
  • Logging
  • Notifications

AFTER Trigger Example

CREATE TRIGGER EmployeeAudit
AFTER INSERT
ON Employee
FOR EACH ROW
BEGIN
    INSERT INTO EmployeeAudit
    VALUES
    (
        NEW.id,
        'INSERT',
        NOW()
    );
END;

6. What are INSTEAD OF Triggers?

INSTEAD OF Triggers

replace

the original operation.

Commonly supported on

  • SQL Server
  • Oracle (with views)

Useful for

  • Complex Views
  • Business Logic

Trigger Types

Triggers

├── BEFORE

├── AFTER

└── INSTEAD OF

7. What is an INSERT Trigger?

Executes automatically

whenever

a new row

is inserted.

INSERT Trigger

flowchart LR

INSERT --> Trigger --> AuditTable["Audit Table"]

8. What is an UPDATE Trigger?

Executes

after

or before

an UPDATE.

Often used for

tracking changes.

UPDATE Trigger Example

AFTER UPDATE
ON Employee

9. What is a DELETE Trigger?

Executes

when

rows

are deleted.

Useful for

  • Auditing
  • Archiving
  • Logging

DELETE Trigger

flowchart LR

DELETE --> Trigger --> ArchiveTable["Archive Table"]

10. What is a Row-Level Trigger?

A Row-Level Trigger executes

once

for

every affected row.

Example

100 Rows Updated

↓

Trigger Executes

100 Times

11. What is a Statement-Level Trigger?

A Statement-Level Trigger executes

once

per SQL statement,

regardless of how many rows are affected.

Example

100 Rows Updated

↓

Trigger Executes

1 Time

Row vs Statement Trigger

Row Trigger Statement Trigger
Every Row One Time
Slower Faster
Detailed Processing Bulk Processing

Note: Support for statement-level triggers varies by database. Oracle supports both row- and statement-level triggers, while MySQL supports only row-level triggers.

12. What are OLD and NEW values?

Triggers can access

Previous Values

and

New Values.

Example

OLD.salary

NEW.salary

OLD vs NEW

Salary

90000

↓

95000

OLD

↓

NEW

13. Why use OLD values?

Useful for

  • Audit Logs
  • Change Tracking
  • History Tables

14. Why use NEW values?

Useful for

  • Validation
  • Logging
  • Business Rules

15. Can Triggers call Stored Procedures?

Yes.

Many databases allow

Stored Procedures

to be executed

inside Triggers.

16. Can Triggers contain Transactions?

Generally,

Triggers execute

within

the same transaction

as the triggering statement.

Whether explicit COMMIT or ROLLBACK statements are allowed inside a trigger depends on the database vendor.

17. Banking Example

Money Transfer

↓

Insert Transaction

↓

Audit Trigger

↓

Audit Table

18. HR Example

Salary Update

↓

Trigger

↓

Salary History Table

19. Healthcare Example

Patient Record Updated

↓

Audit Trigger

↓

Medical History

20. E-Commerce Example

Order Created

↓

Trigger

↓

Inventory Updated

21. Insurance Example

Claim Updated

↓

Trigger

↓

Claim History

22. Production Example

Employee Deleted

↓

Trigger

↓

Archive Table

↓

Audit Log

23. Advantages of Triggers

  • Automatic Execution
  • Centralized Rules
  • Auditing
  • Data Integrity
  • Logging
  • Security

24. Disadvantages of Triggers

  • Hidden Logic
  • Hard to Debug
  • Performance Overhead
  • Recursive Trigger Risk
  • Vendor Differences

25. Trigger vs Stored Procedure

Trigger Stored Procedure
Automatic Manual Call
Event Driven Explicit Execution
No Direct Call CALL / EXEC
Runs on Events Runs on Request

26. Trigger vs Constraint

Trigger Constraint
Complex Logic Simple Validation
Procedural Declarative
Flexible Faster
Can Audit Cannot Audit

27. Common Mistakes

  • Infinite Trigger Loops
  • Heavy Business Logic
  • Large Queries Inside Triggers
  • Missing Error Handling
  • Recursive Updates

28. Performance Tips

  • Keep triggers lightweight.
  • Avoid long-running queries.
  • Don't perform unnecessary updates.
  • Avoid cascading trigger chains.
  • Log only required information.
  • Monitor trigger execution time.

29. Best Practices

  • Keep trigger logic simple.
  • Use triggers mainly for auditing and validation.
  • Avoid implementing entire business workflows inside triggers.
  • Document every trigger.
  • Test trigger performance.
  • Prevent recursive execution.
  • Handle exceptions properly.
  • Use meaningful trigger names.
  • Monitor trigger execution.
  • Review trigger dependencies regularly.

30. Trigger Workflow

flowchart LR

INSERT --> Trigger --> Validation --> Audit --> Commit

Enterprise Best Practices

  • Use triggers primarily for auditing and enforcing simple business rules.
  • Avoid placing complex business logic inside triggers.
  • Keep trigger execution fast.
  • Prevent recursive trigger execution.
  • Log only necessary audit information.
  • Test trigger behavior during bulk operations.
  • Version-control trigger definitions.
  • Monitor execution time and failures.
  • Review trigger dependencies before schema changes.
  • Prefer application or service-layer logic for complex workflows.

Quick Revision

Topic Key Point
Trigger Automatic Database Program
BEFORE Executes Before Event
AFTER Executes After Event
INSTEAD OF Replaces Operation
INSERT Trigger Insert Event
UPDATE Trigger Update Event
DELETE Trigger Delete Event
OLD Previous Value
NEW New Value
Row Trigger Per Row
Statement Trigger Per Statement

Interview Tips

Interviewers frequently ask

  • What is a Trigger?
  • Trigger vs Stored Procedure.
  • BEFORE vs AFTER Trigger.
  • Row Trigger vs Statement Trigger.
  • OLD vs NEW values.
  • Trigger use cases.
  • Trigger advantages and disadvantages.
  • Can Triggers call Stored Procedures?
  • Can Triggers contain transactions?
  • Trigger performance considerations.

A strong interview explanation is:

"A Trigger is an event-driven database object that automatically executes when an INSERT, UPDATE, or DELETE operation occurs. Triggers are commonly used for auditing, validation, enforcing business rules, and maintaining data integrity. BEFORE triggers execute prior to the data modification, AFTER triggers execute after the modification succeeds, and some databases support INSTEAD OF triggers for views. Production triggers should remain lightweight, avoid complex business logic, and be carefully monitored for performance."

Summary

SQL Triggers provide automatic execution of database logic in response to data changes. They are widely used for auditing, logging, validation, history tracking, and enforcing business rules. Understanding BEFORE, AFTER, INSTEAD OF, INSERT, UPDATE, DELETE, OLD, NEW, row-level, and statement-level triggers is essential for enterprise database development.

Mastering SQL Triggers prepares you for advanced topics such as Normalization, Transactions, Database Design, Performance Tuning, and Enterprise Database Architecture.