How to use Insert and update in a single transacti

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 2005 Forums

Transact-SQL (2005)

How to use Insert and update in a single transacti

Author

Topic

khufiamalik
Posting Yak Master

120 Posts

Posted - 2008-09-15 : 05:55:53

Hello All,
I have been using Transaction in my SP.

I have written a query which is updates a record and then right after that i delete some records from the same table( and all this is done within a single transaction).

but When I run the delete query separatly, It runs ok but when i run it from SP , it does not give any error but it does nothing with records.

Can I dont use the Update and then delete Query with in a single transaction?

Update Query
Update MyTable
SET date_Amount=date_Amount-@User_Provided_Amount,
WHERE ID=@UserProvidedID

Delete MyTable
where date_Amount=0.0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-15 : 05:58:34

Are you sure you've records with date_Amount=0 in your table after update?

khufiamalik
Posting Yak Master

120 Posts

Posted - 2008-09-15 : 05:59:57

Yes there are 3 records in my table

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-15 : 06:01:30

How are you wrapping the transaction around the two statements?

E 12°55'05.63"
N 56°04'39.26"

khufiamalik
Posting Yak Master

120 Posts

Posted - 2008-09-15 : 06:03:30

like

Begin Tran
1st statement

2nd statement
commit

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-15 : 06:04:26

quote:
Originally posted by khufiamalik

like

Begin Tran
1st statement

2nd statement
commit

doesnt seem like the problem. what does mesages tab say? 0 rows affected?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-15 : 06:05:36

Most probably the first UPDATE goes wrong (like substracting on a tinyint so that result is less than zero).

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-15 : 06:06:52

Also do you have any UPDATE DELETE triggers enabled on your table?

khufiamalik
Posting Yak Master

120 Posts

Posted - 2008-09-15 : 06:08:15

No I dont have any trigger

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-15 : 06:36:32

check if your table actually has records satisfying criteria. do like belwo

begin tran test
Update MyTable
SET date_Amount=date_Amount-@User_Provided_Amount,
WHERE ID=@UserProvidedID

select count(*) from MyTable WHERE date_Amount=0.0

Delete FROM MyTable
where date_Amount=0.0

COMMIT TRAN test

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources