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?
Go to Top of Page

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
Go to Top of Page

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)
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -