22,210
社区成员
发帖
与我相关
我的任务
分享
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[T_addStudent]
on [dbo].TBMemberIntegralCode
for insert
as
declare @count int
select @count=count(*) from TBMemberIntegralCode where JFcode=(select JFcode from inserted)
if @count>0
begin
ROLLBACK TRAN
end
else
begin
insert into TBMemberIntegralCode select [Member_ID]
,[JFcode]
,[GainIntegral]
,[AvailableIntegral]
,[WorkDate]
,[Type]
,[Results]
,[State]
,[Creator_ID]
,[Creator_Time]
,[ToBranch_ID] from inserted
end
if @count>0
begin
ROLLBACK TRAN
end
你的问题把这里的0改成1就可以了。触发器是在你插入数据后才执行的,所以你插入第一条数据触发这个触发器的时候,表里面已经有了一条数据了(先插入才会执行触发器的内容,第一条数据已经进了表里了)。因此你把这里的0改成1就没问题了。
但是你这样写这个触发器还是有点问题的。你有没有想过当你一个批处理同时插入多笔记录的时候会发生什么。这个触发器就直接报错了。注意不是回滚而是报错。ALTER TRIGGER [dbo].[T_addStudent] ON [dbo].TBMemberIntegralCode
FOR INSERT
AS
DECLARE @count INT
SELECT @count = COUNT(*)
FROM TBMemberIntegralCode
WHERE JFcode IN ( SELECT JFcode --这里返回的是结果集改成in
FROM inserted
)
IF @count > 0
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
INSERT INTO TBMemberIntegralCode
SELECT [Member_ID] ,
[JFcode] ,
[GainIntegral] ,
[AvailableIntegral] ,
[WorkDate] ,
[Type] ,
[Results] ,
[State] ,
[Creator_ID] ,
[Creator_Time] ,
[ToBranch_ID]
FROM inserted
END
ALTER TRIGGER [dbo].[T_addStudent]
ON [dbo].TBMemberIntegralCode
FOR INSERT
AS
BEGIN
SELECT *
INTO #tmp
FROM INSERTED AS a WHERE NOT EXISTS (
SELECT 1 FROM TBMemberIntegralCode AS b WHERE a.JFcode=b.JFcode
)
IF EXISTS(SELECT 1 FROM #tmp)
BEGIN
INSERT INTO TBMemberIntegralCode
SELECT [Member_ID],
[JFcode],
[GainIntegral],
[AvailableIntegral],
[WorkDate],
[Type],
[Results],
[State],
[Creator_ID],
[Creator_Time],
[ToBranch_ID]
FROM #tmp
END
DROP TABLE #tmp
END
这样看看行不行?