Wednesday, 12 June 2013

insert into view with multiple tables

Hi,
       In privious article I have discussed about Basic of view and View on single table. Now we
will discuss about a how to create a view on multiple table and how to insert data or records in
all tables by using view.

example::

Create table::


create table emp_master (emp_id int identity(1,1) ,emp_name varchar(100),emp_sal varchar(100) ,dept_id int)
create table dept_master (dept_id int identity(1,1),dept_name varchar(100))
_

Insert records in table::


insert into emp_master (emp_name,emp_sal,dept_id ) values('sandipG','1000',1)
insert into emp_master (emp_name,emp_sal,dept_id ) values('ajit','45321' ,1)
insert into emp_master (emp_name,emp_sal,dept_id ) values('pranav','5000',2)
insert into emp_master (emp_name,emp_sal,dept_id ) values('aditya','10000',3)
insert into dept_master (dept_name ) values('admin')
insert into dept_master (dept_name ) values('IT')
insert into dept_master (dept_name ) values('production')

Create view on multiple tables


Create view [dbo].emp_dept
as
SELECT dbo.dept_master.dept_id AS dept_id, dbo.emp_master.emp_id, dbo.emp_master.emp_name, dbo.emp_master.emp_sal,
dbo.dept_master.dept_name
FROM dbo.emp_master INNER JOIN
dbo.dept_master ON dbo.emp_master.dept_id = dbo.dept_master.dept_id

Test Case1::Try to insert data in view on multiple tables


insert into emp_dept (emp_name, emp_sal,dept_name)values('sachin','2123','cleaner')
select * from emp_master
select * from dept_master

you get error when you try to insert by using above statement.For solve this problems you
must be write a trigger on view.

Create trigger for insert data in multiple tables by view

Create TRIGGER dbo.Insert_view
ON emp_dept
instead of INSERT
AS
BEGIN
SET NOCOUNT ON;
declare @dept_name varchar(100);
declare @empname varchar(100);
declare @emp_sal varchar(100);
select @dept_name=i.dept_name from inserted i;
declare @dept_id int
insert into dept_master (dept_name) values(@dept_name)
SELECT @dept_id=@@IDENTITY
select @empname=i.emp_name from inserted i;
select @emp_sal=i.emp_sal from inserted i;
insert into emp_master (emp_name,emp_sal,dept_id ) values(@empname,@emp_sal,@dept_id)
END
GO

Now you can try to do this.


insert into emp_dept (emp_name, emp_sal,dept_name)values('sachin','2123','cleaner')
select * from emp_master
select * from dept_master

Related links ::
basic of view
update trigger
Insert trigger
delete trigger
basic of trigger

1 comment:

  1. This post is truly a fastidious one it helps new internet viewers, who are wishing in favor of blogging.


    my page Automated Income App Review

    ReplyDelete

8451831077