Can this be done??

  • Thread starter Thread starter Morten Overgaard
  • Start date Start date
M

Morten Overgaard

Hi Sirs

Is it possible to create a transaction which inserts data into a table in
such a way that all the inserted rows are NOT visible to other processes
accessing the same table until the transaction commits the data??

Thanks in advance

Regards Morten
 
Let me rephrase.

Actually what I meant was

Is it possible for a transaction to only select the rows inserted by the
transaction itself ie. all other rows in the table created by other
transaction are not returned in a SELECT statement.

Thanks in advance

Regards Morten
 
I still don't understand. Is the Transaction only wrapped around Select
statement? If not, what else is it doing? Can you show the flow of events?

What is it you don't want seen?
 
Thanks for Bill's quick response!

Hi Morten,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to create a SELECT command
that will only return the records inserted within the transaction which
hasn't been committed. If there is any misunderstanding, please feel free
to let me know.

As far as I know, this is impossible to be achieved in transaction. Because
the SQL server database engine commits and rolls back transactions by
checking the transaction log. However, the transaction log cannot be
accessed by SELECT statement.

For workaround, I think you can insert the new records into a DataSet and
use DataSet.GetChanges to get the newly inserted rows. For more information
about DataSet.GetChanges method, please check the following link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfSystemDataDataSetClassGetChangesTopic.asp

HTH. If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Hi Kevin / Morten
I think it is possible.
What you have to do is Just Create a insert Trigger on the table in
which you are inserting the record and in the Trigger just write the
statement
Select * from Inserted

as Inserted table contains the newly inserted rows and fires one time of a
insert.
I thing this should work.


regds.....
Akhil
 
Hi Morten,

Thanks for sharing your experience with all the people here. If you have
any questions, please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Back
Top