Problem with transactions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Problem with transactions

Author

Topic

madhulatha_b
Starting Member

22 Posts

Posted - 2006-06-15 : 00:09:27

I have written a stored procedure to delete old records from multiple tables in database by using Transactions

By mistake I forgot one child table. With out deleting the records from that table I tried to remove records from master.So While executing the query I have got error (foreign key constraint). After that also records got deleted from other tables

My question here is when using Begin trans and Commit trans, if error comes it should not commit the results .. but in my case even though the error came it has deleted records from all the tables.

My stored procedure is below

I have got problem while deleting the records from ORDER table. After that it has executed the next delete statements

Please suggest if there is any problem with my stored procedure

*******************************************************************

Create Procedure DeleteOldProjects as

DECLARE @treenode char(255)

Create table #tmp_treenode (got_id uniqueidentifier,
ID_ varchar(255))

Create table #CODE (got_id uniqueidentifier)

DECLARE Treenode_Cur CURSOR FOR

SELECT treenode FROM nodeattributes1
where datediff(day,project_Releasedate,getdate()) > 365 OR project_releasedate is NULL

OPEN Treenode_Cur

FETCH NEXT FROM Treenode_Cur INTO @treenode
WHILE(@@FETCH_STATUS=0)
BEGIN
Begin Tran Tran1

INSERT INTO #tmp_treenode SELECT got_id,ID_ FROM treenode t2 where t2.got_id = @treenode
INSERT INTO #code SELECT got_id FROM treenode t2 where t2.got_id = @treenode

WHILE(SELECT COUNT(*) FROM treenode t2 WHERE t2.got_parent in (select got_id from #code))>0
BEGIN
INSERT INTO #tmp_treenode SELECT got_id,ID_ FROM treenode t2 WHERE t2.got_parent in (select got_id from #code)
SELECT got_id into #code1 from treenode t2 WHERE t2.got_parent in (select got_id from #code)
DELETE FROM #code
INSERT INTO #code SELECT * FROM #code1
--SELECT * INTO #code FROM #code1
DROP TABLE #code1
END
Commit Tran Tran1
FETCH NEXT FROM Treenode_Cur into @treenode
END

Begin Tran Tran2

SELECT got_id into #tmp_order
FROM got..orders
WHERE treenode in (SELECT got_id FROM #tmp_treenode)

DELETE ProjectMembers WHERE treenode in (select got_id from #tmp_treenode)
DELETE cbschinanodeinfo WHERE treenode in (select got_id from #tmp_treenode)
DELETE Got..Orders WHERE Treenode in (select got_id from #tmp_treenode)

DELETE NonOrderableProductRepository WHERE Parent_ID in (select ID_ from #tmp_treenode)

DELETE NodeAttributes1 WHERE treenode in (select got_id from #tmp_treenode)
DELETE TreeNode WHERE Got_id in (select got_id from #tmp_treenode)

Commit Tran Tran2

DROP TABLE #tmp_treenode
DROP TABLE #code

CLOSE Treenode_Cur
DEALLOCATE Treenode_Cur
GO

Kristen
Test

22859 Posts

Posted - 2006-06-18 : 02:54:25

Your problem, as I understand it, is that the first SQL Error (FK constraint violation in your case) will abort the transaction - the rest of the "job" is then running with NO transaction :-( (unless the very next thing is a rollback)

We get around this by having

SET XACT_ABORT ON

is ALL (absolutely no exceptions!) our SProcs which causes the WHOLE Sproc to abort on the first error.

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-19 : 05:48:29

If you want to delete data from all tables, then refer
http://sqlteam.com/forums/topic.asp?TOPIC_ID=65341

Read more about error handling here
http://www.sommarskog.se/error-handling-I.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources