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

Transact-SQL (2005)

Transactions

Author

Topic

sross81
Posting Yak Master

228 Posts

Posted - 2008-05-27 : 11:45:45

If you run the Begin Transaction code and then run a create such as an update query and you see that it effects the number of rows that you wanted it to effect is there a way to look at the actual data that changed before you Commit Transaction?

Thanks!

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2008-05-27 : 11:49:48

"run a create such as an update query" - What do you mean by this?

"is there a way to look at the actual data" - SELECT

=======================================
We should try to be the parents of our future rather than the offspring of our past. -Miguel de Unamuno

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-27 : 11:51:04

You mean to view the data you updated through transaction before commiting it?

sross81
Posting Yak Master

228 Posts

Posted - 2008-05-27 : 11:51:49

Sorry that was a typo. Basically just meant if I was running a query to update a table.

begin transaction

update a
set a.table_name=c.tablename,
a.field_name=c.fieldname
from lab_test_add_conf a
join emr_temp_labtest_configupdate b on a.labtest_key = b.labtestcode
join emr_zseg_code_descriptions c on b.zsegcode = c.zsegcode
where a.table_name = ' ' and a.field_name = ' 'and a.value_code = c.valuecode

commit transaction

How do I look at how the records were affected before I commit?

quote:
Originally posted by Bustaz Kool

"run a create such as an update query" - What do you mean by this?

"is there a way to look at the actual data" - SELECT

=======================================
We should try to be the parents of our future rather than the offspring of our past. -Miguel de Unamuno

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-27 : 11:54:21

One way would be to use an OUTPUT clause in your UPDATE statement. (see output in Books Online)

EDIT:
lots of examples: OUTPUT CLAUSE
http://msdn.microsoft.com/en-us/library/ms177564.aspx

Be One with the Optimizer
TG

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-27 : 13:32:45

I'm not 100% sure what you want, but maybe: @@ROWCOUNT.

sross81
Posting Yak Master

228 Posts

Posted - 2008-05-27 : 14:31:13

I still need to go through and read the Output link that the previous poster posted.

What I mean is that I would like to be able to select * from the table that I updated to see how the update query effected the table before I actually committed the change.

So basically

*Begin Transaction
*Update table (random code to update)
*Select * from the updated table to see how the rows that were updated were effected.
*Then commit transaction if I approve or rollback if I do not
approve.

Just wondering if it was possible. I tried to go look at the actual table before committing but you can't access it because it locks it down when the transaction begins.

quote:
Originally posted by Lamprey

I'm not 100% sure what you want, but maybe: @@ROWCOUNT.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-27 : 14:58:14

Does approving it mean information is sent back to the client or is it some some logic in your code?

If you have to manually approve the update (i.e. click a buttoin or something) then that is more complicated. If it is simple logic, then @@ROWCOUNT should work just fine.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources