Modify view data just as you would modify table data

Summary: Modify the view data as you would modify the table data by adding triggers to the view to modify the entity table data

Implementation effect: Update dbo.[ View] Set [Field]= ' is equivalent to Update dbo.[ Table] Set [Field]= '

Note: The database is using SQL Server 2008

1) User table Basicdata. [User]
Use [Basicdata]
GO

/****** object:table [dbo].    [User] Script date:07/20/2015 16:22:31 ******/
SET ANSI_NULLS on
GO

SET QUOTED_IDENTIFIER ON
GO

SET ansi_padding on
GO

CREATE TABLE [dbo]. [User] (
[UserID] [INT] IDENTITY (*) is not NULL,
[Account] [nvarchar] () not NULL,
[Password] [nvarchar] () not NULL,
[Usertype] [INT] Null
[Deletetag] [INT] Null
[Regdatetime] [DateTime] Null
[Lastlogindatetime] [DateTime] Null
[Network] [INT] Null
[Kid] [INT] Null
[Name] [nvarchar] () NULL,
[Nickname] [nvarchar] () NULL,
[Birthday] [DateTime] Null
[Gender] [INT] Null
[Nation] [INT] Null
[Mobile] [nvarchar] () NULL,
[Email] [nvarchar] (+) NULL,
[Address] [nvarchar] (+) NULL,
[EnrollmentDate] [DateTime] Null
[Exigencetelphone] [nvarchar] () NULL,
[Headpicupdate] [DateTime] Null
[Headpic] [nvarchar] (+) NULL,
[Privince] [INT] Null
[City] [INT] Null
[Istip] [Bit] Null
[Residence] [INT] Null
[Tiprule] [nvarchar] () NULL,
[Gbstatus] [INT] Null
[Enrollmentreason] [varchar] () NULL,
[Smsport] [INT] Null
[UpdateTime] [DateTime] Null
[Ngb_gbversiontag] [INT] Null
[Deletedatetime] [DateTime] Null
[TTS] [varchar] () NULL,
[Roletype] [INT] Null
[Njtype] [INT] Null
[Readright] [INT] Null
[Isneedtransferpassword] [Bit] Null
[Lqright] [INT] Null
[Mc_photo_udate] [DateTime] Null
[AddType] [smallint] Not NULL,
[Sname] [nvarchar] () NULL,
[Jzxxgrade] [INT] Null
[Proxyid] [smallint] Null
CONSTRAINT [Pk_user] PRIMARY KEY CLUSTERED
(
[UserID] Asc
) with (Pad_index = off, Statistics_norecompute = off, Ignore_dup_key = off, Allow_row_locks = on, Allow_page_locks = O N) on [PRIMARY]
) on [PRIMARY]

GO

SET ansi_padding OFF
GO

EXEC sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' user login account ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' user ',

@level2type =n ' COLUMN ', @level2name =n ' account '
GO

EXEC sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' password ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @ Level1type=n ' TABLE ', @level1name =n ' user ',

@level2type =n ' COLUMN ', @level2name =n ' password '
GO

EXEC sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' user category
0 children
1 teacher
97 Principal
98 admin ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' user ', @level2type =n ' COLUMN ' , @level2name =n ' usertype '
GO

EXEC sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' registration time ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' user ',

@level2type =n ' COLUMN ', @level2name =n ' Regdatetime '
GO

EXEC sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' recent login time ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' user ',

@level2type =n ' COLUMN ', @level2name =n ' Lastlogindatetime '
GO

EXEC sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' name ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @ Level1type=n ' TABLE ', @level1name =n ' user ',

@level2type =n ' COLUMN ', @level2name =n ' name '
GO

EXEC sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' nickname ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @ Level1type=n ' TABLE ', @level1name =n ' user ',

@level2type =n ' COLUMN ', @level2name =n ' nickname '
GO

EXEC sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' birthday ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @ Level1type=n ' TABLE ', @level1name =n ' user ',

@level2type =n ' COLUMN ', @level2name =n ' birthday '
GO

EXEC sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' sex 3 male 2 female ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' user ',

@level2type =n ' COLUMN ', @level2name =n ' gender '
GO

EXEC sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' Country id ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' user ',

@level2type =n ' COLUMN ', @level2name =n ' Nation '
GO

EXEC sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' mobile number ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' user ',

@level2type =n ' COLUMN ', @level2name =n ' mobile '
GO

EXEC sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' e-mail ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' user ',

@level2type =n ' COLUMN ', @level2name =n ' email '
GO

EXEC sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' address ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @ Level1type=n ' TABLE ', @level1name =n ' user ',

@level2type =n ' COLUMN ', @level2name =n ' address '
GO

EXEC sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' admission time ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' user ',

@level2type =n ' COLUMN ', @level2name =n ' enrollmentdate '
GO

EXEC sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' avatar upload time ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' user ',

@level2type =n ' COLUMN ', @level2name =n ' headpicupdate '
GO

EXEC sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' Avatar ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @ Level1type=n ' TABLE ', @level1name =n ' user ',

@level2type =n ' COLUMN ', @level2name =n ' Headpic '
GO

EXEC sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' province id ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' user ',

@level2type =n ' COLUMN ', @level2name =n ' privince '
GO

EXEC sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' city id ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' user ',

@level2type =n ' COLUMN ', @level2name =n ' city '
GO

EXEC sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' growth profile Type 0 School Edition 1 People edition ', @level0type =n ' SCHEMA ', @ Level0name=n ' dbo ',

@level1type =n ' TABLE ', @level1name =n ' user ', @level2type =n ' COLUMN ', @level2name =n ' Gbstatus '
GO

EXEC sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' SMS Channel ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' user ',

@level2type =n ' COLUMN ', @level2name =n ' Smsport '
GO

EXEC sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' update Time ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' user ',

@level2type =n ' COLUMN ', @level2name =n ' UpdateTime '
GO

EXEC sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' growth file version tag (Kindergarten edition 0, Personal Edition 1) ', @level0type =n ' SCHEMA ', @ Level0name=n ' dbo ',

@level1type =n ' TABLE ', @level1name =n ' user ', @level2type =n ' COLUMN ', @level2name =n ' Ngb_gbversiontag '
GO

EXEC sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' delete time ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' user ',

@level2type =n ' COLUMN ', @level2name =n ' Deletedatetime '
GO

EXEC sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' pronunciation tts ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ' , @level1type =n ' TABLE ', @level1name =n ' user ',

@level2type =n ' COLUMN ', @level2name =n ' TTS '
GO

EXEC sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' binary permissions (1-bit: Website user, 2-bit: Digital Book Reader card user) ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ',

@level1type =n ' TABLE ', @level1name =n ' user ', @level2type =n ' COLUMN ', @level2name =n ' Roletype '
GO

EXEC sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' Grade (0, TOEIC, 1 class, 2 shift, 3 Large) ', @level0type =n ' SCHEMA ', @ Level0name=n ' dbo ',

@level1type =n ' TABLE ', @level1name =n ' user ', @level2type =n ' COLUMN ', @level2name =n ' Njtype '
GO

EXEC sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' binary permissions (1 small classes, 2 small classes, 3 secondary, 4 mid-shift, 5 big class, 6 big classes, 7 TOEIC, 8 Torr) ' ,

@level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' user ', @level2type =n ' COLUMN ', @ Level2name=n ' Readright '
GO

EXEC sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' morning check photo update time ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ',

@level1type =n ' TABLE ', @level1name =n ' user ', @level2type =n ' COLUMN ', @level2name =n ' mc_photo_udate '
GO

EXEC sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' morning check for uncommon word substitution name ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ',

@level1type =n ' TABLE ', @level1name =n ' user ', @level2type =n ' COLUMN ', @level2name =n ' sname '
GO

EXEC sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' user table ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @ Level1type=n ' TABLE ', @level1name =n ' user '
GO

ALTER TABLE [dbo].  [User] With check ADD CONSTRAINT [Chk_user_name] CHECK ((Patindex ('%[#|] % ', [name]) = (0)))
GO

ALTER TABLE [dbo]. [User] CHECK CONSTRAINT [Chk_user_name]
GO

ALTER TABLE [dbo]. [User] ADD DEFAULT ((0)) for [network]
GO

ALTER TABLE [dbo]. [User] ADD CONSTRAINT [df_user_headpic] DEFAULT (' attachsfiles/default/headpic/default.jpg ') for [headpic]
GO

ALTER TABLE [dbo]. [User] ADD CONSTRAINT [Df_user_istip] DEFAULT ((0)) for [Istip]
GO

ALTER TABLE [dbo]. [User] ADD CONSTRAINT [Df_user_tiprule] DEFAULT (' ') for [Tiprule]
GO

ALTER TABLE [dbo]. [User] ADD CONSTRAINT [Df_user_gbstatus] DEFAULT ((0)) for [Gbstatus]
GO

ALTER TABLE [dbo]. [User] ADD CONSTRAINT [Df_user_enrollmentreason] DEFAULT (") for [Enrollmentreason]
GO

ALTER TABLE [dbo]. [User] ADD CONSTRAINT [Df_user_smsport] DEFAULT ((8)) for [Smsport]
GO

ALTER TABLE [dbo]. [User] ADD CONSTRAINT [Df_user_updatetime] DEFAULT (GETDATE ()) for [UpdateTime]
GO

ALTER TABLE [dbo]. [User] ADD DEFAULT ((0)) for [Ngb_gbversiontag]
GO

ALTER TABLE [dbo]. [User] ADD CONSTRAINT [Df_user_tts] DEFAULT (") for [TTS]
GO

ALTER TABLE [dbo]. [User] ADD CONSTRAINT [Df_user_roletype] DEFAULT ((1)) for [Roletype]
GO

ALTER TABLE [dbo]. [User] ADD DEFAULT ((1)) for [AddType]
GO


2) corresponding view
Use [Kmapp]
GO

/****** Object:view [dbo].    [Km_user] Script date:07/20/2015 16:28:21 ******/
SET ANSI_NULLS on
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE view [dbo]. [Km_user]
As
--role 0: Principal, 1: Teacher, 2: Parent, 3: Public number
Select A.kid, A.userid, A.role, A.name, A.account, A.password, A.gender, A.mobile, B.binding_mobile, A.deletetag,
A.nickname, B.headpic, B.headserver, B.point, B.districtid,
A.usertype, Cast (case if B.userid > 0 then 1 Else 0 End as Bit) SGS, B.default_relation, A.proxyid, A.birthday, A.rol EType
From BasicData.dbo.app_user a left Join dbo.app_user_detail b on a.userid = B.userid

GO
---------------(1)----------
CREATE view [dbo]. [App_user] with schemabinding
As
--role:usertype 0: Principal, 1: Teacher, 2: Parent, 3: Public number
With Data as (
Select userid, Min (role) role from Dbo.user_role Group by UserID
)
Select A.kid, A.userid, Isnull (b.role, Case if usertype in (98) and then 0 when usertype = 1 then 1 Else 2 End) role,
A.name, A.nickname, A.account, A.password, A.gender, A.mobile, A.deletetag, A.usertype, A.proxyid, A.birthday, a. Roletype
FROM dbo. [user] A left Join Data b on a.userid = B.userid
Where A.deletetag = 1

Use [Kmapp]
GO
-----------------(2)--------------
/****** object:table [dbo].    [App_user_detail] Script date:07/20/2015 16:30:17 ******/
SET ANSI_NULLS on
GO

SET QUOTED_IDENTIFIER ON
GO

SET ansi_padding on
GO

CREATE TABLE [dbo]. [App_user_detail] (
[UserID] [bigint] Not NULL,
[App_account] [nvarchar] (+) NULL,
[Binding_mobile] [nvarchar] () NULL,
[Nickname] [varchar] () NULL,
[Headpic] [varchar] () NULL,
[Headserver] [varchar] () NULL,
[Point] [Float] Null
[Districtid] [INT] Null
[Lastlogin] [DateTime] Null
[Default_relation] [smallint] Null
[Adddate] [DateTime] Not NULL,
CONSTRAINT [Pk_user_detail] PRIMARY KEY CLUSTERED
(
[UserID] Asc
) with (Pad_index = off, Statistics_norecompute = off, Ignore_dup_key = off, Allow_row_locks = on, Allow_page_locks = O N) on [PRIMARY]
) on [PRIMARY]

GO

SET ansi_padding OFF
GO

ALTER TABLE [dbo]. [App_user_detail] ADD CONSTRAINT [df_app_user_detail_adddate] DEFAULT (GETDATE ()) for [Adddate]
GO
============================================================================

3) Modify the entity table by adding a trigger to the view
CREATE Trigger [dbo]. [Tri_km_user_update] on [dbo]. [Km_user]
INSTEAD of UPDATE
As
Set Nocount on

EXEC commonfun.dbo.SetDoInfo @DoUserID = ', @DoProc = ' kmapp.dbo.Tri_km_user_update '

If update (gender) or update (password) or update (kid) or update (account) or update (mobile) or update (nickname) or update (BI Rthday)
Update a Set gender = b.gender, password = b.password, kid = case when Isnull (B.kid, ') = "then A.kid Else b.kid End,
Name = b.name, nickname = b.nickname, mobile = b.mobile, birthday = B.birthday
From Basicdata.dbo. [user] A, inserted b
Where A.userid = B.userid;

If Update (Kid)
Begin
Merge BasicData.dbo.user_kindergarten A
Using inserted b on a.userid = B.userid
When matched then
Update Set kid = B.kid
When isn't matched then
Insert (UserID, Kid) Values (B.userid, B.kid);

--Initialize the public number
Insert into Dbo.subscription (userid, publicID, GroupID)
Select UserID, Case role if 0 then-1 then-else-end, case role-0 then-then-1 then-else end
From inserted
Where Kid > 0
End

Merge Dbo.app_user_detail A
Using inserted b on a.userid = B.userid
When matched then
Update Set headpic = b.headpic, Binding_mobile = B.binding_mobile,
Headserver = b.headserver, point = b.point, Districtid = B.districtid,
Default_relation = B.default_relation
When not matched and b.sgs = 1 Then
Insert (userid, nickname, Headpic, Headserver, point, Districtid, Binding_mobile, default_relation)
Values (B.userid, B.nickname, B.headpic, B.headserver, B.point, B.districtid, B.binding_mobile, b.default_relation);


4) Realization
-- =============================================
--Author:
--Create Date: <create date,,>
--Description: Modifying personal data
--Km_updatepersonalinfov2 433990, ' 123 ', 3, 440113
-- =============================================
CREATE PROCEDURE [dbo]. [KM_UPDATEPERSONALINFOV2]
@uid bigint,
@gender smallint,
@realname Varchar (50),
@birthday datetime
As
Set Nocount on

Update dbo.km_user Set name = @realname, gender = @gender, birthday = @birthday Where userid = @uid

Modify view data just as you would modify table data