Friday, 14 June 2013

update data in view with multiple table

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

example::


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')

1)Create view with multiple table with select all columns in table .


alter view [dbo].emp_dept
as
SELECT dbo.emp_master.emp_name,
dbo.emp_master.emp_sal,dbo.dept_master.dept_name, dbo.dept_master.dept_id AS dept_id, dbo.emp_master.emp_id

FROM dbo.emp_master INNER JOIN
dbo.dept_master ON dbo.emp_master.dept_id = dbo.dept_master.dept_id

it is not possible to exicute a update statement againests view with multiple tables. you must
be write a trigger on that view for update records.
Create  TRIGGER dbo.Update_view
ON emp_dept
instead of update
AS
BEGIN
SET NOCOUNT ON;
declare @dept_name varchar(100);
declare @empname varchar(100);
declare @emp_sal varchar(100);
declare @dept_id varchar(100);
declare @emp_id varchar(100);

select @dept_name=i.dept_name from inserted i;
select @dept_id=i.dept_id from inserted i;

update dept_master set dept_name=@dept_name where dept_id=@dept_id

select @emp_id=i.emp_id from inserted i;
select @empname=i.emp_name from inserted i;
select @emp_sal=i.emp_sal from inserted i;

update emp_master set emp_name=@empname,emp_sal=@emp_sal,dept_id =@dept_id where emp_id= @emp_id
END
GO

Test Case::

update emp_dept set emp_name ='sachin',emp_sal=2123 ,dept_name='Computer' where dept_id=1 and emp_id=1 select * from emp_master select * from dept_master

No comments:

Post a Comment

8451831077