SQL Server Trigger After Insert

We can fire triggers after an SQL statement to perform a specific task. A trigger is a set of SQL statements defined to perform a specific task you can fire after a certain event. This tutorial will discuss SQL Server trigger after insert with a few examples.

The triggers can be created on the tables. Multiple triggers can be created on a table. We will discuss some use cases and examples of triggers. You will see how you can create triggers on the table.

We use the CREATE TRIGGER statement to create a trigger. We specify the table name, type of trigger i.e. Before or After. This means whether the trigger will be fired before or after the event. We use the following general syntax:

CREATE TRIGGER <Trigger Name>
ON <table name>
AFTER|BEFORE <event>
AS
BEGIN
	<Code to be run when the trigger will be fired>
END

All the examples I have done here are by using sql server 2019.

SQL Server trigger after insert example

In this section, you will see an example of how to create a trigger after insert in sql server on a table in the SQL server.

  • Consider the following Products table:
sql server trigger after insert
Products Table
  • We will create a trigger on this Products table that will display a message on the output screen.
CREATE TRIGGER InsertProducts
ON dbo.Products
AFTER INSERT
AS
BEGIN
	PRINT('Record(s) inserted successfully')
END
  • In the above code, the name of the trigger is InsertProducts.
  • We have created this trigger on the Products table.
  • We have specified AFTER INSERT which means the trigger will be fired after the INSERT statement.
  • In the body of the trigger, we have specified the message that it will display when a new record is inserted.
  • You can also create a trigger using the Object Explorer Window of SQL Server Management Studio.
    • You can expand the table on which you want to create the trigger. You will see the Triggers option when you will expand the table.
    • Right click on Triggers and click New Trigger to create a new trigger.
    • It will create a template for the trigger. You can directly write the body of the trigger using this method.
  • Now let us try to insert a record in the Products table and see if the triggers work accordingly.
USE master
GO
INSERT INTO dbo.Products([Product ID], Name, Price, Rating)
VALUES(1248, 'Hair Oil', 150, 8)
sql server trigger after insert example
  • You can see the message on the output screen indicating that the trigger worked.

Thus, you might have got a basic idea of how the trigger works. We will discuss more examples in this article that will help you to understand more.

SQL Server trigger get inserted record

You have learned how to create a trigger after the Insert statement in sql server. Now you will learn how you can get the inserted values and use them later.

  • We have created a table named Persons. We will create a trigger on this table which will be fired when a new record will be inserted into it.
  • We will create the trigger with the below query:
USE master
GO
CREATE TRIGGER InsertPersons
ON dbo.Persons
AFTER INSERT
AS
BEGIN
DECLARE
@FirstName nchar(30),
@LastName nchar(30),
@Location nchar(30)
SET NOCOUNT ON;
SELECT @FirstName= INSERTED.[First Name],
@LastName= INSERTED.[Last Name],
@Location= Location FROM INSERTED
PRINT(@FirstName+ @LastName+ @Location)
END
  • The INSERTED table stores the data that is inserted or updated using the Insert or Update statement.
  • We can use this table to get the inserted values and use them anywhere.
  • Now, we will try to insert values in the table and verify if the trigger is working or not.
USE [master]
GO
INSERT INTO dbo.Persons
([First Name], [Last Name], Age, Gender, Email, Phone, Location)
VALUES('Veruca', 'Williams', 39, 'Female',
'williams123veruca@yahoo.com', '+1 505 978 297', 'Taos')
sql server trigger after insert get inserted value
The Output of the Trigger
  • You can see the trigger is working. You can use these values anywhere you want. For example, maintaining logs or storing some values in another table.

SQL Server trigger after insert with condition

In this section, you will learn how to use a trigger with a conditional statement after the Insert statement. We will create a trigger on a table and include a condition inside it.

For example, we have a table named Persons and a table named Names. We will create a trigger that will be fired after an Insert statement for the table Persons.

The trigger will insert a new record in the Names table with a condition. The condition will check if the record is already in the Names table or not. If the record is not already in the table, it will insert a new row, otherwise, it will not insert a new row in the table.

  • The two tables are shown in the below images:
sql server trigger after insert
Persons Table
sql trigger after insert
Names Table
  • We will create a trigger on the Persons table:
USE [master]
GO
CREATE TRIGGER [dbo].[InsertSP]
ON [dbo].[Persons]
AFTER INSERT
AS
BEGIN
	DECLARE
		@FirstName nchar(10),
		@LastName nchar(10),
		@FullName nchar(30)
	SELECT @FirstName= INSERTED.[First Name],
	@LastName= INSERTED.[Last Name] FROM INSERTED
	SET @FullName= @FirstName+ @LastName

	IF EXISTS(SELECT * FROM dbo.Names WHERE [First Name]= @FirstName AND [Last Name]= @LastName)
	BEGIN
		PRINT('Record Already Exists in the Names Table')
	END
	ELSE
	BEGIN
		INSERT INTO dbo.Names([First Name], [Last Name], [Full Name])
		VALUES(@FirstName, @LastName, @FullName)
		PRINT('Record Inserted in the Names Table')
	END	
END
  • The above trigger will only insert the record in the Names table if the same record does not already exist. Otherwise, it will display a message ‘Record Already Exists in the Names Table’.
  • Let us insert a record into the Persons table such that the same values are not available in the Names persons.
USE [master]
GO
INSERT INTO dbo.Persons
([First Name], [Last Name], Age, Gender, Email, Phone, Location)
VALUES('Rosy', 'Jones', 28, 'Female',
'jonesrosy@gmail.com', '+1 701 915 571', 'Medora')
trigger after insert sql server
New Row Inserted in the Persons Table
  • You can see the row is inserted.
  • Now assume that we are inserting a row in the Persons table, but the same row values are already stored in the Names table.
  • Let us see what happens when we insert such a row:
USE [master]
GO
INSERT INTO dbo.Persons
([First Name], [Last Name], Age, Gender, Email, Phone, Location)
VALUES('Rosy', 'Jones', 28, 'Female',
'jonesrosy@gmail.com', '+1 701 915 571', 'Medora')
after insert trigger sql server
Row Inserted in the Persons Table but not in the Names Table
  • As the record was already stored in the Names table, the trigger did not insert the record and displayed the message.

Thus, you might have learned how to create a trigger after insert in sql server by specifying a condition.

SQL Server trigger after insert if not exists

We can use the IF NOT EXISTS statement to verify if a record already exists in the table. In this section, you will see an example where we will use this statement in an after insert trigger.

We will insert a record in a table, resulting in the firing of a trigger that will insert the same row in another table. However the record will only be inserted if the same record does not exist already in the table.

  • We have the following two tables:
    • Student
    • StudentID
after insert trigger sql server
Student Table
sql server create trigger after insert
StudentID Table
  • The StudentID table contains only the names and IDs of the student from the Student table.
  • We will create an after-insert trigger on the Student table that will insert a row in the StudentID table.
USE master
GO
CREATE TRIGGER TriggerStudent
ON dbo.Student
AFTER INSERT
AS
BEGIN
	DECLARE
		@FirstName nchar(30),
		@LastName nchar(30),
		@CollegeID int
	SELECT @FirstName= INSERTED.[First Name],
			@LastName= INSERTED.[Last Name],
			@CollegeID= INSERTED.[College ID]
			FROM INSERTED
	IF NOT EXISTS(SELECT * FROM dbo.StudentID WHERE CollegeID= @CollegeID)
	BEGIN
		INSERT INTO dbo.StudentID(
		[First Name], [Last Name], CollegeID)
		VALUES(@FirstName, @LastName, @CollegeID)
	END
	ELSE
		PRINT('Record Already Exists in the StudentID table')
END
  • Now if we insert a new row in the Student table, the valuesof the same row will be inserted into the StudentID table.
USE master
GO
INSERT INTO dbo.Student(
	[College ID], [First Name], [Last Name], Stream, [E mail], Phone)
	VALUES(1987, 'Lisa', 'Brooke', 'Arts', 'lisa.brooke.321@gmail.com', '1978457345')
mssql trigger after insert
Record Inserted by the Trigger
  • As this row is already stored in the StudentID table, if we try to insert the same row again, the row will not be inserted.
USE master
GO
INSERT INTO dbo.Student(
	[College ID], [First Name], [Last Name], Stream, [E mail], Phone)
	VALUES(1987, 'Lisa', 'Brooke', 'Arts', 'lisa.brooke.321@gmail.com', '1978457345')
trigger sql server after insert
The row is not Inserted

Thus, you might have learned how you can use the IF NOT EXISTS statement in an after insert trigger to verify the availability of the record in the table.

Conclusion

In this tutorial, I have explained how to create a trigger after insert in sql server. I have shown a few examples of “sql server trigger after insert“.

You may like: