像修改表数据一样修改视图数据
摘要：像修改表数据一样修改视图数据，具体思路是通过给视图增加触发器来实现修改实体表数据
实现效果： Update dbo.[视图] set [字段]='' 等价于 Update dbo.[表] set [字段]=''
备注：数据库使用的是Sql Server 2008
1)用户表 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(1,1) NOT NULL,
[account] [nvarchar](50) NOT NULL,
[password] [nvarchar](50) NOT NULL,
[usertype] [int] NULL,
[deletetag] [int] NULL,
[regdatetime] [datetime] NULL,
[lastlogindatetime] [datetime] NULL,
[network] [int] NULL,
[kid] [int] NULL,
[name] [nvarchar](50) NULL,
[nickname] [nvarchar](50) NULL,
[birthday] [datetime] NULL,
[gender] [int] NULL,
[nation] [int] NULL,
[mobile] [nvarchar](50) NULL,
[email] [nvarchar](100) NULL,
[address] [nvarchar](200) NULL,
[enrollmentdate] [datetime] NULL,
[exigencetelphone] [nvarchar](50) NULL,
[headpicupdate] [datetime] NULL,
[headpic] [nvarchar](200) NULL,
[privince] [int] NULL,
[city] [int] NULL,
[istip] [bit] NULL,
[residence] [int] NULL,
[tiprule] [nvarchar](50) NULL,
[gbstatus] [int] NULL,
[enrollmentreason] [varchar](50) NULL,
[smsport] [int] NULL,
[updatetime] [datetime] NULL,
[NGB_gbVersionTag] [int] NULL,
[deletedatetime] [datetime] NULL,
[tts] [varchar](50) 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](50) 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 = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户登录帐号' , @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'密码' , @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'用户类别
0小朋友
1老师
97园长
98管理员' , @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'注册时间' , @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'最近登录时间' , @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'姓名' , @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'昵称' , @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'生日' , @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'性别 3男2女' , @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'国家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'手机号码' , @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'电子邮件' , @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'地址' , @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'入园时间' , @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'头像上传时间' , @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'头像' , @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'省份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'城市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'成长档案类型 0家校版 1个人版' , @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'短信通道' , @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'更新时间' , @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'成长档案版本标记(幼儿园版0、个人版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'删除时间' , @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'发音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'二进制权限（1位：网站用户，2位：数字图书阅读卡用户）' , @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'所属年级（0,托班,1小班，2中班，3大班）' , @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'二进制权限（1小班上，2小班下，3中班上，4中班下，5大班上，6大班下，7托班上，8托班下）' ,
@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'晨检照片更新时间' , @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'晨检生僻字替代名称' , @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'用户表' , @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 ('000') 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)对应视图
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:园长, 1:老师, 2:家长, 3:公众号
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 When b.userid > 0 Then 1 Else 0 End as bit) sgs, b.default_relation, a.proxyid, a.birthday, a.RoleType
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:园长, 1:老师, 2:家长, 3:公众号
With Data as (
Select userid, Min(role) role From dbo.user_role Group by userid
)
Select a.kid, a.userid, Isnull(b.role, Case When usertype In (97, 98) 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](200) NULL,
[binding_mobile] [nvarchar](50) NULL,
[nickname] [varchar](50) NULL,
[headpic] [varchar](300) NULL,
[headserver] [varchar](50) 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 = ON) 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）通过给视图增加触发器来实现修改实体表
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(birthday)
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 not Matched Then
Insert (userid, kid) Values(b.userid, b.kid);
--初始化公众号
Insert Into dbo.subscription(userid, publicid, groupid)
Select userid, Case role When 0 Then 16 When 1 Then 17 Else 18 End, Case role When 0 Then 19 When 1 Then 20 Else 21 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）实现
-- =============================================
-- Author:
-- Create date: <Create Date,,>
-- Description: 修改个人资料
-- 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
