Triggers In SQL Server with Real-Time Example


 Hello, and welcome to Quickpickdeal! In today's session, we'll delve into the various types of triggers available in SQL Server. Specifically, we'll explore after insert and delete triggers with real-time examples.

In the SQL server, there are three types of triggers

  1. DML
  2. DDL
  3. Logon triggers.

In this article, we will discuss DML triggers with examples. DML stands for Data Manipulation Language, and statements like insert, update, and delete are examples of DML statements because they modify the data within a table or a view.

DML triggers are automatically activated in response to DML events. Since they are triggered by insert, update, or delete statements, which are classified as DML statements, these triggers are referred to as DML triggers.

Now DML triggers can again be classified into two types

  1. After triggers
  2. Instead of triggers.

After triggers, sometimes referred to as FOR triggers, function in SQL Server to execute after the triggering action occurs. This triggering action could be an insert, update, or delete operation. Therefore, after triggers fire subsequent to the execution of an insert, update, or delete statement.

On the other hand, instead of triggers, as their name implies, fire in place of the triggering action. For instance, when attempting to insert a row into a table, if there is an instead of trigger defined, it will be executed instead of inserting the row directly into the table. More information about instead of triggers can be found in the post linked below.

After triggers are also classified into three categories

  1. After insert trigger
  2. After update trigger
  3. After delete trigger

Triggers can be considered as a special kind of stored procedures that execute automatically in response to a triggering action.

Let's explore triggers with an example using the TblDoctors table. This table contains columns for Id, name, gender, and specialization. We'll use this table to demonstrate how triggers work in SQL Server.
1

Whenever someone adds a new row to the TblDoctors table, whether through a SQL statement or an application, I want to automatically capture some log information and generate a notification for the admin. This notification should be stored in the TblNotifications table, as shown in the image below.

2

TblNotifications includes columns such as ID, DoctorId, DoctorName, Notification, IsSeen, and CreateAt.

Suppose, for instance, that whenever a new row is inserted into TblDoctors, I want to populate the TblNotifications table with data similar to the example image provided.

To accomplish this, we can associate a trigger with the TblDoctors table. This trigger will execute as soon as an insert statement is fired on the TblDoctors table.

I aim to create an after insert trigger, which will capture the ID of the newly inserted record into TblDoctors, along with the current DateTime, and then insert a corresponding row into the TblNotifications table.

Let’s look at that in action.

Real-time After insert trigger

CREATE TRIGGER tr_TblDoctors_ForInsert
ON TblDoctors
FOR INSERT
AS
BEGIN
Declare @Id int
Declare @Name varchar(100)
Select @Id = Id from inserted
Select @Name = Name from inserted

insert into TblNotifications
values(@Id,@Name,'New Doctors with Id = ' + Cast(@Id as nvarchar(5)) +'Name= '+@Name+ ' is registered at ' + cast(Getdate() as nvarchar(20)),0,Getdate())
END

Now, when you create a trigger, you specify both the table and the specific event that triggers its execution.

In this example, we want to create a trigger on the TblDoctors table for the insert event. This is because we aim to log data in the TblNotifications table as soon as new rows are inserted into TblDoctors.

That's why we create the trigger on the TblDoctors table for the insert action/event, as demonstrated in the query provided above."

2

 

Explanation of  above after insert trigger Query

I'm creating a trigger named 'tr_TableName_Action', following the convention of prefixing with 'tr' followed by the table name and the action the trigger is for. Here, I'm creating a trigger for the 'INSERT' action on the 'TblDoctors' table. 

The trigger starts with 'FOR INSERT as Begin' and ends with 'End', similar to a stored procedure. Inside this trigger, you can implement any necessary logic.

Our goal here is to capture the 'Id' and 'Name' of the doctor that is being inserted into the 'TblDoctors' table. To achieve this, we'll declare variables of type 'integer' and 'varchar'. So, we declare 'Id' as integer and 'Name' as varchar. 

Then, we'll select the 'Id' and 'Name' of the doctor. If you observe closely, we're assigning the value of 'ID' from the 'inserted' table to the variable '@ID' using the query 'select @ID = ID from inserted'.

3
What is the inserted table? Where did this come from?

The 'inserted' table is a special table utilized by triggers, available solely within the trigger's context.

Every time a row is inserted into a table such as 'TblDoctors', SQL Server maintains a special table called 'inserted'. This 'inserted' table acts as a repository, storing a copy of the inserted row from the original table. Essentially, when you insert a row into 'TblDoctors', SQL Server silently generates this 'inserted' table in memory and preserves a replica of the inserted row within it. Subsequently, the 'inserted' table can be accessed exclusively within the trigger's scope.

This allows for the examination of these values. Should you desire to undo the changes, you can rollback those alterations, or if you wish to log information for auditing purposes, you can capture it.

Where i can find trigger in sql server?

So we created trigger on the TblDoctors table. So go to the TblDoctors table refresh triggers folder expand that.

4

You should be able to view the trigger in the database. Since this trigger is designed for the 'INSERT' action, once we execute an 'INSERT' statement into the 'TblDoctors' table, the trigger should be triggered.

Let's proceed by executing an 'INSERT' statement into the 'TblDoctors' table. Upon doing so, the trigger should fire.

INSERT INTO [dbo].[TblDoctors]
([Name]
,[Gender]
,[Specialization])
VALUES
('Dr. Thomas'
,'Male'
,'Urology')

5

After executing the SQL statement, SQL Server reports that two rows were affected. This count reflects the insertion of a row immediately after the 'INSERT' statement due to the trigger firing. 

Now, let's verify whether a row has been inserted into the 'TblNotifications' table.

select * from [dbo].[TblNotifications]

As you can see in the below image we have an entry for the ‘Dr. Thomas‘

notifcation

Now let us now capture information, when a doctor row is deleted from the TblDoctors, tblEmployee.

Create an AFTER TRIGGER for DELETE event on TblDoctors table:

 

CREATE TRIGGER tr_TblDoctors_ForDELETED
ON TblDoctors
FOR DELETE
AS
BEGIN
Declare @Id int
Declare @Name varchar(100)
Select @Id = Id from deleted
Select @Name = Name from deleted

insert into TblNotifications
values(@Id,@Name,'New Doctors with Id = ' + Cast(@Id as nvarchar(5)) +'Name= '+@Name+ ' is DELETED at ' + cast(Getdate() as nvarchar(20)),0,Getdate())
END

we just need to use the triggering event as DELETE and retrieving the deleted row ID and Name from the DELETED table.

delete from [dbo].[TblDoctors] where id=14

select * from [dbo].[TblNotifications]

Notification

The 'DELETED' table is another special table employed by triggers. When a row is deleted from the 'TblDoctors' table, SQL Server creates a replica of the deleted row within the 'DELETED' table, which can be accessed within the trigger's scope.

Unlike the 'INSERTED' table, which can be accessed outside the trigger's context, the 'DELETED' table cannot be accessed outside the trigger's scope. Additionally, the structure of the 'DELETED' table mirrors that of the 'TblDoctors' table.

For more detail read mircosoft article-microsoft

Read More –After Update Trigger in Sql Server With Example

Table Script

/****** Object: Table [dbo].[TblDoctors] Script Date: 10/13/2020 7:47:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblDoctors](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](max) NOT NULL,
[Gender] [nvarchar](50) NULL,
[Specialization] [nvarchar](50) NULL,
CONSTRAINT [PK_TblStudent] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[TblNotifications] Script Date: 10/13/2020 7:47:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblNotifications](
[DoctorId] [int] NOT NULL,
[DoctorName] [nvarchar](50) NOT NULL,
[Notification] [nvarchar](max) NOT NULL,
[IsSeen] [bit] NOT NULL,
[CreateAt] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[TblDoctors] ON
GO
INSERT [dbo].[TblDoctors] ([Id], [Name], [Gender], [Specialization]) VALUES (1, 'Carter', 'Male', 'Neurology')
GO
INSERT [dbo].[TblDoctors] ([Id], [Name], [Gender], [Specialization]) VALUES (3, 'Gianna', 'Female', 'Urology')
GO
INSERT [dbo].[TblDoctors] ([Id], [Name], [Gender], [Specialization]) VALUES (4, 'Brandon', 'Male', 'Medical Gastroenterology')
GO
INSERT [dbo].[TblDoctors] ([Id], [Name], [Gender], [Specialization]) VALUES (5, 'Julia', 'Female', 'Cardiology (Heart Care)
')
GO
INSERT [dbo].[TblDoctors] ([Id], [Name], [Gender], [Specialization]) VALUES (6, 'Julian', 'Male', 'Oncology (Cancer Care)
')
GO
INSERT [dbo].[TblDoctors] ([Id], [Name], [Gender], [Specialization]) VALUES (7, 'Kayla', 'Female', 'Bone Marrow Transplant.
')
GO
INSERT [dbo].[TblDoctors] ([Id], [Name], [Gender], [Specialization]) VALUES (8, 'Henry', 'Male', 'Medical Gastroenterology.
')
GO
INSERT [dbo].[TblDoctors] ([Id], [Name], [Gender], [Specialization]) VALUES (9, 'Autumn', 'Female', 'Urology')
GO
INSERT [dbo].[TblDoctors] ([Id], [Name], [Gender], [Specialization]) VALUES (10, 'Sebastian', 'Male', 'Neurology')
GO
INSERT [dbo].[TblDoctors] ([Id], [Name], [Gender], [Specialization]) VALUES (11, 'Blake', 'Male', 'Bone Marrow Transplant.
')
GO
INSERT [dbo].[TblDoctors] ([Id], [Name], [Gender], [Specialization]) VALUES (12, 'Dr. Jacob', 'Male', 'Urology')
GO
INSERT [dbo].[TblDoctors] ([Id], [Name], [Gender], [Specialization]) VALUES (13, 'Dr. Henry', 'Male', 'Urology')
GO
SET IDENTITY_INSERT [dbo].[TblDoctors] OFF


Read Similar Articles

Copyright 2024 www.quickpickdeal.com. All rights reserved.