之前触发器的帖子就是我发的,谢谢你的代码,帮忙优化,下面这段是完整的,希望您帮忙看看. set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER trigger [EditUsers] on [dbo].[ZlEmployee] for update,insert AS BEGIN
SET NOCOUNT ON; set XACT_ABORT ON--启用分布式查询 if update(code) or update(name) or update(Dept) or update(CardNo) begin declare @UserCode nvarchar(10) declare @UserName nvarchar(10) declare @CardNumber nvarchar(12) declare @DepartmentID nvarchar(10) declare @DepartmentName nvarchar(20) -- declare @DateTimeBegin nvarchar(20) set @UserCode='' set @UserName='' set @CardNumber='' set @DepartmentID='' -- set @DateTimeBegin='' declare my_cursor cursor for select Code,Name,CardNo,Dept from Inserted open my_cursor FETCH my_cursor INTO @UserCode,@UserName,@CardNumber,@DepartmentID while @@fetch_status=0 begin ------------------------ set @UserCode=ltrim(rtrim(@UserCode)) set @UserName=ltrim(rtrim(@UserName)) set @CardNumber=ltrim(rtrim(@CardNumber)) set @DepartmentID=ltrim(rtrim(@DepartmentID)) -------------------状态变更退餐---------ERROR RPC设置错误--------------- -- if update(State) -- begin -- set @DateTimeBegin=convert(nvarchar(20),getdate(),23) -- -- exec DBVIP.[Hitachi].[dbo].Meal_Update -- @TypeID=10, -- @MealDateBegin=@DateTimeBegin, -- @OperatorName='人事系统', -- @ComputerName='人事系统', -- @ComputerIp='0.0.0.0', -- @UserCode=@UserCode -- end -------------------部门ID转换------------------------ if @DepartmentID='8400000000' begin set @DepartmentID='16' end else if @DepartmentID='8400000001' begin set @DepartmentID='5' end else if @DepartmentID='8400000003' begin set @DepartmentID='4' end else if @DepartmentID='8400000004' begin set @DepartmentID='6' end else if @DepartmentID='8400000005' begin set @DepartmentID='2' end else if @DepartmentID='8400000006' begin set @DepartmentID='1' end else if @DepartmentID='8400000007' begin set @DepartmentID='17' end else if @DepartmentID='8400000008' begin set @DepartmentID='3' end else if @DepartmentID='8400000010' begin set @DepartmentID='19' end else if @DepartmentID='8400000011' begin set @DepartmentID='20' end ----------------已存在信息进行修改或添加信息------------------- if exists(select 1 from DBVIP.Hitachi.dbo.Users where UserCode=@UserCode) begin--修改 if exists(select 1 from DBVIP.Hitachi.dbo.Users where UserCode=@UserCode and DepartmentID<>@DepartmentID) begin set @DepartmentName='' select @DepartmentName=DepartmentName from DBVIP.Hitachi.dbo.Department where DepartmentID=@DepartmentID update DBVIP.Hitachi.dbo.Users set DepartmentID=@DepartmentID,GroupID='30',IsBaocanDaiding='0' where UserCode=@UserCode update DBVIP.Hitachi.dbo.MealNight set DepartmentName=@DepartmentName where UserCode=@UserCode update DBVIP.Hitachi.dbo.MealReportPerson set DepartmentName=@DepartmentName where UserCode=@UserCode delete from DBVIP.Hitachi.dbo.MealFriendAll where UserCodeD=@UserCode or UserCodeP=@UserCode delete from DBVIP.Hitachi.dbo.MealFriends where UserCodeM=@UserCode or UserCodeF=@UserCode delete DBVIP.Hitachi.dbo.MsgNoticeNum where UserCode=@UserCode -- update DBVIP.Hitachi.dbo.MsgInfo set Department=@DepartmentName,GroupName='未知课别' where UserName=@UserName end
if (@CardNumber<>'' and @CardNumber is not null)--卡号不能为空 begin if exists(select 1 from DBVIP.[Hitachi].[dbo].[Users] where UserCode=@UserCode and CardNumber<>@CardNumber)--此人卡号不对 begin update DBVIP.[Hitachi].[dbo].Users set CardNumber=@CardNumber where UserCode=@UserCode update DBVIP.[Hitachi].[dbo].MealNight set CardNumber=@CardNumber where UserCode=@UserCode update DBVIP.[Hitachi].[dbo].MealReportPerson set CardNumber=@CardNumber where UserCode=@UserCode end end insert into DBVIP.Hitachi.dbo.UpDateCardLog(UserCode,UserName,DepartmentID,CardNumber,CardType) values (@UserCode,@UserName,@DepartmentID,@CardNumber,1)--添加日志 end else if @UserCode<>'' and @UserCode is not null--添加 begin if exists(select 1 from DBVIP.Hitachi.dbo.Users where UserName=@UserName) begin set @UserName=@UserName+@UserCode end insert into DBVIP.Hitachi.dbo.Users(UserCode,UserName,DepartmentID,CardNumber) values (@UserCode,@UserName,@DepartmentID,@CardNumber) insert into DBVIP.Hitachi.dbo.UpDateCardLog(UserCode,UserName,DepartmentID,CardNumber,CardType) values (@UserCode,@UserName,@DepartmentID,@CardNumber,0)--添加日志 end fetch next from my_cursor into @UserCode,@UserName,@CardNumber,@DepartmentID end close my_cursor --关闭游标 deallocate my_cursor --释放游标资源 end END
begin distributed tran
begin distributed tran
之前触发器的帖子就是我发的,谢谢你的代码,帮忙优化,下面这段是完整的,希望您帮忙看看.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER trigger [EditUsers] on [dbo].[ZlEmployee] for update,insert
AS
BEGIN
SET NOCOUNT ON;
set XACT_ABORT ON--启用分布式查询 if update(code) or update(name) or update(Dept) or update(CardNo)
begin
declare @UserCode nvarchar(10)
declare @UserName nvarchar(10)
declare @CardNumber nvarchar(12)
declare @DepartmentID nvarchar(10)
declare @DepartmentName nvarchar(20)
-- declare @DateTimeBegin nvarchar(20)
set @UserCode=''
set @UserName=''
set @CardNumber=''
set @DepartmentID=''
-- set @DateTimeBegin='' declare my_cursor cursor for select Code,Name,CardNo,Dept from Inserted
open my_cursor
FETCH my_cursor INTO @UserCode,@UserName,@CardNumber,@DepartmentID
while @@fetch_status=0
begin
------------------------ set @UserCode=ltrim(rtrim(@UserCode))
set @UserName=ltrim(rtrim(@UserName))
set @CardNumber=ltrim(rtrim(@CardNumber))
set @DepartmentID=ltrim(rtrim(@DepartmentID)) -------------------状态变更退餐---------ERROR RPC设置错误---------------
-- if update(State)
-- begin
-- set @DateTimeBegin=convert(nvarchar(20),getdate(),23)
--
-- exec DBVIP.[Hitachi].[dbo].Meal_Update
-- @TypeID=10,
-- @MealDateBegin=@DateTimeBegin,
-- @OperatorName='人事系统',
-- @ComputerName='人事系统',
-- @ComputerIp='0.0.0.0',
-- @UserCode=@UserCode
-- end -------------------部门ID转换------------------------ if @DepartmentID='8400000000'
begin
set @DepartmentID='16'
end
else if @DepartmentID='8400000001'
begin
set @DepartmentID='5'
end
else if @DepartmentID='8400000003'
begin
set @DepartmentID='4'
end
else if @DepartmentID='8400000004'
begin
set @DepartmentID='6'
end
else if @DepartmentID='8400000005'
begin
set @DepartmentID='2'
end
else if @DepartmentID='8400000006'
begin
set @DepartmentID='1'
end
else if @DepartmentID='8400000007'
begin
set @DepartmentID='17'
end
else if @DepartmentID='8400000008'
begin
set @DepartmentID='3'
end
else if @DepartmentID='8400000010'
begin
set @DepartmentID='19'
end
else if @DepartmentID='8400000011'
begin
set @DepartmentID='20'
end ----------------已存在信息进行修改或添加信息-------------------
if exists(select 1 from DBVIP.Hitachi.dbo.Users where UserCode=@UserCode)
begin--修改
if exists(select 1 from DBVIP.Hitachi.dbo.Users where UserCode=@UserCode and DepartmentID<>@DepartmentID)
begin
set @DepartmentName=''
select @DepartmentName=DepartmentName from DBVIP.Hitachi.dbo.Department where DepartmentID=@DepartmentID
update DBVIP.Hitachi.dbo.Users set DepartmentID=@DepartmentID,GroupID='30',IsBaocanDaiding='0' where UserCode=@UserCode
update DBVIP.Hitachi.dbo.MealNight set DepartmentName=@DepartmentName where UserCode=@UserCode
update DBVIP.Hitachi.dbo.MealReportPerson set DepartmentName=@DepartmentName where UserCode=@UserCode
delete from DBVIP.Hitachi.dbo.MealFriendAll where UserCodeD=@UserCode or UserCodeP=@UserCode
delete from DBVIP.Hitachi.dbo.MealFriends where UserCodeM=@UserCode or UserCodeF=@UserCode
delete DBVIP.Hitachi.dbo.MsgNoticeNum where UserCode=@UserCode
-- update DBVIP.Hitachi.dbo.MsgInfo set Department=@DepartmentName,GroupName='未知课别' where UserName=@UserName
end
if (@CardNumber<>'' and @CardNumber is not null)--卡号不能为空
begin
if exists(select 1 from DBVIP.[Hitachi].[dbo].[Users] where UserCode=@UserCode and CardNumber<>@CardNumber)--此人卡号不对
begin
update DBVIP.[Hitachi].[dbo].Users set CardNumber=@CardNumber where UserCode=@UserCode
update DBVIP.[Hitachi].[dbo].MealNight set CardNumber=@CardNumber where UserCode=@UserCode
update DBVIP.[Hitachi].[dbo].MealReportPerson set CardNumber=@CardNumber where UserCode=@UserCode
end
end insert into DBVIP.Hitachi.dbo.UpDateCardLog(UserCode,UserName,DepartmentID,CardNumber,CardType) values (@UserCode,@UserName,@DepartmentID,@CardNumber,1)--添加日志
end
else if @UserCode<>'' and @UserCode is not null--添加
begin
if exists(select 1 from DBVIP.Hitachi.dbo.Users where UserName=@UserName)
begin
set @UserName=@UserName+@UserCode
end insert into DBVIP.Hitachi.dbo.Users(UserCode,UserName,DepartmentID,CardNumber) values (@UserCode,@UserName,@DepartmentID,@CardNumber)
insert into DBVIP.Hitachi.dbo.UpDateCardLog(UserCode,UserName,DepartmentID,CardNumber,CardType) values (@UserCode,@UserName,@DepartmentID,@CardNumber,0)--添加日志
end
fetch next from my_cursor into @UserCode,@UserName,@CardNumber,@DepartmentID
end
close my_cursor --关闭游标
deallocate my_cursor --释放游标资源
end
END