改(根据ID更新员工主表,根据员工ID删除部门和员工表中的数据,再次插入新数据到部门和员工关系表中)
--根据员工ID更新员工表和关系表
create proc myUpdate
(
@EmployeeID int
,@DepartmentID varchar(200)
,@FName int
,@FAge varchar(50)
,@FBirthday datetime
,@FJiGuan varchar(50)
,@FSexID int
,@IsAbled int
,@FNumber varchar(50)
,@IsConfirmed int
)
as
begin
begin tran myTran--声明一个事务
update TEmployee set
update TEmployee set
FName=@FName
,FAge=@FAge
,FBirthday=@FBirthday
,FJiGuan=@FJiGuan
,FSexID=@FSexID
,IsAbled=@IsAbled
,FNumber=@FNumber
,IsConfirmed=@IsConfirmed
where ID=@EmployeeID--根据员工ID更新员工表数据
--根据员工ID删除部门关系表中的数据
delete from Mapping_Employee_Department where employeeID=@EmployeeID
--从员工部门ID字符串拆分成的表中取到本部门ID字段,然后再加上员工ID一起放进员工部门关系表中
insert into Mapping_Employee_Department(employeeID,departmentID)
select @EmployeeID,value from SplitToTable(@DepartmentID,',')
if(@@ERROR<>0)
rollback tran myTran--回滚事务
else
commit tran myTran--提交事务
,FAge=@FAge
,FBirthday=@FBirthday
,FJiGuan=@FJiGuan
,FSexID=@FSexID
,IsAbled=@IsAbled
,FNumber=@FNumber
,IsConfirmed=@IsConfirmed
where ID=@EmployeeID--根据员工ID更新员工表数据
--根据员工ID删除部门关系表中的数据
delete from Mapping_Employee_Department where employeeID=@EmployeeID
--从员工部门ID字符串拆分成的表中取到本部门ID字段,然后再加上员工ID一起放进员工部门关系表中
insert into Mapping_Employee_Department(employeeID,departmentID)
select @EmployeeID,value from SplitToTable(@DepartmentID,',')
if(@@ERROR<>0)
rollback tran myTran--回滚事务
else
commit tran myTran--提交事务
end