In this sample i am creating a view and then creating an "instead of trigger" on view which inserts two statements. In this User_Master table row gets inserted
create table User_Master(Userid int identity,UserName Varchar(100),Password varchar(50))
create table User_Details(UserID int unique,Fname varchar(100),Mname varchar(100),Lname varchar(100),email varchar(200))
create view vw_master_details2
as
select [User_Master].[Username],[User_Master].[Password],[User_Details].[FNAME],[User_Details].[MNAME],[User_Details].[LNAME],[User_Details].[EMAIL] from [User_Master],[User_Details] where [User_Master].[UserID]=[User_Details].[UserID]
Create trigger trig_vw_master_details2
on vw_master_details2
instead of insert
as
BEGIN
Declare @fname varchar(50)
Declare @mname varchar(50)
Declare @lname varchar(50)
Declare @email varchar(50)
Declare @un varchar(50)
Declare @pass varchar(50)
select @un=Username,@pass=Password,@fname=FNAME,@lname=LNAME,@mname=MNAME,@email=EMAIL FROM INSERTED
INSERT INTO User_Master(Username,Password) VALUES(@un,@pass)
INSERT INTO User_Details(UserID,FNAME,MNAME,LNAME,EMAIL) VALUES(@@Identity,@fname,@mname,@lname,@email)
END
INSERT INTO vw_master_details2(Username,Password,FNAME,MNAME,LNAME,EMAIL)
VALUES('Shiva','Kumar','Patil','Shiva@hotmail.com','Bangalore','Mypass')
select * from vw_master_details2
select * from User_Master
select * from User_Details
drop table User_Master
drop table User_Details
drop view vw_master_details2
|
No responses found. Be the first to respond and make money from revenue sharing program.
|