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
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.)