Creating Trigger On Complex View To Insert Data

Creating Trigger On Complex View

In this article I am explaining how to create a trigger on a complex view.

Here I am showing how can I split the data in trigger and insert in two different tables.

Here are my tables.

 

This is (tbl_empdetails).

 

 

This is (tbl_location).

Here I have created the complex view.

 

If you don't know how to create a view please check my tutorial on view here:

•  The view is created like this.
  
  
  Now here I am writing a trigger so that when I insert the data into the view it will affect both the tables.

  USE [Employee] GO
  /****** Object: Trigger [dbo].[tr1] Script Date: 10/15/2015 00:56:43 ******/
  SET
  ANSI_NULLS ON GO
  SET
  QUOTED_IDENTIFIER ON GO ALTER trigger [dbo].[tr1] on [dbo].[mycomplexview] instead of insert as begin declare @Empid as nvarchar(10) declare @name as nvarchar(20) declare @ContactNo as nvarchar(11) declare @Location as nvarchar(20)
  select
  @Empid = Empid,
  @name = name,
  @ContactNo = ContactNo,
  @Location = Location
  from
  inserted insert into tbl_empdetails(EmpId, Name, ContactNo)
  values
  (@Empid, @name, @ContactNo) insert into tbl_Location(Location)
  values
  (@Location) end

• Here in this trigger I have split the data that I entered to the view and inserted into both the tables.
  
  
  
• Thus this trigger will be created here
  
  
  
• Now for testing purpose I am inserting a record in the view.
  

  insert into mycomplexview (Empid,Name,ContactNo,Location )values(100,'Sunil','8018070999','Bangalore')

  Now if I will check my tbl_empdetails I will find the record.
  

  select * from tbl_empdetails

  
  
  
• Now let me check my tbl_location table.
  
  

In this way I can create a complex view and insert record on different table using  trigger.