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
- DML
- DDL
- 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
- After triggers
- 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.
After triggers are also classified into three categories
- After insert trigger
- After update trigger
- 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.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- How do I write a SQL Server function to return a single value from result of several queries
- How to get percentile instead of rank
- Where are all database file paths stored in SQL?
- Migration from SQL Server 2005 to SQL Server 2012 - Stored Procedure issue
- FOR/AFTER in SQL triggers
- Why is t-SQL order by datetime correct in SQL SMS but not in ASP Classic output?
- Generating scripts based on condition
- Freetds could not connect to SQL Azure by server name (-S), but connect by host (-H) works
- NodeJS backend post request posts data to the SQL Server table as 'NULL' values
- CASE WHEN - What am I doing wrong?
- Reading the IMPLICIT_TRANSACTIONS value in SQL Server
- SQL Server: is it possible to script table as UPDATE?
- Multiple locations and different user privileges for database
- Image byte array is not saving properly to database
- can't add a sql database to my project
- COUNT from 2 separate tables
- How to find the most common value in each column
- Will this query load the whole table in memory
- selecting Row count between a range of dates based on current Date
- Maximum value of a table
- Multiple SET expressions in query?
- SQL Insert depending if a column exists
- Can "allow multiple values" for parameters be used with stored procedures in Report Builder 3.0?
- GROUP BY with COUNT condition
- How do I send data from Excel to SQL temp tables for processing using VBA?
- Microsoft SQL Server Management Studio says that RowVersion is invalid type
- SQL Server : getting execution time out exceptions from stored procedure many times, but when executing the query it returns output quickly
- want total revenue for last 30 days from max date and age of customers between 25 and 30 calculated from DOB
- Process SQL Table with no Unique Column
- How do I list current running Stored Procedures with SMO?
- Update random top 50 rows
- How to split a string from a table SQL Server 2012?
- The SQL Server Network Interface library could not deregister the Service Principal Name (SPN)
- Structuring XML using FOR XML
- Azure SQL Server Error : The elastic pool has reached its storage limit. The storage usage for the elastic pool cannot exceed (204800)
- Query SQL Server System Tables For Character Data Type Sizes
- How to break a table CSVs into rows
- Calculate date-difference, compering rows and creating new once
- Microsoft SQL Server : finding columns differences between two separate databases
- sum over multiple columns
- SysIndex - difference b/w is_unique and is_unique_constraint
- Get Sql server's data using smo
- Executing stored procedures from a DbContext
- SQL Server not accepting alias name
- Clustered index selection with Django
- Stored procedure:Column name is not appearing while execute output parameters
- Do numerical primary keys of deleted records in a database get reused for future new records?
- Returning data where dates are at least 7 days apart
- Transpose Keep order
- SQL Server 2014: INSERT INTO values & subqueries
- Parameterized stored procedure skip if when zero is passed
- Rollback SQL Server from code
- Selecting column names from a query NOT a view
- How to pass the where clause to a SQL Server stored procedure?
- Rollback in Trigger
- Function to return int values from a column - SQL
- How to handle SQL injection in mssql node module
- SQL Server How To Transpose Rows To Columns, without PIVOT or UNPIVOT or Aggregation
- SQL Server : most frequent value in each row
- SQL Query to Select by String ID
- How do I rounding DateTime to 6 characters after the second?
- For Each Loop to iterate all the tables name(SQL)
- How to install latest python version for SQL Server
- Create SQL Server CE database file programmatically
- Why use JOIN rather than inner queries
- XML taking to long to parse in MSSQL
- Table row in sql as new table
- Migrating data from SQL Server to NoSQL
- How to convert CamelCase to a string with a space in T-SQL
- Ctrl+N doesn't open new query window in Management Studio (MS SQL 2008)
- Windows application deployment with SQL Server database and Entity Framework error
- Generate XML comments with SQL FOR XML statement
- SQL Server - displaying data "downward"
- SQL JOIN with TOP ID from WHERE clause
- SELECT FROM inner query slowdown
- Ignore identical values in a specific column
- Read FTP files without local folder in SSIS package
- delete rows not correspondent in sql tables
- What do you do in SQL Server to CREATE OR ALTER?
- Use SQL OpenRowSet BulkColumn to Insert data from .txt File
- Good resources for learning database optimization part
- CMD command to install sql server 2005 express edition via command prompt (silent install)
- How to get Count from FromSql?
- SQL pivot table on text value
- TFS / SSDT Deployment in Multi Environment Scenario
- Implementing search criteria using JSP and SQL Server 2008?
- Simple SQL math operation gives incorrect results
- How can I replace duplicate strings with increasing order in T-SQL?
- SQL Server 2008: I/O Wait Time per Database File
- Is it possible to iterate over a list of numbers in tsql?
- SQL Server - Select daily values for each month
- SQL Number Formatting
- How to write this WHERE filter in my T-SQL Query that will cater for 2 conditions to be met?
- SQLServer can't create table, "Already exists", but it doesn't AFAICT
- Pivoting columns based on a key
- Connection leaks in Classic ASP using Server.CreateObject("ADODB.Connection")
- Adding textbox values to an SQL database in c#
- Possible to hide columns in a SQL Server Management Studio database diagram?
- can't establish connection between PHP and SQL Server (Unix)
- Downloading a file after storing it in SQL Server 2008 Data Base
- django Microsoft SQL server migrate issue django.db.migrations.exceptions.MigrationSchemaMissing
- Fill rows for missing data by last day of month
- Retrieving current timestamp of the database
- Remove non-unique ids rows
- Replace value in XML using SQL
- Element Present in One group must present with other too
- SQL Server HASHBYTES SHA2_512 and C#
- How to select distinct records with where conditions without using sub queries
- Case statement in WHERE clause in SQL
- SQL Server sub query
- SQL server - cumulative values in a column
- Huge Errorlog with SQL Server 2005 Express (15GB)
- Is it possible to use LowerCase or UpperCase with Select *
- How to search the column values
- ASP.NET Edit and Delete issues in GridView
- Why is LINQ expression causing two separate queries on the DB?
- SQL error in subquery
- what is the CLSID and PROG ID of Microsoft.sqlserver.Management.Smo
- How to convert negative value into zero
- Convert a 9 digit Canadian SIN to an 11 character SIN (adding spaces) in SQL Server
- Count(*) in a subquery
- I can't seem to get the syntax correct to call a stored procedure in a Select statement
- How to convert a datetime string to datetime in SQL Server
- How do I correct a Stored Procedure Error #1934? (SQL Server / PHP)
- how to add last values of same group in sql query
- SQL Collation Issue on JOIN clause -- EXTREMELY slow runtime
- How to add condition to COALESCE while reading xml in Sql
- SQL Server Where clause with Case Checking null value
- SQL combine rows for date table
- Short SQL Server Rank/Grouping Script
- How to get a unique identifier from a stored procedure?
- Create date column from 3 strings in SQL
- How to find UserDefined Objects that reference System tables/functions in SQL Server
- Cannot connect Docker MsSql from AspNetCore app
- SSIS 2008 Flat File Source Deployment
- Question mark issue with ADOdb and MSSQL
- It is possible to alter SQL Server replication filter without delivering an entire publication snapshot?
- A bigger database has almost all disk space but 54% of free space on it. Do I have to shrink it?
- Return Table from Store Procedure - SQL Server
- In what order does execution on WHERE and ON clauses work?
- SQL Server > Query > Column Names
- How to simulate a DB connection timeout
- How to replace a backslash with a double backslash without replacing existing double backslashes in SQL
- how can I make a minus in this query?
- Generate date from serial number
- Size of a single Record ? SQL
- UNIQUE constraint controlled by a bit column
- How to get the rows without duplicate cells in result set
- SQL automatic date range using DateSerial function
- Logging the SQL generated by LINQ to SQL in Entity Framework in .net