Triggers in SQL server with real-time example

Hello, welcome to Appsloveworld Technologies, In this session, we learn about the different typesof triggers that are available in SQL Server and After insert and delete trigger in SQL server with real-time example.

In the SQL server, there are three types of triggers

1. DML
2. DDL
3. Logon triggers.

In this article, we will talk about DML triggers with examples. DML stands for Data Manipulation Language, insert update and delete statements are examples for the DML statements because they modified the data that’s present inside the table or a view.

DML triggers fire automatically in response to DML events. Since they are fired in response to an insert update or delete statement and these statements can be classified as DML statements, these triggers are called DML triggers.

Now DML triggers can again be classified into two types

1. After triggers
2. Instead of triggers.

After triggers, Sometimes they are also called as FOR triggers. So FOR or after triggers in SQL server means, the same. Now let’s understand the difference between after triggers and instead of triggers.

After triggers – as the name says, they fire after the triggering action, and the triggering action could be inserted, update and delete. So after triggers, fire after an insert update or delete statement execution.

Instead of triggers – instead of triggers, as the name says, they fire instead of the triggering action. For example, when you try to insert a row into a table instead of inserting that row into that table if there is an instead of trigger that gets fired.You can read more about instead of triggers in the below post.

• Instead Of Triggers in Sql server With Example

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.

So let’s understand triggers with examples. so I have a table called TblDoctors. We just got Id, names, gender, and Specialization columns.

So let’s say whenever somebody adds a new row into TblDoctors, that could be directly using a SQL statement or through an application. I want some log information to be captured automatically and generate a notification for admin in the TblNotifications table that you can see on the below image.

TblNotifications has gotID,DoctorId,DoctorName,Notification,IsSeen and CreateAt columns.

Now, let’s say, for example, whenever I insert a new row into TblDoctors, in the TblNotifications table, I want data like the above image.

So how can I do that? I mean, obviously, we can make use of a stored procedure to do that. But the easiest way to achieve, this is to basically associate a trigger with the TblDoctors table. So that as soon as the insert statement is fired, the trigger is executed on the TblDoctors table.

I want to create an after insert trigger, which will capture the Id of the record that you have inserted into TblDoctors and then the DateTime and insert a row into the TblNotifications table.

Let’s look at that in action.

So here we have a 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, look at the trigger. Whenever you’re creating a trigger, you create a trigger for a table and a specific event.

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

So that’s why. We create the trigger on the TblDoctors table for insert action or event, you can see that in the above query.

 

Explanation of  above after insert trigger Query

I’m creating a trigger, “CREATE TRIGGER ” trigger name, the naming convention is to use “tr” so underscore the name of the table and then for which action you are creating a trigger.

Here we are creating a trigger for insert action on TblDoctors , FOR INSERT “as ” Begin and the “End” just like store procedure. So inside your trigger, you can implement whatever logic you want

So what we want here, we want to capture the Id and Name of the Doctor that we have just inserted into the TblDoctors table.

let’s declare a variable of type integer and varchar.So declare Id integer, Name varchar and we are selecting the id, Name of the Doctor. If you look at this, we are saying select @ID is equal to ID from inserted the table.

So what is the inserted table? Where did this come from?

 Inserted a table is a special table that is, used by triggers and this table is only available in the context of a trigger.

Now whenever you insert a row, into TblDoctors or any other table for that matter, the SQL server maintains this magic table called inserted Table, which retains a copy of the row that you have inserted into the actual table.

So when you insert a row into the TblDoctors , SQL server behind the scenes creates a table called Inserted in memory and a copy of the row that you have just inserted into table TblDoctors will be maintained in this inserted table. And then inserted a table can be accessed inside the context of a trigger.

And then you can inspect those values. If you want to roll back, you can roll back those changes or if you want to audit information, you can capture that.

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.

You should see the trigger there. So now since this is created for insert action, so as soon as we execute and insert a statement into the TblDoctors table, so let’s execute the insert statement.

What should happen, trigger should fire. Now Let’s insert a row into TblDoctors

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

So when we execute the above SQL server says two rows effected because after the insert statement Immediately, the trigger gets fired, and a row into TblNotifications.

Let see row is inserted or not in TblNotifications

select * from [dbo].[TblNotifications]

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

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

So Let’s 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]

DELETED table, is a special table utilized by triggers. When you delete a row from the TblDoctors table, a copy of the deleted row will be created in the DELETED table, which can access by the trigger.

You can access the INSERTED table outside the context of the trigger, But the DELETED table cannot be accessed, outside the context of the trigger and, the structure of the DELETED table will be identical to the structure of 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

The post Triggers in SQL server with real-time example appeared first on Software Development | Programming Tutorials.