Heyo,

Anyone who's seen my posts on here know's I'm a DBA intern for a big company who's been learning alot about MSSQL Server the past year or so. This is the first time I'm posting a question beyond how to write a particular complicated query, and this is a real stretch for me.

Right now I'm working on an in-house application directory which stores information about applications that can't be queried or otherwise generated. The data includes information such as who wrote the apps, who uses them, what the app does, what other apps it relies on, where it's hosted, etc. When application developers make changes, they have to submit changes to this directory to be reviewed and approved by managers and various other beaurucratic processes.

The problem I have is the higher ups want to view the changes exactly as they will appear in the database before they approve them, and commit the changes to the DB. The other DBA's and I have been discussing exactly how to do this including duplicating all the tables, duplicating the entire database, etc. The changes can be complicated including inserts, updates, and deletes on several tables at once. Does anyone have recommednations or know of any papers online or something discussing strategies on issues like this?

Thanks!

---
Dlongnecker

You can use different DB comparision tool

see this link http://www.sqlservercentral.com/articles/Tools/64908/

Regards,
Nitin

Not quite what I was looking for.

What I'm looking for is help designing a database in which changes to any of the tables (inserts, updates, deletes) are not applied until a supervisor/manager of some kind approves them. For example, we could duplicate the entire database, and any rows in the "pending" database can be approved by a manager which would then be moved to the production database. This solution is bulky, and makes changes to the database hard to manage.

All interaction with the data has to be done by end users using a custom application. A DB Comparison tool for DBA's would be inappropriate.

Small use case scenario: Joe wants to list that his application is used by payroll, and logs into an application tied to our database. He finds the record of his application and adds payroll to the list of users. His manager later logs into the same application and finds the change awaiting approval. The manager can then approve the change (and the actual record is updated), or disapprove the change (and the record is not updated).

---
Dlongnecker

I would add a flag to the data for "IsApproved" or something like that. Defaults to 0, and can only be changed to 1 by a manager. Queries and such used to report data out of the database would either ignore rows with a 0 in the flag, or would report it as Pending, depending on whether it was a manager viewing the data or not.

I think that's what you're looking for.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

GSquared, you're on the right track.

A simple pending flag like that wouldn't solve the issue of records that were, updated.

Maybe a multi-option flag, such as
0 = Live or production record
1 = Pending Insertion
2 = Pending Delete
3 = This record is a pending update, and there should be a corresponding record with a 0 flag.

This then runs into issues such as what if someone flags a record to delete, but then someone else flags it for an update and such.

---
Dlongnecker

Something like that would require a lookup table and some more complex rules, but it could certainly be done.

If you go that way, you might also want to add an audit log to that table, to keep track of when it was originally added, when approved, when marked for deletion, etc.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

I'd look at implementing the good ole' crab trap technique. In short - operate with 2 structurally identical DB's. One is the one the users can enter into, edit, etc....the other is the "approved stuff". On a specified interval, run a compare between the 2, and allow the higher-ups to approve/deny each of the changes.

Not all that different from GSquared's idea, really. It's just sometimes a little easier to do it that way than to try to shoehorn it all into one single set of objects.....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

That solution makes a lot of sense. Here's something I've been thinking about:

I will place a trigger on the database (actually on every table) and anytime a user executes an INSERT, UPDATE, or DELETE I will copy the data from the temporary INSERT and DELETE tables into two tables in the database made to hold that information, and assign a GUID to all the records in these two tables that are related to the users actions. The trigger will never allow the users original INSERT, UPDATE, or DELETE to take place.

whenever a manager logs in, they can then review all the the insert and update tables and approve or deny the changes.

This leads to a question - can I have one trigger at the database level that will fire on INSERT, UPDATE, and DELTE's or do I need a trigger on each and every table.

---
Dlongnecker

Every table.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

The pain to wordcount ratio on that post was obscene.

---
Dlongnecker