SQL Server trigger after insert with examples

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

The triggers can be created on the tables. There can be multiple triggers 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 the event 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

You will various examples in the upcoming sections.

• SQL Server trigger after insert example
• SQL Server trigger get inserted record
• SQL Server trigger after insert with condition
• SQL Server trigger after insert execute stored procedure
• SQL Server trigger after insert update another table
• SQL Server before insert example
• SQL Server trigger insert instead of
• SQL Server trigger after insert copy row to another table
• SQL Server trigger after insert get id
• SQL Server trigger after insert send email
• SQL Server trigger prevent insert
• SQL Server trigger after insert if not exists
• SQL Server trigger after insert update same row
• SQL Server after insert trigger on view
• SQL Server trigger after insert raiserror
• SQL Server trigger after insert rollback
• SQL Server trigger after insert scope_identity

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 where we will create a trigger on a table in the SQL server.

• Consider the following 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 will be 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)

• 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 which 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')

• 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.

Read SQL Server Substring Function [9 Examples]

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:

• 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')

• 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')

• 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 you can create a trigger by specifying a condition.

Read SQL Server Convert Function + Examples

SQL Server trigger after insert execute stored procedure

When you insert some records into a table, you may want to insert those values into another table for maintaining logs or for some other reasons also. A good approach is to use a separate stored procedure instead of writing the whole set of SQL statements inside a trigger.

In this section, you will learn how to execute a stored procedure inside a trigger using the inserted values in SQL server 2019. We will demonstrate this with an example.

• We have a table named Persons. Whenever we insert a record into it, some values should also be inserted in the other table named Names. The question is how to do this.
• Well, we will create a stored procedure that will insert the desired records into the Names table. We will call this procedure inside the trigger that will be fired whenever a record will be inserted into the Persons table.

• We have created the following stored procedure to insert values into the Names table:

USE [master]
GO
CREATE PROCEDURE [dbo].[InsertIntoNames]
@FName nchar(10), @LName nchar(10), @FullName nchar(30)
AS
BEGIN
INSERT INTO dbo.Names([First Name], [Last Name], [Full Name])
VALUES(@FName, @LName, @FullName)
END

• The above stored dbo.InsertIntoNames takes three input parameters i.e First Name, Last Name and Full name of the person.
• Now we will create a trigger on the Persons table that will be fired whenever a row will be inserted into the table.

CREATE TRIGGER 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
EXEC dbo.InsertIntoNames @Firstname, @LastName, @FullName

END

• In the above trigger, we are calling the dbo.InsertIntoNames stored procedure with the three input parameters.
• Now let us try to insert a record in the Persons table.

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')

• Let us verify if the records are inserted in both the tables or not.

• You can observe that when we inserted a record in the Persons table, a record with the same values(First Name, Last Name, Full Name) is inserted into the Names table also.

Thus, in this way you can use a trigger to execute a stored procedure along with the input parameters.

Read Create Foreign Key in SQL Server

SQL Server trigger after insert update another table

In this section, you will learn how to use a trigger after an Insert statement that will update the data in another table in SQL server.

• Consider the two tables:
  • OrderDetails: Stores orders information.
  • Sales: Stores total sales of a salesman

• All the sales amount from the table OrderDetails is saved in the Sales table according to the Salesman ID.
• We will create a trigger on the OrderDetails table such that whenever we will insert a row in this table, the total sales amount of the salesman will also be updated in the Sales table.

USE master
GO
CREATE TRIGGER TriggerOrderDetails
ON dbo.OrderDetails
AFTER INSERT
AS
BEGIN
	DECLARE
	@Amount real,
	@SalesmanID int
	SELECT @Amount= INSERTED.Amount,
	@SalesmanID= INSERTED.[Salesman ID]
	FROM INSERTED
	UPDATE dbo.Sales
	SET [Total Sales]= [Total Sales] + @Amount
	WHERE [Salesman ID]= @SalesmanID
END

• We will insert a row in the OrderDetails table and check if the data is updated in the Sales table.

USE master
GO
INSERT INTO dbo.OrderDetails
([Order Number], Amount, Date, [Customer ID], [Salesman ID])
VALUES(8010, 500, GETDATE(), 4196, 5102)

• You can see that the Total Sales of the Salesman with the Salesman ID as 5102 is updated.

Thus, you might have understood how you can use a trigger after the Insert statement to update another table.

Read Types of Backup in SQL Server

SQL Server before insert example

The BEFORE INSERT trigger type is not supported by SQL Server. SQL Server supports only two types of triggers:

• AFTER
• INSTEAD OF

However, if you want the same functionality as the BEFORE INSERT Trigger, you can use the INSTEAD OF INSERT trigger. The INSTEAD OF INSERT trigger will prevent the insert statement from running and only the SQL code inside the trigger will learn.

Inside the trigger body, firstly, write the queries that you want to execute before the Insert statement as you would write in the BEFORE INSERT trigger. Then write the records that you want to insert in an Insert statement.

If you want to know how the INSTEAD OF INSERT trigger works, you can read the section just below. You will learn about the INSTEAD OF INSERT trigger with the help of an example.

SQL Server trigger insert instead of

In this section, you will learn to use the INSTEAD OF type trigger in SQL Server. Suppose we have created an INSTEAD OF INSERT trigger on a table. If we try to insert a row in the table, the code inside the trigger body will run only. This means the statements will not be inserted.

This type of trigger is very useful. You can use these triggers for validation of data before insertion, checking for constraints, etc. We will create this type of trigger in the example explained below:

• We have created a table named SumTable.
• It has three columns:
  • First Number
  • Second Number
  • Sum of Numbers
• The column Sum of Numbers stores the sum of the two columns.
• You can also try this example on your own end by copying the SQL code below:

USE master
GO
DROP TABLE IF EXISTS dbo.SumTable
CREATE TABLE dbo.Sumtable(
[First Number] real,
[Second Number] real,
[Sum of Numbers] real
)

• Once the table is created, we will insert a record in the two columns i.e First Number and Second Number.
• We will create a trigger on this table that will calculate the sum of two numbers and store the sum in the Sum of Numbers column.

USE master
GO
CREATE TRIGGER TriggerSumTable
ON dbo.SumTable
INSTEAD OF INSERT
AS
BEGIN
	DECLARE
		@Fnum real,
		@Snum real,
		@Sum real
	SELECT @Fnum= INSERTED.[First Number],
		   @Snum= INSERTED.[Second Number]
		   FROM INSERTED
	SET @Sum= @Fnum+ @Snum
	INSERT INTO dbo.SumTable(
	[First Number], [Second Number], [Sum of Numbers])
	VALUES(@Fnum, @Snum, @Sum)
END

• Now we will insert a record in the table as our requirement:

USE master
GO
INSERT INTO dbo.SumTable( [First Number], [Second Number])
VALUES(1000, 1500)

• You can observe in the table that a row is inserted along with the sum.

Thus, you might have learned how you can use the INSTEAD OF type of trigger with the INSERT statement.

Read Saving changes is not permitted in SQL Server

SQL Server trigger after insert copy row to another table

In this section, you will learn how to copy a row to another table using a trigger after the Insert statement in the SQL server 2019. Sometimes you want to insert a row in two similar tables. You can create a trigger on a table that will also insert the same row into another table.

• Consider the following two tables:
  • Customers: The original table on which we will create a trigger.
  • NewCustomers: The table into which you want to copy the rows from the Customers table. This table will be identical to the Customers table in structure.

• Initially, the Customers table have some records in the table but the NewCustomers table is empty.
• Let us create a trigger on the table Customers.
• After creating the trigger, when we will insert the values in the Customers table, the same row will be copied into the NewCustomers table.

USE master
GO
CREATE TRIGGER TriggerCustomers
ON dbo.Customers
AFTER INSERT
AS
BEGIN
	DECLARE
		@CustomerID int,
		@Name nchar(20)
	SELECT @CustomerID= INSERTED.[Customer ID],
	@Name= INSERTED.[Customer Name]
	FROM INSERTED
	INSERT INTO dbo.NewCustomers(CustomerID, [Customer Name])
	VALUES(@CustomerID, @Name)
END

• Now we will insert a record in the Customers table.

USE master
GO
INSERT INTO dbo.Customers([Customer ID], [Customer Name])
VALUES(1263, 'Christiano')

• You can observe in the below image that the same row is also copied into the NewCustomers table.

Hence, you might have understood more about the triggers and how you can use these.

SQL Server trigger after insert get id

When you insert a row in a table having an identity column, you do not insert the value in the identity column because the values are automatically assigned to that column in sql server 2019.

Suppose you are using an insert trigger on a table having an identity column, for example, inserting the same rows to another table. If you want to access the identity values, you have to access these from the INSERTED table. This table stores the record that you are going to insert.

Let us understand this with an example.

• Consider the following TableIdentity table.
• The table has three columns:
  • EmpName
  • EmpDep
  • EmpID
• The EmpID column is an identity column in this table.
• Suppose we want to create a new table with same structure:

USE master
GO
DROP TABLE IF EXISTS dbo.NewTableIdentity
CREATE TABLE dbo.NewTableIdentity(
[Employee Name] nchar(20),
[Employee Department] nchar(10),
[Employee ID] int
)

• The new table is NewIdentityTable.
• Initially, both the tables are empty.
• We want to create an insert trigger that will insert the values in the new table also.
• As you know, we will not specify the value for identity column while inserting a row. But, we will need those identity column values for inserting in the new table.
• Therefore, we will use the INSERTED table for this purpose. Let us see how.
• Create the insert trigger on the TableIdentity table.

USE master
GO
CREATE TRIGGER TriggerTableIdentity
ON dbo.TableIdentity
AFTER INSERT
AS
BEGIN
	DECLARE
		@EmpName nchar(20),
		@EmpDep nchar(10),
		@EmpID int
	SELECT @EmpName= INSERTED.[EmpName],
		   @EmpDep= INSERTED.[EmpDep],
		   @EmpID= INSERTED.[EmpID]
		   FROM INSERTED

	INSERT INTO dbo.NewTableIdentity(
	[Employee Name], [Employee Department], [Employee ID])
	VALUES(@EmpName, @EmpDep, @EmpID)
END

• You can access the identity column values from the INSERTED table.
• Now insert a row in the TableIdentity table.

USE master
GO
INSERT INTO dbo.TableIdentity(EmpDep, EmpName)
	Values('Finance', 'David Miller')

• Lets us insert one more row in the table.

USE master
GO
INSERT INTO dbo.TableIdentity(EmpDep, EmpName)
	Values('Sales', 'Heath Williams')

• Check the inserted row in the TableIdentity table and the NewTableIdentity table.

• You can observe that we did not specify the identity column value while inserting a row, but the value still got inserted in the tables.

Thus, here we learned how you can access the identity column values in an insert trigger.

Read Delete Duplicate Rows in SQL Server

SQL Server trigger after insert send email

Sometimes, we need to send notification emails whenever there is a change in the database. These mails are generally sent to the Database Administrator so that they are aware of the changes made in the database.

We implement this functionality with the help of triggers. Whenever there are some changes in the database, the trigger is fired and the mail is sent.

In this section, you will learn how you can send emails using a trigger on a database table. You will require to configure the mail profile in the SQL Server.

When you create a mail profile, you configure the mail service you are using i.e. login credentials and details about the mail server. We are assuming that you have already configured your mail profile in SQL Server.

• For example, there is a Student table.
• You want to send an email from SQL Server whenever a new student record is inserted.
• You can create the trigger on the Student table as:

CREATE TRIGGER dbo.TriggerStudent
       ON dbo.Student
AFTER INSERT
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE
		@CollegeID int,
		@MailBody nchar(70)
	SELECT @CollegeID= INSERTED.[College ID] FROM INSERTED
	SET @MailBody= 'A new student with the college ID '+ STR(@CollegeID)+ 'is added'
    EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'Test_Profile'
           ,@recipients = 'recipient@gmail.com'
           ,@subject = 'Sending the Test Email'
           ,@body = @MailBody
END

• The sp_send_dbmail is a built in stored procedure in the msdb database that we use to send mails. We specify various options as the input parameters in this stored procedure.
  • @profile_name: This is the name of the mail profile that you will use to send the mail. A profile contains the mail server information.
  • @recipients: This is the recipients email address i.e. to whom you want to send the mail.
  • @subject: The subject of the mail to be sent.
  • @body: The body of the mail to be sent.
• In the above example, we are notifying a Database Administrator that a new student record is added.

Thus, we have understood how to send emails using triggers.

SQL Server trigger prevent insert

You can prevent the insert statement transaction with the help of triggers. But, the question is why would you want to do that. Well, the answer is sometimes you need to validate some things. These can be validations to control duplicate values or the validations about constraints applied on the table.

There are multiple ways to do this. You can use the INSTEAD OF type of trigger. You can also the ROLLBACK command to prevent the changes. We will create an example of the INSTEAD OF type trigger in this section.

• We have a table named EmployeeDetails.

• We will implement a validation which will check if the record is already present in the table or not inside a trigger.

USE master
GO
CREATE TRIGGER TriggerEmployeeDetails
ON dbo.EmployeeDetails
INSTEAD OF INSERT
AS
BEGIN
	DECLARE
		@EmpID int,
		@EmpName nchar(20),
		@EmpDep nchar(20)
	SELECT @EmpID= INSERTED.[Employee ID],
		   @EmpName= INSERTED.[Employee Name],
		   @EmpDep= INSERTED.[Employee Department]
		   FROM INSERTED
		IF EXISTS(SELECT * FROM dbo.EmployeeDetails
				  WHERE [Employee ID]= @EmpID)
			PRINT('Record is already in the table')
		ELSE
			INSERT INTO dbo.EmployeeDetails(
				[Employee ID], [Employee Name], [Employee Department])
			VALUES(@EmpID, @EmpName, @EmpDep)
END

• Let us insert a record which is not available in the table.

USE master
GO
INSERT INTO dbo.EmployeeDetails(
	[Employee ID], [Employee Name], [Employee Department])
	VALUES(1011, 'Joseph', 'Accounts')

• You can see that the record is inserted.

• But what if we try to insert the same record. This time it should not be inserted as it is already stored in the table.

USE master
GO
INSERT INTO dbo.EmployeeDetails(
	[Employee ID], [Employee Name], [Employee Department])
	VALUES(1011, 'Joseph', 'Accounts')

• The desired message is displayed and if we check the EmployeeDetails table, the record in not inserted.

Thus, in this way, you can prevent the INSERT statement.

Read IDENTITY_INSERT in SQL Server

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. But 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

• The StudentID table contains only the names and IDs of teh 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')

• 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')

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.

Read SQL Server Row_Number

SQL Server trigger after insert update same row

In this section, you will see an example in which we will update the same row using an after insert trigger.

• Consider the following StudentMarks table.
• The table will store the marks and grades of the students.
• You can also create this table using the code given below:

USE [master]
GO
DROP TABLE IF EXISTS dbo.StudentMarks
CREATE TABLE [dbo].[StudentMarks](
	[Student ID] [int] NOT NULL,
	[Student Name] [nchar](20) NOT NULL,
	[Marks] [real] NOT NULL,
	[Grade] [nchar](1) NULL
) ON [PRIMARY]
GO

• We will populate it after we define a trigger on the table.
• While inserting the rows, we will not insert the value for the grade column. The trigger defined on the table will update the value of the grade column according to the condition defined on the marks.

USE master
GO
CREATE TRIGGER TriggerGrade
ON dbo.StudentMarks
AFTER INSERT
AS
BEGIN
	DECLARE
		@Marks real,
		@Grade nchar(1),
		@StudentID int
	SELECT @Marks= INSERTED.Marks FROM INSERTED
	SELECT @StudentID= INSERTED.[Student ID] FROM INSERTED
	IF @Marks> 90
		SET @Grade= 'A'
	ELSE
	BEGIN
		IF @Marks> 70
			SET @Grade= 'B'
		ELSE
		BEGIN
			IF @Marks> 50
				SET @Grade= 'C'
			ELSE
			BEGIN
				IF @Marks> 34
				 SET @Grade= 'D'
				ELSE
					SET @Grade= 'F'
			END
		END
	END
	UPDATE dbo.StudentMarks
	SET Grade= @Grade WHERE [Student ID]= @StudentID

END

• Once the trigger is created, we will insert a few rows in the table.
• But keep in mind not to insert all the values in a single batch. The SQL Server triggers are not executed for every row. The code inside the trigger is fired only once at the end.

USE master
GO
INSERT INTO dbo.StudentMarks(
[Student ID], [Student Name], Marks)
	VALUES(4001, 'Sasha Brooke', 80)

USE master
GO
INSERT INTO dbo.StudentMarks(
[Student ID], [Student Name], Marks)
	VALUES(4002, 'Lily Wicky', 92)

USE master
GO
INSERT INTO dbo.StudentMarks(
[Student ID], [Student Name], Marks)
	VALUES(4003, 'Karmen Kamra', 30)

USE master
GO
INSERT INTO dbo.StudentMarks(
[Student ID], [Student Name], Marks)
	VALUES(4004, 'Sam Curran', 45)

USE master
GO
INSERT INTO dbo.StudentMarks(
[Student ID], [Student Name], Marks)
	VALUES(4005, 'Katy Smith', 65)

• If we have a look at the table, we can see that the values for the Grade column are updated by the trigger.

Thus, you might have learned how you can update the same row using an after insert trigger in SQL Server.

Read SQL Server Add Column + Examples

SQL Server after insert trigger on view

In SQL Server, you cannot create an after insert trigger on a view. However, you can create an INSTEAD OF trigger to make the task done. Generally, a view is created from multiple base tables. When you try to insert a record into a view, changes have to be made to all the tables that are used for creating the view. In this case, you get an error.

However, you can use the INSTEAD OF INSERT type trigger as this trigger prevents the direct use of the INSERT statement and implements the code defined inside the trigger instead of the Insert statement.

SQL Server trigger after insert raiserror

Raiserror is used to throw a user-defined exception in SQL Server. We can use it with a trigger for validation. If the validation fails, we will throw an exception. We will create an example where we will use the raiserror function to throw an error inside an insert trigger.

• Consider the following Login table:

• We will create a trigger on this table for validating if the login credentials that we are trying to insert is already stored in the table or not.
• If the record is already stored in the table, the SQL program will throw an exception. Otherwise, the row will be inserted into the table.

USE master
GO
CREATE TRIGGER TriggerLogin
ON dbo.Login
INSTEAD OF INSERT
AS
DECLARE 
    @ErrorMessage  nvarchar(4000), 
    @ErrorSeverity int, 
    @ErrorState    int;

BEGIN TRY
	DECLARE
		@Username nchar(20),
		@Password nchar(20)
	SELECT @Username= INSERTED.[Username],
		   @Password= INSERTED.[Password]
		   FROM INSERTED
	IF EXISTS(SELECT * FROM dbo.Login
				WHERE Username= @Username AND Password= @Password)
    RAISERROR('Oops! The User ID and the password combination already exists', 10, 1)
	ELSE
		INSERT INTO dbo.Login(Username, Password)
		VALUES(@Username, @Password)
END TRY
BEGIN CATCH
    
END CATCH;

• We will insert a new row in the table.

USE master
GO
INSERT INTO dbo.Login( Username, Password)
VALUES('Carlos', '@124carlos')

• As the row is a new row, it will be inserted into the table.
• Now inserting this row again will throw an exception.

USE master
GO
INSERT INTO dbo.Login( Username, Password)
VALUES('Carlos', '@124carlos')

• As you can observe in the output, the error message is displayed that we defined in the RAISERROR function and the record is not inserted.

Thus, you might have understood how you can use the RAISERROR function with an insert trigger for handling the exceptions.

Read SQL Server drop table if exists

SQL Server trigger after insert rollback

Sometimes, you need to roll back the insert transaction if a certain condition is not met. In such a case, you can use an AFTER INSERT trigger to check for the condition. If the condition is met, the transaction will be committed, otherwise, the transaction will be rolled back.

• Consider the below Employees table and the Department table.

• We will insert a row in the employees table.
• Then we will create a trigger on the Employees table which will check if the Department ID inserted in the Employees table is available in the Department table or not i.e. if the inserted department exists or not.
• If the department exists, the transaction will be commited, else the transaction will be rolled back.

USE master
GO
CREATE TRIGGER [dbo].[TrigEmployees]
ON [dbo].[Employees]
AFTER INSERT
AS
BEGIN
	DECLARE
		@DepID int
	SELECT @DepID= INSERTED.DepID FROM INSERTED
	IF NOT EXISTS(SELECT * FROM dbo.Department WHERE DepID= @DepID)
		ROLLBACK TRANSACTION
END

• Now let us try to insert a row with a valid department ID.

BEGIN TRANSACTION
INSERT INTO dbo.Employees(EmpID, EmpName,DepID)
VALUES(1011, 'Johnny', 12)
COMMIT TRANSACTION

• As the department is valid, the transaction is committed.
• Now try to insert a row with an invalid department ID.

BEGIN TRANSACTION
INSERT INTO dbo.Employees(EmpID, EmpName,DepID)
VALUES(1011, 'Johnny', 16)
COMMIT TRANSACTION

• You got the error saying that the transaction is aborted i.e. the insert statement is rolled back.

Thus, you might have learned how you can use the rollback statement after insert trigger in SQL Server.

SQL Server trigger after insert scope_identity

The SCOPE_IDENTITY() function can be used to get the identity value of the last inserted row in a scope. You might be thinking that what is a scope. A scope is just a module i.e. functions, stored procedures, triggers, etc. For example, if you have two insert statements in functions, both the statements are in the same scope.

The use of the SCOPE_IDENTITY function is recommended over the @@IDENTITY function because the SCOPE_IDENTITY is limited to a scope. This means it will not interfere with the triggers and reduce the complexity of the database schema.

However, if you want to get the last inserted identity value and use it with a trigger, you should use the @IDENTITY function. This is because the @@IDENTITY function is not limited to a scope. It is limited to a session. Therefore, if you are using an after insert trigger, you should use the @@IDENTITY function to get the last inserted identity value from the current session.

You may also like the following SQL server tutorials:

• Msg 11555 NOT NULL parameters are only supported with natively compiled modules, except for inline table-valued functions
• SQL Server stored procedure return value

Here we learned SQL Server trigger after insert with a few examples.

• SQL Server trigger after insert example
• SQL Server trigger get inserted record
• SQL Server trigger after insert with condition
• SQL Server trigger after insert execute stored procedure
• SQL Server trigger after insert update another table
• SQL Server before insert example
• SQL Server trigger insert instead of
• SQL Server trigger after insert copy row to another table
• SQL Server trigger after insert get id
• SQL Server trigger after insert send email
• SQL Server trigger prevent insert
• SQL Server trigger after insert if not exists
• SQL Server trigger after insert update same row
• SQL Server after insert trigger on view
• SQL Server trigger after insert raiserror
• SQL Server trigger after insert rollback
• SQL Server trigger after insert scope_identity