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.
| Author |
Topic |
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
 Posted - 2006-07-18 : 19:12:55
|
Hi all, We have an app requiring that data changes in a particular table are authorised by another user. We are handling this at the moment by writing to a 'releases' table, but it is inefficient because it goes down to column level (i.e. one 'release' row per column whose data has changed). This method is not ideal, mainly because the method of authorising the changes requires lots of messy SQL (and doing it the front-end is extremely slow...).I know there must be a better way of going about it. I had thought of creating status flags on each of the tables that require authorisation. In this way, changes to a record would result in a new row of status 'unreleased'. Authorising it would simply be a matter of changing the status of a couple of rows. But this method may cause problem with data integrity - particularly if we include the row status as part of the PK. I'd appreciate any input you guys/gals may have (also very open to suggestions...though not in that way )Cheers,Tim |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-07-18 : 21:57:57
|
| the status flag sounds good to me...i did something like this in a moving inventory system where checks/orders don't get posted into the ledger until they are authorized, also remember to add a date column to track the time for the changes and an audit table to log who did what on those table, problem is you may need to consider the max row size limit if you add another columnhowever, the table approach sounds more appealing because it has less impact on the tables themselves, you only need to modify your sproc to make verifications to these tables, we did something like this in a call center, where orders where put on hold until they were verified and finally committed to the permanent tables then deleting the temp tables afterwards, lots of processing and making sure you don't make a mistake especially during commits (assuming this is set based)hope this helps...--------------------keeping it simple... |
 |
|
|
|
|
|
|
|
Authorising data changes