In this article we will show you, How to write the INSTEAD OF INSERT Triggers in SQL Server with example. For this demonstration, We are going to use the below shown tables. As you can see from the below screenshot that our Employee table is Empty, and our task is to create INSTEAD OF INSERT TRIGGER on this Employee table. And by using this trigger, we want to restrict the records inserting into Employee Table, and also insert records into the Audit table, along with the audit information
TIP: You can refer INSTEAD OF DELETE Triggers in SQL Server article to understand the INSTEAD OF INSERT Trigger, and INSTEAD OF UPDATE Triggers in SQL Server article for INSTEAD OF UPDATE Trigger.
and our Employee Table Audit is also empty
NOTE: SQL Instead of Insert Triggers can be created in Tables, and Views. In general ,we us them on View
Instead Of INSERT Triggers in SQL Server Example
In this example we will show you, How to create an Instead of insert Triggers in SQL Server. And here, we will create a trigger on Employee table using the CREATE TRIGGER Statement.
From the below code snippet you can see that, we are using the INSERT INTO SELECT Statement to select all the records that are inserting into Employee table, and then
- We are inserting the records whose yearly Income is less than or equal to 70000 into the Audit table, and.
- Whose yearly Income is greater than 70000 into the Employee table
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
-- Example for INSTEAD OF INSERT Triggers in SQL Server USE [SQL Tutorial] GO CREATE TRIGGER InsteadOfINSERTTriggerExample on [EmployeeTable] INSTEAD OF INSERT AS BEGIN INSERT INTO [EmployeeTableAudit]( [ID], [Name], [Education], [Occupation], [YearlyIncome], [Sales], [ServerName], [ServerInstanceName], [Insert Time]) SELECT ID, Name, Education, Occupation, YearlyIncome, Sales, CAST( SERVERPROPERTY('MachineName') AS VARCHAR(50)), CAST( SERVERPROPERTY('ServerName') AS VARCHAR(50)), GETDATE() FROM INSERTED WHERE YearlyIncome <= 70000; PRINT 'We Successfully Fired Our INSTEAD OF INSERT Triggers in SQL Server.'; INSERT INTO [EmployeeTable]( [Name], [Education], [Occupation], [YearlyIncome], [Sales]) SELECT Name, Education, Occupation, YearlyIncome, Sales FROM INSERTED WHERE YearlyIncome > 70000; END GO |
OUTPUT
ANALYSIS
Here, we used the INSERT Statement to insert the selected values into Employee Table. And the following statements will return the Machine Name, and the server name. This information might be helpful for the audit
|
1 2 |
SERVERPROPERTY('MachineName'), SERVERPROPERTY('ServerName') |
Let me show you the newly created trigger by opening the Object explorer -> Go to the SQL Tutorial Database -> Go and expand the Employee Table -> and then expand the Triggers Folder
For the demonstration purpose we are inserting 14 records present in the Employee into the Employee table, to check whether the Instead of insert Trigger is triggered or not.
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
USE [SQL Tutorial] GO INSERT INTO [EmployeeTable] ([Name] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales]) SELECT [Firstname] + ' '[LastName] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] FROM [Employee] |
OUTPUT
From the above screenshot you can see that, our trigger is triggered. And, instead of inserting 14 records into the Employee table, it inserted 8 records into the Audit Table, and 6 records into Employee table. Please use the following SQL Query to check the inserted records in Employee table
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 |
USE [SQL Tutorial] GO SELECT [ID] ,[Name] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] FROM [EmployeeTable] |
OUTPUT
Next, check the records in Employee table Audit using the following query.
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 |
USE [SQL Tutorial] GO SELECT [Name] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] ,[ServerName] ,[ServerInstanceName] ,[Insert Time] FROM [EmployeeTableAudit] |
OUTPUT
You can also try with the conditions, something like: Insert the records into Employee table whose Yearly Income is greater than 7000 otherwise, throw an error (or message) etc.
Thank you for Visiting Our Blog
Leave a Reply