Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Authors
About us
Contact us
Newsletters
Write for us
Keep up to date - daily newsletter:
Sign up
Back
SQLServerCentral
Register
Home
»
SQL Server 7,2000
»
Strategies
»
Approving Changes
Approving Changes
Post reply
Like
139
Add to Briefcase
1
2
Next
Approving Changes
View
Options
Author
Message
dlongnecker-802303
dlongnecker-802303
Posted 9 years ago
#636414
Right there with Babe
Group: General Forum Members
Points: 752
Visits: 199
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
4
Quote
nitinpatel31
nitinpatel31
Posted 9 years ago
#636435
SSCrazy
Group: General Forum Members
Points: 2807
Visits: 293
You can use different DB comparision tool
see this link http://www.sqlservercentral.com/articles/Tools/64908/
Regards,
Nitin
4
Quote
dlongnecker-802303
dlongnecker-802303
Posted 9 years ago
#636482
Right there with Babe
Group: General Forum Members
Points: 752
Visits: 199
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
5
Quote
GSquared
GSquared
Posted 9 years ago
#636486
SSC Guru
Group: General Forum Members
Points: 169569
Visits: 9732
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
5
Quote
dlongnecker-802303
dlongnecker-802303
Posted 9 years ago
#636489
Right there with Babe
Group: General Forum Members
Points: 752
Visits: 199
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
38
Quote
GSquared
GSquared
Posted 9 years ago
#636510
SSC Guru
Group: General Forum Members
Points: 169569
Visits: 9732
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
5
Quote
Matt Miller (4)
Matt Miller (4)
Posted 9 years ago
#636525
SSC Guru
Group: General Forum Members
Points: 82254
Visits: 20422
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?
4
Quote
dlongnecker-802303
dlongnecker-802303
Posted 9 years ago
#636533
Right there with Babe
Group: General Forum Members
Points: 752
Visits: 199
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
35
Quote
GSquared
GSquared
Posted 9 years ago
#636573
SSC Guru
Group: General Forum Members
Points: 169569
Visits: 9732
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
4
Quote
dlongnecker-802303
dlongnecker-802303
Posted 9 years ago
#636576
Right there with Babe
Group: General Forum Members
Points: 752
Visits: 199
The pain to wordcount ratio on that post was obscene.
---
Dlongnecker
31
Quote
Go
Post reply
Like
139
Add to Briefcase
1
2
Next
Post quoted reply
Permissions
You
can't
post new topics.
You
can't
post topic replies.
You
can't
post new polls.
You
can't
post replies to polls.
You
can't
edit your own topics.
You
can't
delete your own topics.
You
can't
edit other topics.
You
can't
delete other topics.
You
can't
edit your own posts.
You
can't
edit other posts.
You
can't
delete your own posts.
You
can't
delete other posts.
You
can't
post events.
You
can't
edit your own events.
You
can't
edit other events.
You
can't
delete your own events.
You
can't
delete other events.
You
can't
send private messages.
You
can't
send emails.
You
can
read topics.
You
can't
vote in polls.
You
can't
upload attachments.
You
can
download attachments.
You
can't
post HTML code.
You
can't
edit HTML code.
You
can't
post IFCode.
You
can't
post JavaScript.
You
can
post emoticons.
You
can't
post or upload images.
Select a forum
SQL Server 2017
SQL Server 2017 - Administration
SQL Server 2017 - Development
SQL Server 2016
SQL Server 2016 - Administration
SQL Server 2016 - Development and T-SQL
SQL Server 2014
Administration - SQL Server 2014
Development - SQL Server 2014
SQL Server 2012
SQL 2012 - General
SQL Server 2012 - T-SQL
SQL Server vNext
SQL Server 15 - Administration
SQL Server 15 - Development
SQL Server 2008
SQL Server 2008 - General
T-SQL (SS2K8)
June 2007 CTP
Working with Oracle
July CTP
SQL Server Newbies
Security (SS2K8)
SQL Server 2008 High Availability
SQL Server 2008 Administration
Data Corruption (SS2K8 / SS2K8 R2)
SQL Server 2008 Performance Tuning
Cloud Computing
SQL Azure - Development
SQL Azure - Administration
Amazon AWS and other cloud vendors
General Cloud Computing Questions
CosmosDB
Azure Data Lake
Azure Machine Learning
Reporting Services
Reporting Services
Reporting Services 2005 Administration
Reporting Services 2005 Development
Reporting Services 2008/R2 Administration
Reporting Services 2008 Development
SSRS 2012
SSRS 2014
SSRS 2016
Programming
Connecting
General
SMO/RMO/DMO
XML
Service Broker
Powershell
Testing
TFS/Data Dude/DBPro
SSDT
Continuous Integration, Deployment, and Delivery
R Services and R Language
Data Warehousing
Integration Services
Strategies and Ideas
Analysis Services
Data Transformation Services (DTS)
Performance Point
Data Mining
PowerPivot
R language
Machine Learning
Database Design
Disaster Recovery
Design Ideas and Questions
Relational Theory
Hardware
Virtualization
Security and Auditing
SQLServerCentral.com
Anything that is NOT about SQL!
Contests!
Editorials
SQLServerCentral.com Announcements
SQLServerCentral.com Website Issues
Suggestions
Tag Issues with Content
Podcast Feedback
SQLServerCentral.com Test Forum
Articles Requested
SQL Server 2005
Administering
Backups
Business Intelligence
CLR Integration and Programming.
Data Corruption
Development
Working with Oracle
SQL Server 2005 Compact Edition
SQL Server 2005 General Discussion
SQL Server 2005 Security
SQL Server 2005 Strategies
SS2K5 Replication
SQL Server Express
SQL Server 2005 Performance Tuning
SQL Server 2005 Integration Services
T-SQL (SS2K5)
SQL Server Newbies
SQL Server 7,2000
Administration
Backups
Data Corruption
General
Globalization
In The Enterprise
Working with Oracle
Security
Strategies
SQL Server Newbies
Service Packs
SQL Server CE
Performance Tuning
Replication
Sarbanes-Oxley
T-SQL
SQL Server Agent
SQL Server and other platforms
MySQL
Oracle
PostgreSQL
DB2
SQL Server and Sharepoint
Older Versions of SQL (v6.5, v6.0, v4.2)
Older Versions of SQL (v6.5, v6.0, v4.2)
Career
Certification
Employers and Employees
Events
Job Postings
Resumes and Job Hunters
Presentations and Speaking
Retired Members
Testing Center
Question of the Day (QOD)
SQL Server Security Skills
Microsoft Access
Microsoft Access
Products and Books
Third Party Products
CA
SQLCentric
Extreme Technologies.
Innovartis
Embarcadero
SQL Sentry
Golden Gate Software
Idera
Lumigent
Red Gate Software
Quest Software
ApexSQL
Sonasoft
Discussions about Books
Discuss Programming Books
Discuss XML Books
Discuss Data Warehousing Books
Discuss T-SQL Books
Discuss DTS Books
Discuss SQL Server 2000 Books
Discuss SQL Server 7.0 Books
Notification Services
Administration
Article Discussions
Future Versions
SQL 12
Narrow your search by forum
Explore
Home
Latest
Popular
Calendar
Members
Who's on
Moderators
Forums
SQL Server 2017
SQL Server 2017 - Administration
SQL Server 2017 - Development
SQL Server 2016
SQL Server 2016 - Administration
SQL Server 2016 - Development and T-SQL
SQL Server 2014
Administration - SQL Server 2014
Development - SQL Server 2014
SQL Server 2012
SQL 2012 - General
SQL Server 2012 - T-SQL
SQL Server vNext
SQL Server 15 - Administration
SQL Server 15 - Development
SQL Server 2008
SQL Server 2008 - General
T-SQL (SS2K8)
June 2007 CTP
Working with Oracle
July CTP
SQL Server Newbies
Security (SS2K8)
SQL Server 2008 High Availability
SQL Server 2008 Administration
Data Corruption (SS2K8 / SS2K8 R2)
SQL Server 2008 Performance Tuning
Cloud Computing
SQL Azure - Development
SQL Azure - Administration
Amazon AWS and other cloud vendors
General Cloud Computing Questions
CosmosDB
Azure Data Lake
Azure Machine Learning
Reporting Services
Reporting Services
Reporting Services 2005 Administration
Reporting Services 2005 Development
Reporting Services 2008/R2 Administration
Reporting Services 2008 Development
SSRS 2012
SSRS 2014
SSRS 2016
Programming
Connecting
General
SMO/RMO/DMO
XML
Service Broker
Powershell
Testing
TFS/Data Dude/DBPro
SSDT
Continuous Integration, Deployment, and Delivery
R Services and R Language
Data Warehousing
Integration Services
Strategies and Ideas
Analysis Services
Data Transformation Services (DTS)
Performance Point
Data Mining
PowerPivot
R language
Machine Learning
Database Design
Disaster Recovery
Design Ideas and Questions
Relational Theory
Hardware
Virtualization
Security and Auditing
SQLServerCentral.com
Anything that is NOT about SQL!
Contests!
Editorials
SQLServerCentral.com Announcements
SQLServerCentral.com Website Issues
Suggestions
Tag Issues with Content
Podcast Feedback
SQLServerCentral.com Test Forum
Articles Requested
SQL Server 2005
Administering
Backups
Business Intelligence
CLR Integration and Programming.
Data Corruption
Development
Working with Oracle
SQL Server 2005 Compact Edition
SQL Server 2005 General Discussion
SQL Server 2005 Security
SQL Server 2005 Strategies
SS2K5 Replication
SQL Server Express
SQL Server 2005 Performance Tuning
SQL Server 2005 Integration Services
T-SQL (SS2K5)
SQL Server Newbies
SQL Server 7,2000
Administration
Backups
Data Corruption
General
Globalization
In The Enterprise
Working with Oracle
Security
Strategies
SQL Server Newbies
Service Packs
SQL Server CE
Performance Tuning
Replication
Sarbanes-Oxley
T-SQL
SQL Server Agent
SQL Server and other platforms
MySQL
Oracle
PostgreSQL
DB2
SQL Server and Sharepoint
Older Versions of SQL (v6.5, v6.0, v4.2)
Older Versions of SQL (v6.5, v6.0, v4.2)
Career
Certification
Employers and Employees
Events
Job Postings
Resumes and Job Hunters
Presentations and Speaking
Retired Members
Testing Center
Question of the Day (QOD)
SQL Server Security Skills
Microsoft Access
Microsoft Access
Products and Books
Third Party Products
CA
SQLCentric
Extreme Technologies.
Innovartis
Embarcadero
SQL Sentry
Golden Gate Software
Idera
Lumigent
Red Gate Software
Quest Software
ApexSQL
Sonasoft
Discussions about Books
Discuss Programming Books
Discuss XML Books
Discuss Data Warehousing Books
Discuss T-SQL Books
Discuss DTS Books
Discuss SQL Server 2000 Books
Discuss SQL Server 7.0 Books
Notification Services
Administration
Article Discussions
Future Versions
SQL 12
SQLServerCentral
Register
Search
Narrow your search by forum
Unthreaded, ascending
Unthreaded, descending
Subscribe to topic
Print topic
RSS feed
Go to topics forum
Jump to page
Jump to page
Copyright © 2002-2018 Redgate. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.