Delete Transaction - 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 2008 Forums

Transact-SQL (2008)

Delete Transaction

Author

Topic

eljapo4
Posting Yak Master

100 Posts

Posted - 2014-04-11 : 11:10:11

Hi can someone please verify the below transaction please?
BEGIN TRANSACTION;
DELETE
--Step 1
FROM dbo.TableA
WHERE SA_APP_ID IN(
'2A9B01B1-BFA2-E211-8C81-00265587DAD4',
'79BF9504-C9E8-E211-ADA6-00265587DAD4'
)

IF @@ROWCOUNT >= 1

DELETE
FROM dbo.TableB
WHERE ENVA_APP_ID IN(
'2A9B01B1-BFA2-E211-8C81-00265587DAD4',
'79BF9504-C9E8-E211-ADA6-00265587DAD4'
)

ELSE IF @@ROWCOUNT = 1
COMMIT TRANSACTION;
ELSE
ROLLBACK;

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-04-11 : 11:19:59

quote:
Originally posted by eljapo4

Hi can someone please verify the below transaction please?
BEGIN TRANSACTION;
DELETE
--Step 1
FROM dbo.TableA
WHERE SA_APP_ID IN(
'2A9B01B1-BFA2-E211-8C81-00265587DAD4',
'79BF9504-C9E8-E211-ADA6-00265587DAD4'
)

IF @@ROWCOUNT >= 1

DELETE
FROM dbo.TableB
WHERE ENVA_APP_ID IN(
'2A9B01B1-BFA2-E211-8C81-00265587DAD4',
'79BF9504-C9E8-E211-ADA6-00265587DAD4'
)

ELSE IF @@ROWCOUNT = 1
COMMIT TRANSACTION;
ELSE
ROLLBACK;

Does not look right. The COMMIT TRANSACTION statement will never be executed. That is bad - it will leave dbo.TableA or at least some rows/pages locked.

What is the logic you are trying to implement?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-04-11 : 12:00:42

Do you really need to ehck to see if step one deleted a row first? Or can you just delete both?

BEGIN TRY
	BEGIN TRANSACTION;
	
	--Step 1
	DELETE 
	FROM dbo.TableA 
	WHERE SA_APP_ID IN(
	'2A9B01B1-BFA2-E211-8C81-00265587DAD4',
	'79BF9504-C9E8-E211-ADA6-00265587DAD4'
	)


	DELETE 
	FROM dbo.TableB
	WHERE ENVA_APP_ID IN(
	'2A9B01B1-BFA2-E211-8C81-00265587DAD4',
	'79BF9504-C9E8-E211-ADA6-00265587DAD4'
	)

	COMMIT TRANSACTION; 
END TRY
BEGIN CATCH
	-- D0 mroe checking here for open tran if needed.
	ROLLBACK TRANSACTION
END CATCH

eljapo4
Posting Yak Master

100 Posts

Posted - 2014-04-14 : 05:50:54

quote:
Originally posted by James K

quote:
Originally posted by eljapo4

Hi can someone please verify the below transaction please?
BEGIN TRANSACTION;
DELETE
--Step 1
FROM dbo.TableA
WHERE SA_APP_ID IN(
'2A9B01B1-BFA2-E211-8C81-00265587DAD4',
'79BF9504-C9E8-E211-ADA6-00265587DAD4'
)

IF @@ROWCOUNT >= 1

DELETE
FROM dbo.TableB
WHERE ENVA_APP_ID IN(
'2A9B01B1-BFA2-E211-8C81-00265587DAD4',
'79BF9504-C9E8-E211-ADA6-00265587DAD4'
)

ELSE IF @@ROWCOUNT = 1
COMMIT TRANSACTION;
ELSE
ROLLBACK;
Does not look right. The COMMIT TRANSACTION statement will never be executed. That is bad - it will leave dbo.TableA or at least some rows/pages locked.

What is the logic you are trying to implement?

I want to check that there are some records deleted from Table A before moving to delete the record from Table B (there should only be one record here)

eljapo4
Posting Yak Master

100 Posts

Posted - 2014-04-15 : 05:26:05

this query meets my needs.


BEGIN TRY
  BEGIN TRANSACTION;
 
 --There could be more than 1 record deleted from this Table
  IF (SELECT COUNT(SA_APP_ID) FROM TableA 
      WHERE SA_APP_ID = @APP_ID) >=1 AND
    (SELECT COUNT(ENVA_APP_ID) FROM dbo.TableB
     WHERE ENVA_APP_ID = @APP_ID) = 1 
  
BEGIN

  DELETE FROM dbo.TableA WHERE SA_APP_ID = @APP_ID
  DELETE FROM dbo.TableB WHERE ENVA_APP_ID = @APP_ID

END
  
  COMMIT TRANSACTION; 
  
END TRY
BEGIN CATCH
 ROLLBACK TRANSACTION
END CATCH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources