存储过程事务
ALTER proc [dbo].[Unigo_Community_DeletePrivateMessage]
@userId int,
@privateMessageId int
as
begin
if(@userId >0 and @privateMessageId >0)
begin
BEGIN TRANSACTION Tran_MSG
BEGIN
delete from user_to_p_msg
where user_id = @userId
and private_message_id = @privateMessageId
delete from P_MSG where user_id=@userId and private_message_id =@privateMessageId
END
IF (@@ERROR<>0)
ROLLBACK TRANSACTION Tran_MSG
ELSE
COMMIT TRANSACTION Tran_MSG
end
end
游标实例
USE [Unigo.Com]
GO
DECLARE @UserId int
SET @UserId=1144
DECLARE @UserId_tmp int
DECLARE @MsgId_tmp int
DECLARE DELETE_MESSAGE CURSOR FOR
SELECT user_to_p_msg.user_id ,p_msg.private_message_id FROM
[dbo].[p_msg]
INNER JOIN
[dbo].user_to_p_msg
ON
[dbo].[p_msg].private_message_id =
[dbo].user_to_p_msg.private_message_id
where [dbo].[p_msg].user_id =@UserId
OPEN DELETE_MESSAGE
FETCH NEXT FROM DELETE_MESSAGE
INTO @UserId_tmp,@MsgId_tmp
WHILE @@FETCH_STATUS = 0
BEGIN
Print '---'+ cast(@UserId_tmp as varchar)+'---'+cast(@MsgId_tmp as
varchar)
EXEC [dbo].[Unigo_Community_DeletePrivateMessage] @UserId_tmp,@MsgId_tmp
FETCH NEXT FROM DELETE_MESSAGE INTO @UserId_tmp,@MsgId_tmp
END
CLOSE DELETE_MESSAGE
DEALLOCATE DELETE_MESSAGE
-----DELETE Two
DECLARE DELETE_MESSAGE_2 CURSOR FOR
SELECT
user_to_p_msg.user_id,p_msg.private_message_id
FROM
[dbo].[p_msg]
LEFT OUTER JOIN
[dbo].user_to_p_msg
ON
[dbo].[p_msg].private_message_id =
[dbo].user_to_p_msg.private_message_id
where [dbo].user_to_p_msg.user_id =@UserId
OPEN DELETE_MESSAGE_2
FETCH NEXT FROM DELETE_MESSAGE_2
INTO @UserId_tmp,@MsgId_tmp
WHILE @@FETCH_STATUS = 0
BEGIN
Print '---'+ cast(@UserId_tmp as varchar)+'---'+cast(@MsgId_tmp as
varchar)
EXEC [dbo].[Unigo_Community_DeletePrivateMessage] @UserId_tmp,@MsgId_tmp
FETCH NEXT FROM DELETE_MESSAGE_2 INTO @UserId_tmp,@MsgId_tmp
END
CLOSE DELETE_MESSAGE_2
DEALLOCATE DELETE_MESSAGE_2
IF @@ERROR=0
BEGIN
--Delete P_MSG Info
delete P_MSG where user_id=@UserId
--Delete UserFriendBlackList
END
GO
USE [Unigo]
GO
DECLARE @UserId int
SET @UserId=1144
DELETE tblUserFriendBlackList WHERE UserId=@UserId
GO
责任编辑：小草
