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.Now if I will check my tbl_empdetails I will find the record.
- insert into mycomplexview (Empid,Name,ContactNo,Location )values(100,'Sunil','8018070999','Bangalore')
- select * from tbl_empdetails
- Now let me check my tbl_location table.
Santhakumar MunuswamyPosted Oct 18, 2015, 2:26 AM
Good one
RakeshPosted Oct 16, 2015, 2:54 PM
Good share
Mukesh KumarPosted Oct 16, 2015, 2:19 PM
Great Job thanks for sharing
Nilesh JadavPosted Oct 16, 2015, 9:14 AM
Nice Post !!
Kamlesh BhorPosted Oct 16, 2015, 8:08 AM
Good one...