邹建 大哥，请帮帮忙啦！先谢谢了！触发器的问题！

Question

改成这样：
CREATE TRIGGER TR_jx1 ON [jiangxi2].[dbo].[Dv_User_bak]  
FOR INSERT, UPDATE, DELETE 
as
alter table  [jiangxi].[dbo].[Dv_User_bak]  DISABLE  trigger  all ---在所有的出发器加此语句
set nocount on
set identity_insert  [jiangxi].[dbo].[Dv_User_bak]  on /*关闭自动递增*/
if not exists (select * from deleted) --插入
insert into [jiangxi].[dbo].[Dv_User_bak]  (UserID,UserName,UserPassword,UserEmail,UserPost,UserTopic,UserSign,UserSex,UserFace,UserWidth,UserHeight,JoinDate,LastLogin,UserLogins,UserViews,LockUser,UserClass,UserGroup,userWealth,userEP,userCP,UserPower,UserDel,UserIsBest,UserTitle,UserBirthday,UserQuesion,UserAnswer,UserLastIP,UserPhoto,UserFav,UserSetting,UserGroupID,TitlePic,UserHidden,UserMsg,IsChallenge,UserMobile,TruePassWord,UserToday,UserIsAva) select * from inserted
else if not exists (select * from inserted)--删除
delete [jiangxi].[dbo].[Dv_User_bak] where UserID in (select UserID from deleted)
else--更新
update a set a.UserName=i.UserName,a.UserPassword=i.UserPassword,a.UserEmail=i.UserEmail,a.UserPost=i.UserPost,a.UserTopic=i.UserTopic,a.UserSign=i.UserSign,a.UserSex=i.UserSex,a.UserFace=i.UserFace,a.UserWidth=i.UserWidth,a.UserHeight=i.UserHeight,a.JoinDate=i.JoinDate,a.LastLogin=i.LastLogin,a.UserLogins=i.UserLogins,a.UserViews=i.UserViews,a.LockUser=i.LockUser,a.UserClass=i.UserClass,a.UserGroup=i.UserGroup,a.userWealth=i.userWealth,a.userEP=i.userEP,a.userCP=i.userCP,a.UserPower=i.UserPower,a.UserDel=i.UserDel,a.UserIsBest=i.UserIsBest,a.UserTitle=i.UserTitle,a.UserBirthday=i.UserBirthday,a.UserQuesion=i.UserQuesion,a.UserAnswer=i.UserAnswer,a.UserLastIP=i.UserLastIP,a.UserPhoto=i.UserPhoto,a.UserFav=i.UserFav,a.UserSetting=i.UserSetting,a.UserGroupID=i.UserGroupID,a.TitlePic=i.TitlePic,a.UserHidden=i.UserHidden,a.UserMsg=i.UserMsg,a.IsChallenge=i.IsChallenge,a.UserMobile=i.UserMobile,a.TruePassWord=i.TruePassWord,a.UserToday=i.UserToday,a.UserIsAva=i.UserIsAva from [jiangxi].[dbo].[Dv_User_bak] as a,inserted as i where a.UserID=i.UserID
set identity_insert [jiangxi].[dbo].[Dv_User_bak] off /*取消自动递增*/
set nocount off
alter table [jiangxi].[dbo].[Dv_User_bak] ENABLE  trigger  all ---在所有的出发器加此语句

Answer 1

我实在看不过眼了，改下：
CREATE TRIGGER TR_jx1 ON [jiangxi2].[dbo].[Dv_User_bak]  
FOR INSERT, UPDATE, DELETE 
as
alter table  [jiangxi].[dbo].[Dv_User_bak]  DISABLE  trigger  all ---在所有的出发器加此语句
set nocount on
set identity_insert  [jiangxi].[dbo].[Dv_User_bak]  on /*关闭自动递增*/
if not exists (select * from deleted) --插入
insert into [jiangxi].[dbo].[Dv_User_bak]
 select * from inserted
else if not exists (select * from inserted)--删除
delete [jiangxi].[dbo].[Dv_User_bak] where UserID in (select UserID from deleted)
else--更新
/*考虑到要写那么多字段，我改成这样*/
begin
delete [jiangxi].[dbo].[Dv_User_bak] where UserID in (select UserID from deleted)
insert into [jiangxi].[dbo].[Dv_User_bak]
select * from inserted
endset identity_insert [jiangxi].[dbo].[Dv_User_bak] off /*取消自动递增*/
set nocount off
alter table [jiangxi].[dbo].[Dv_User_bak] ENABLE  trigger  all ---在所有的出发器加此语句

Answer 2

不行呀提示错误：仅当使用了列的列表，并且 IDENTITY_INSERT 为 ON 时，才能在表 'jiangxi.dbo.Dv_User_bak' 中为标识列指定显式值。
INSETTED ，DELETED列不允许next,ntext,image

Answer 3

错误提示已经说得很明白了，因为'jiangxi.dbo.Dv_User_bak' 有标识列，且你前面设置了IDENTITY_INSERT 为 ON ，所以上面在插入操作那里，不能省掉列名列表；另外，SQL Server 不允许引用 inserted 和 deleted 表中的 text、ntext 或 image 列，所以你要把类型变一变。

Answer 4

我把你写的修改了下：
CREATE TRIGGER TR_jx1 ON [jiangxi2].[dbo].[Dv_User_bak]  
FOR INSERT, UPDATE, DELETE 
as
alter table  [jiangxi].[dbo].[Dv_User_bak]  DISABLE  trigger  all ---在所有的出发器加此语句
set nocount on
set identity_insert  [jiangxi].[dbo].[Dv_User_bak]  on /*关闭自动递增*/
if not exists (select * from deleted) --插入
insert into [jiangxi].[dbo].[Dv_User_bak]  (UserID,UserName,UserPassword,UserEmail,UserPost,UserTopic,
UserSign,UserSex,UserFace,UserWidth,UserHeight,JoinDate,LastLogin,UserLogins,UserViews,LockUser,UserClass,UserGroup,userWealth,userEP,userCP,UserPower,UserDel,UserIsBest,UserTitle,UserBirthday,UserQuesion,UserAnswer,UserLastIP,UserPhoto,UserFav,UserSetting,UserGroupID,TitlePic,UserHidden,UserMsg,IsChallenge,UserMobile,TruePassWord,UserToday,UserIsAva) select * from inserted
else if not exists (select * from inserted)--删除
delete [jiangxi].[dbo].[Dv_User_bak] where UserID in (select UserID from deleted)
else--更新
update a set a.UserName=i.UserName,a.UserPassword=i.UserPassword,a.UserEmail=i.UserEmail,a.UserPost=i.UserPost,
a.UserTopic=i.UserTopic,a.UserSign=i.UserSign,a.UserSex=i.UserSex,a.UserFace=i.UserFace,a.UserWidth=i.UserWidth,
a.UserHeight=i.UserHeight,a.JoinDate=i.JoinDate,a.LastLogin=i.LastLogin,a.UserLogins=i.UserLogins,a.UserViews=i.UserViews,
a.LockUser=i.LockUser,a.UserClass=i.UserClass,a.UserGroup=i.UserGroup,a.userWealth=i.userWealth,a.userEP=i.userEP,
a.userCP=i.userCP,a.UserPower=i.UserPower,a.UserDel=i.UserDel,a.UserIsBest=i.UserIsBest,a.UserTitle=i.UserTitle,
a.UserBirthday=i.UserBirthday,a.UserQuesion=i.UserQuesion,a.UserAnswer=i.UserAnswer,a.UserLastIP=i.UserLastIP,
a.UserPhoto=i.UserPhoto,a.UserFav=i.UserFav,a.UserSetting=i.UserSetting,a.UserGroupID=i.UserGroupID,a.TitlePic=i.TitlePic,
a.UserHidden=i.UserHidden,a.UserMsg=i.UserMsg,a.IsChallenge=i.IsChallenge,a.UserMobile=i.UserMobile,a.TruePassWord=i.TruePassWord,
a.UserToday=i.UserToday,a.UserIsAva=i.UserIsAva from [jiangxi].[dbo].[Dv_User_bak] as a,inserted as i where a.UserID=i.UserID
set identity_insert [jiangxi].[dbo].[Dv_User_bak] off /*取消自动递增*/
set nocount off
alter table [jiangxi].[dbo].[Dv_User_bak] ENABLE  trigger  all ---在所有的出发器加此语句