Creating Triggers in SQL Server

Triggers are a powerful feature in SQL Server that allow you to enforce business rules and maintain data integrity. In this tutorial, we will explore how to create insert, update, and delete triggers in SQL Server.

Creating an Insert Trigger:

An insert trigger is used to enforce business rules when inserting new data into a table. Here is an example of how to create an insert trigger:

CREATE TRIGGER trgEmployeeInsert
ON Employee
FOR INSERT
AS
   INSERT INTO EmpLog(EmployeeID, FirstName, LastName, HireDate, Operation, UpdatedOn, UpdatedBy)
   SELECT EmployeeID, Firstname, LastName, HireDate, 'INSERT', GETDATE(), SUSER_NAME()
   FROM INSERTED;
GO

Creating an Update Trigger:

An update trigger is used to enforce business rules when updating existing data in a table. Here is an example of how to create an update trigger:

CREATE TRIGGER trgEmployeeUpdate
ON Employee
FOR UPDATE
AS
   INSERT INTO EmpLog(EmployeeID, FirstName, LastName, HireDate, Operation, UpdatedOn, UpdatedBy)
   SELECT EmployeeID, Firstname, LastName, HireDate, 'UPDATE', GETDATE(), SUSER_NAME()
   FROM INSERTED;
GO

Creating a Delete Trigger:

A delete trigger is used to enforce business rules when deleting data from a table. Here is an example of how to create a delete trigger:

CREATE TRIGGER trgEmployeeDelete
ON Employee
FOR DELETE
AS
   INSERT INTO EmpLog(EmployeeID, FirstName, LastName, HireDate, Operation, UpdatedOn, UpdatedBy)
   SELECT EmployeeID, Firstname, LastName, HireDate, 'DELETED', GETDATE(), SUSER_NAME()
   FROM DELETED;
GO

Testing the Triggers:

To test the triggers, you can insert, update, and delete data from the Employee table and verify that the corresponding records are inserted into the EmpLog table.

I hope this tutorial helps you understand how to create insert, update, and delete triggers in SQL Server!

Creating a Delete Trigger:

A delete trigger is used to enforce business rules when deleting data from a table. Here is an example of how to create a delete trigger:

CREATE TRIGGER trgEmployeeDelete
ON Employee
FOR DELETE
AS
   INSERT INTO EmpLog(EmployeeID, FirstName, LastName, HireDate, Operation, UpdatedOn, UpdatedBy)
   SELECT EmployeeID, Firstname, LastName, HireDate, 'DELETED', GETDATE(), SUSER_NAME()
   FROM DELETED;
GO

Testing the Triggers:

To test the triggers, you can insert, update, and delete data from the Employee table and verify that the corresponding records are inserted into the EmpLog table.

I hope this tutorial helps you understand how to create insert, update, and delete triggers in SQL Server.

Coming Soon