Get Even More Visitors To Your Blog, Upgrade To A Business Listing >>
Idioma: En

SQL Server Instead Of Insert Trigger Example

In this tutorial, I am going to explain you INSTEAD OF Insert Trigger in SQL-Server with example. In the previous tutorials, I had explained introduction to triggers in SQL-Server. Please go through the links provided below.

Related Links

  • Introduction to triggers in SQL-Server.
  • After INSERT Trigger in SQL Server with example.

There are two main types of DML trigger.1. After Triggers (also called as FOR trigger)2. Instead of Triggers

After Trigger (using FOR/AFTER CLAUSE)

This trigger fires after SQL Server completes the execution of the action successfully that fired it.Example: If you insert record/row in a table then the trigger associated with the insert event on this table will fire only after the row passes all the checks, such as primary key, rules, and constraints. If the record/row insertion fails, SQL Server will not fire the After Trigger.

Instead of Trigger (using INSTEAD OF CLAUSE)

This trigger fires before SQL Server starts the execution of the action that fired it. This is much more different from the AFTER trigger, which fires after the action that caused it to fire. We can have an INSTEAD OF insert/update/delete trigger on a table that successfully executed but does not include the actual insert/update/delete to the table.Example: If you insert record/row in a table then the trigger associated with the insert event on this table will fire before the row passes all the checks, such as primary key, rules, and constraints. If the record/row insertion fails, SQL Server will fire the Instead of Trigger.

Instead of Insert Trigger Example

When we try to insert a record into a table then instead of insert trigger is fired if trigger is associated with that table. Insert of trigger is mostly used with Views in SQL, but in this tutorial we are going to implement instead of trigger with single table. In article, we will use instead of trigger with Views. Now let's implement the trigger.

Create a table Employee with 3 columns EmployeeId, Name and Age. EmployeeId will be auto generated. Below is the code to create Employee table and insert some dummy records.

CREATE TABLE Employee
(
EmployeeId INT PRIMARY KEY IDENTITY,
Name VARCHAR(20),
Age INT
)
GO
INSERT INTO Employee VALUES('Mr. A',25)
INSERT INTO Employee VALUES('Mr. B',19)
INSERT INTO Employee VALUES('Mr. C',30)
GO

Now, we will create a Audit Table for Employee table. When somebody insert record of Employee table, then we will insert same record in audit table by using instead of insert trigger. Below is the script to create Employee_Audit table.

CREATE TABLE Employee_Audit
(
Id INT IDENTITY PRIMARY KEY,
EmployeeId INT,
Name VARCHAR(20),
Age INT,
InsertedDate DATETIME
)

Creating Instead Of Insert Trigger

Below is the script to create Instead Of Insert Trigger.

CREATE TRIGGER tr_Employee_InsteadOfInsert   -- Trigger name

ON Employee    -- table name

INSTEAD OF INSERT  -- trigger type

AS
BEGIN

     DECLARE@Name VARCHAR(20),
             @Age INT

     SELECT@Name=i.Name,
            @Age=i.Age
            FROMinserted AS i  -- inserted table is also called as magic table
           
       IF (@Name is null or @Name='')
       BEGIN
              RAISERROR('Name is required.', 16 ,1)
              --Here you can use 'RETURN' or 'ROLLBACK' keyword as per your need
       END
       ELSE IF (@Age>30)
       BEGIN
              RAISERROR('Age cannot be greater than 30.', 16 ,1)
       END
       ELSE
       BEGIN
              INSERTINTO Employee VALUES(@Name,@Age)
              INSERTINTO Employee_Audit VALUES(@@identity,@Name,@Age,GETDATE())
       END
END

When you try to insert a record into Employee table then SQL-Server creates a magic table named as 'inserted' and same copy of record will we inserted into this magic table. Inserted table is accessible inside the context of trigger. You can not access inserted table outside of triggers.

Executing After Delete Trigger

As we can not execute triggers explicitly, to execute trigger, we need to insert an record into the table. Initially we have only 3 records into Employee and zero record in Employee_Audit table as shown below.

SELECT * FROM Employee
SELECT * FROM Employee_Audit

Employee Table:
EmployeeId  Name   Age
1           Mr. A  25
2           Mr. B  19
3           Mr. C  30

Employee_Audit Table:
No records

Now try to insert a new record into Employee table as shown below. A new record will be inserted into Employee as well as Employee_Audit tables.

INSERT INTO Employee VALUES ('Mr. D',20)

GO
SELECT * FROM Employee
SELECT * FROM Employee_Audit

Employee Table:
EmployeeId  Name   Age
1           Mr. A  25
2           Mr. B  19
3           Mr. C  30
4           Mr. D  20

Employee_Audit Table:
Id    EmployeeId  Name    Age   InsertedDate
1     4           Mr. D   20    2017-12-03 22:17:44.080

Now try to insert a new record into Employee table with Age greater than 30 or empty in Name column, a error will be raised and no record will be inserted into Employee and Employee_Audit tables as shown below.

INSERT INTOEmployee VALUES ('Mr. E',35)
Message
Msg 50000, Level 16, State 1, Procedure tr_Employee_InsteadOfInsert, Line 24
Age cannot be greater than 30.

(1 row(s) affected)

Related Articles



This post first appeared on ASPArticles, please read the originial post: here

Share the post

SQL Server Instead Of Insert Trigger Example

×

Subscribe to Asparticles

Get updates delivered right to your inbox!

Thank you for your subscription

×