■■■各位高手这个用游标的触发器这样写有没有必要
我里面用到一个错误处理，我的意图是当循环内发生错误时，继续执行下一个循环，所以用到continue语句,不知道恰当不恰当，请大家帮忙
ALTER TRIGGER [dbo].[Trig_Insert_I_fbxxi] ON [dbo].[I_fbxxi]
for INSERT
AS
-- 定义一个查询逻辑表的游标
DECLARE level_cursor cursor local for SELECT FSmsType,xx_id,zh_id,Sjhao,xxnrong FROM inserted
OPEN level_cursor
fetch next from level_cursor into @FSmsType,@xx_id,@zh_id, @Sjhao,@Content
while @@fetch_status=0
begin
SET @TeaID=dbo.fn_JXT_GetFullTeacherID(@zh_ID)
SET @XXID=substring(@TeaID,1,1)+substring(@TeaID,3,3)
--外地号码和小灵通用转到电信的网关发送
IF (left(@Sjhao,3)= '013 ' OR left(@Sjhao,3)= '015 ' and len(@Sjhao)=12 ) OR (left(@Sjhao,1)= '0 ' AND len(@Sjhao) <14)
BEGIN
IF (left(@Sjhao,3)= '013 ' OR left(@Sjhao,3)= '015 ' and len(@Sjhao)=12 )
begin
SET @Sjhao=substring(@Sjhao,2,len(@Sjhao)-1)
end
INSERT INTO MobileSend(Mobile, Content,zh_id) values(@Sjhao,@Content,@zh_id)
END
ELSE IF((left(@Sjhao,2)= '13 ' or left(@Sjhao,2)= '15 ') and len(@Sjhao)=11)
begin
INSERT sms_sendcontent
(SchoolID,TeacherID,Tag,ReadTag,Theme, SendMode,HomeNumber,ToName,ToID, Content, SendTime)
VALUES(@XXID, @TeaID, '0 ', '0 ' , 'I_fbxxi ', '2 ' ,@Sjhao, '1111 ', '1111 ',@Content,getdate())
IF @@error <> 0
begin
fetch next from level_cursor into @FSmsType,@xx_id,@zh_id, @Sjhao,@Content
continue
end
UPDATE I_fbxxi SET Fk_id=SCOPE_IDENTITY() WHERE xx_ID=@xx_id
END
IF @@error <> 0
begin
fetch next from level_cursor into @FSmsType,@xx_id,@zh_id, @Sjhao,@Content
continue
end
fetch next from level_cursor into @FSmsType,@xx_id,@zh_id, @Sjhao,@Content
END
-- 关闭游标
close level_cursor
-- 释放游标
DealLocate level_cursor
[解决办法]
问题如下：
1、很难保证数据的一致性，你的@@error判断在update语句后，前面的insert语句可能已经成功。
2、@@error判断并不能判断约束性的错误。
[解决办法]
现在用游标的少了，你还在TRIGGER里面写，很难说没有问题，难道不能用别的业务逻辑来代替
