Creating Trigger On Complex View To Insert Data

Debendra Dash
9y
9.9k
0
1

Article

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.
1. USE [Employee] GO
2. /****** Object: Trigger [dbo].[tr1] Script Date: 10/15/2015 00:56:43 ******/
3. SET
4. ANSI_NULLS ON GO
5. SET
6. 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)
7. select
8. @Empid = Empid,
9. @name = name,
10. @ContactNo = ContactNo,
11. @Location = Location
12. from
13. inserted insert into tbl_empdetails(EmpId, Name, ContactNo)
14. values
15. (@Empid, @name, @ContactNo) insert into tbl_Location(Location)
16. values
17. (@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.

Up Next

Ebook Download

View all

CSharp.com Ebook

Introducing Microsoft SQL Server 2016

Read by 11k people

Download Now!

Learn

View all

Learn Image