批量处理时的触发器问题
建立触发器如下：
ALTER TRIGGER TG_InsertEmployee ON [SynchEmployeeTemp]
FOR INSERT
AS
BEGIN TRAN AddUsers
/* 如果存在相同编号，则返回 */
IF EXISTS (SELECT 1 FROM Users a INNER JOIN INSERTED b ON a.UserCode = b.UserCode)
RETURN
/* 产生用户名 开始 */
DECLARE @Gender nvarchar(10), @Val2 DATETIME, @Birthday NVARCHAR(20), @LimitAge INT, @NowYear INT, @Age INT, @Init INT, @Terminal INT, @I INT
, @MaxNo NVARCHAR(50), @UserName NVARCHAR(50), @PassWord NVARCHAR(50)
SELECT @Gender = Gender, @Val2 = Birthday, @PassWord = SUBSTRING(ISNULL(CardID, '000000000 '), 0, 9) FROM INSERTED
IF @Val2 IS NULL
SET @Val2 = '1900-01-01 '
if @Gender <> '1 ' AND @Gender <> '2 '
SET @Gender = '1 '
SELECT @Birthday = CAST(DATEPART(year, @Val2) AS NVARCHAR(10)), @LimitAge = 60, @NowYear = DATEPART(YEAR, GETDATE()), @Init = 10, @Terminal = 10
SET @Age = @NowYear - CAST(DATEPART(year, @Val2) AS NVARCHAR(10))
if @Gender = '1 '
SET @limitage = 60;
else if @Gender = '2 '
SET @LimitAge = 55;
SET @I = (@limitage - @age) / 5
if @i < 0
SET @i = 0;
SELECT @Init = 10 + ((@NowYear - 2001) / 5), @Terminal = 10 + @I
--BEGIN TRAN GetMaxEmployeeNo
IF NOT EXISTS(SELECT 1 FROM MaxEmployeeNo)
BEGIN
select @MaxNo = max(EmployeeNo) from employeeinfo with(TABLOCKX)
print '最大值： ' + @maxno
SET @MaxNo = SUBSTRING(@MaxNo, 1, 5)
INSERT INTO MaxEmployeeNo(CurrentMaxNo) VALUES(@MaxNo)
END
ELSE
SELECT @MaxNo = CurrentMaxNo FROM MaxEmployeeNo
select * from MaxEmployeeNo
SET @MaxNo = CAST(@MaxNo AS INT) + 1
PRINT @MaxNo
SET @MaxNo = left(CAST((CAST(@MaxNo AS INT) + 1) AS NVARCHAR(5)) + '00000 ', 5)
UPDATE MaxEmployeeNo SET CurrentMaxNo = CurrentMaxNo + 1
--IF @@ERROR <> 0
--ROLLBACK TRAN GetMaxEmployeeNo
--ELSE
--COMMIT TRAN GetMaxEmployeeNo
print '初始值： ' + CAST(@init AS NVARCHAR(10))
PRINT '加值后的最大值: ' + @MaxNo
SELECT @UserName = CAST(@MaxNo AS NVARCHAR(10)) + CAST(@Init AS NVARCHAR(10)) + CAST(@Terminal AS NVARCHAR(10));
print '0用户名 ' + @UserName
SELECT * FROM INSERTED
/* 用户名产生结束 */
IF LEN(@UserName) < 3
RETURN
DECLARE @SchoolCode NVARCHAR(36)
IF EXISTS(select CardID from EmployeeInfo where CardID IN (SELECT CardID FROM INSERTED) and isAdmin = '0 ')
RETURN
IF EXISTS(SELECT 1 FROM Users WHERE UserCode IN (SELECT UserCode FROM INSERTED))
RETURN
IF EXISTS(SELECT 1 from aspnet_users where UserId IN (SELECT UserCode FROM INSERTED))
RETURN
select TOP 1 @SchoolCode = a.SchoolCode from SchoolInfo a INNER JOIN INSERTED b ON a.OrgCode = b.ZZID
/* 插入用户 */
insert into [Users]([UserCode],[UserName],[Password],[RealName],[Birthday], [IsConfirm])
SELECT UserCode, @UserName, @PassWord, UserName, Birthday, 0
FROM INSERTED
print '1用户名 ： ' + @username
insert into aspnet_Users(ApplicationId,UserId,UserName,LoweredUserName,MobileAlias,IsAnonymous,LastActivityDate)
SELECT '43D27A0F-5AD5-4556-8A98-AAE0C1E43890 ',UserCode,@UserName, @PassWord,null,0,getdate()
FROM INSERTED
print '2用户名 ： ' + @username
insert into aspnet_Membership(ApplicationId,UserId,CreateDate,Password,PasswordFormat)
SELECT '43D27A0F-5AD5-4556-8A98-AAE0C1E43890 ',UserCode,getdate(), @PassWord,0
FROM INSERTED
print '3用户名 ： ' + @username
insert into EmployeeInfo ([EmployeeCode],[EmployeeNo],[Name],[CardID], ComeSchoolYear,JoinWorkYear,NationCode ,StaffTypeCode ,TeachStatusCode,GenderCode, Birthday,ZZID,SchoolCode, DegreeCode )
SELECT UserCode, @UserName, UserName, CardId, ComeToSchoolTime, JoinWorkTime, Nation, StaffType, TeachStatus, Gender, Birthday, ZZID, @SchoolCode, CultDegree
FROM INSERTED
print '4用户名 ： ' + @username
insert into EmployeeJobApellation(Code,EmployeeCode,JobApelCode)
SELECT newid(),UserCode,PostType
FROM INSERTED
print '5用户名 ： ' + @username
INSERT INTO [EmployeePosition]([Code], [EmployeeCode], [PositionCode], [AppointDate], [SchoolCode], [SFSY])
SELECT NEWID(), UserCode, PositionCode, GETDATE(), @SchoolCode, 1
FROM INSERTED
print '6用户名 ： ' + @username
INSERT INTO [dbo].[aspnet_UsersInRoles]([UserId], [RoleId])
SELECT UserCode, 'FBD43FD4-C839-475E-A656-754CEDA01AF5 '
FROM INSERTED
print '7用户名 ： ' + @username
PRINT ' 操作结束 '
IF @@ERROR = 0
COMMIT TRAN AddUsers
ELSE
ROLLBACK TRAN AddUsers
测试的时候，流水号应该是不会重复的，但实际上总会提示有唯一约束。导致整个语句失败。麻烦各位帮忙看一下
------解决方案--------------------
很长，先占个沙发
[解决办法]
jf
[解决办法]
建立触发器如下：
ALTER TRIGGER TG_InsertEmployee ON [SynchEmployeeTemp]
FOR INSERT
AS
------------
FOR INSERT
改为-------->
INSTEAD OF INSERT
[解决办法]
FOR INSERT 是在insert成功之后的操作，你的insert可能失败了
INSTEAD OF INSERT 是替代insert操作
[解决办法]
Mark有时间再看看