---
title: "Why are use triggers in the SQL Server Database?"  
description: "Why are use triggers in the SQL Server Database?"  
author: "Ashutosh Patel"  
published: 2024-07-18  
updated: 2024-07-18  
canonical: https://www.mindstick.com/interview/33959/why-are-use-triggers-in-the-sql-server-database  
category: "mssql server"  
tags: ["mssql server", "sql server", "sql"]  
reading_time: 4 minutes  

---

# Why are use triggers in the SQL Server Database?

#### SQL Server Trigger

Triggers in a database are special stored procedures that are automatically executed or fired in response to certain events in a table or view. It is used to enforce business rules, ensure data integrity, and automate business based on data transformation.

#### Key Uses of Triggers

**Data Validation and Enforcement**\
Verify that a value in a column meets certain criteria before allowing it to be `insert` or `updat`. For example, below created trigger is prevented from inserting the negative value into the Salary column,

```plaintext
CREATE TRIGGER ValidateSalary
ON Employees
BEFORE INSERT, UPDATE
AS
BEGIN
   IF EXISTS (SELECT * FROM inserted WHERE Salary < 0)
   BEGIN
       RAISERROR ('Salary cannot be negative.', 16, 1);
       ROLLBACK;
   END
END;
```

## Maintain History

Triggers are also used to automatically maintain a history of records that are deleted from tables.

the below trigger is fires automatically when someone deleted the record from `Employees` table and insert the deleted records into `Employees_History` table

```plaintext
CREATE TRIGGER ValidateSalary ON Employees
AFTER DELETE
AS
BEGIN
  INSERT INTO Employees_History(EmpName, EmpName, Salary)
  SELECT EmpName, EmpName, Salary FROM deleted
END;
```

#### \
Types of Triggers

**BEFORE Triggers-** Perform before the triggering event (INSERT, UPDATE, DELETE). Useful for validating or editing data before commissioning.

**AFTER Triggers-** Perform after the triggering event has occurred. Useful for actions like logging or updating linked tables.

**INSTEAD OF Triggers-** Replace trigger events with intentional actions. Useful for custom tasks.

#### Benefits of Using Triggers

**Automation-** The rules are enforced and tasks are performed without the need for manual intervention.\
**Consistency-** Ensure data integrity and business rules are applied consistently throughout the database.\
**Auditing-** Provide a built-in tool to track changes in data to monitor compliance and correct deficiencies.

**Also, Read:** [What are SQL Stored Procedures in the Database?](https://www.mindstick.com/interview/33957/what-are-sql-stored-procedures-in-the-database)

## Answers

### Answer by Ashutosh Patel

#### SQL Server Trigger

Triggers in a database are special stored procedures that are automatically executed or fired in response to certain events in a table or view. It is used to enforce business rules, ensure data integrity, and automate business based on data transformation.

#### Key Uses of Triggers

**Data Validation and Enforcement**\
Verify that a value in a column meets certain criteria before allowing it to be `insert` or `updat`. For example, below created trigger is prevented from inserting the negative value into the Salary column,

```plaintext
CREATE TRIGGER ValidateSalary
ON Employees
BEFORE INSERT, UPDATE
AS
BEGIN
   IF EXISTS (SELECT * FROM inserted WHERE Salary < 0)
   BEGIN
       RAISERROR ('Salary cannot be negative.', 16, 1);
       ROLLBACK;
   END
END;
```

## Maintain History

Triggers are also used to automatically maintain a history of records that are deleted from tables.

the below trigger is fires automatically when someone deleted the record from `Employees` table and insert the deleted records into `Employees_History` table

```plaintext
CREATE TRIGGER ValidateSalary ON Employees
AFTER DELETE
AS
BEGIN
  INSERT INTO Employees_History(EmpName, EmpName, Salary)
  SELECT EmpName, EmpName, Salary FROM deleted
END;
```

#### \
Types of Triggers

**BEFORE Triggers-** Perform before the triggering event (INSERT, UPDATE, DELETE). Useful for validating or editing data before commissioning.

**AFTER Triggers-** Perform after the triggering event has occurred. Useful for actions like logging or updating linked tables.

**INSTEAD OF Triggers-** Replace trigger events with intentional actions. Useful for custom tasks.

#### Benefits of Using Triggers

**Automation-** The rules are enforced and tasks are performed without the need for manual intervention.\
**Consistency-** Ensure data integrity and business rules are applied consistently throughout the database.\
**Auditing-** Provide a built-in tool to track changes in data to monitor compliance and correct deficiencies.

**Also, Read:** [What are SQL Stored Procedures in the Database?](https://www.mindstick.com/interview/33957/what-are-sql-stored-procedures-in-the-database)


---

Original Source: https://www.mindstick.com/interview/33959/why-are-use-triggers-in-the-sql-server-database

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.