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

Related Posts::

basic of view
insert into view with multiple tables
Insert into view with single table
Instead of update trigger and after update trigger in sql
Instead of Insert trigger and after insert trigger in sql
instead of delete trigger and after delete trigger in sql
basic of trigger and interview quetions on trigger
Knowledged Queries
Comman Table Expression
Functions In Sql