1

I have a database with multiple tables

and the user can change the data in the table.

my problems is that I wont that nothing changes in the database until the user click the button "save", and even when he do - it submit only the table he decide to save

but in the meantime it is necessary that the user can see all the changes that he did. and every "select" must give him the modified data ,and not the base data.

how I can on the one hand not submit the data in the database, and On the other hand show the data modified to the user?

I thought to do a transaction and don't submit, (and use read uncommitted) but for that I must don't close the connection (if I close without submit - all the changes are canceled) and I don't wont leave several of connection open.

I also thought to build a list of all the change, and whenever the user make a select - first searching from the list. but it is very complicated , and I prefer a simple solution

Thank you

chmouel kalifa
  • 129
  • 2
  • 11

2 Answers2

0

You can use temporary tables to store a temporary data and then move them when it will need.

Vyacheslav Shcherbak
  • 94
  • 5
0

This is going to be very tricky to handle as you've insisted that you cannot use transactions.

Best I can suggest is to add columns to each table to represent the state - but even then that's going to be tricky on how you'd ensure userA see's the pre-change and userB the post but not yet committed.

Perhaps you could look at using two tables and have a view selecting the pertinent data from both depending on the requirements.

Either way it's a nasty way to go about it and not very performant.

The moment you insisted you couldn't use a transaction is the moment you took away any chance of a simple answer.

A temporary table won't help here (as suggested above) as it's tied to the connection which you state will be closed. The only alternative temp table solution is a global temporary table but that also leads to issues (who creates it, what if you're the last connection to use it, check to see if it exists etc.)

Rachel Ambler
  • 1,440
  • 12
  • 23
  • I didn't "insisted" anything, if I can do the job with tanscaction - grate! but how? – chmouel kalifa Sep 18 '15 at 14:06
  • Then leave the connection open and commit the Transaction when the user presses save. If they exit out the close the transaction and the changes will be rolled back, ditto if the app breaks connectivity. – Rachel Ambler Sep 18 '15 at 19:16
  • I thought I can't leave leave the connection open. because there is not enough connections, and it will crash. if you suggest it's the better solution - I will try it – chmouel kalifa Sep 19 '15 at 19:48
  • Sql can support 32,767 active connections depending on how it's configured and how much memory there is. – Rachel Ambler Sep 19 '15 at 19:54