Compare SQL database after delete/update statements

Question

We have had some issues where an update or delete operation on the database would update/delete more records then expected. This has passed multiple layers of testing and reviews. Now I am looking for some tool that can help me to test only the correct records where updated or maybe another strategy to prevent situations likes this.

I have looked at SQL compare of Red gate, this works pretty good, but data setup and compare is a bit time consuming.

Maybe there is a easier and or better way to prevent data loss when using update/delete queries in code:

• Which tool, possible run-able from the command-line
• Automated, if so please explain how
• Other testing techniques
• Process steps during development cycle

How do you tackle this?

Answer 1

The way I could do it would be through SOAPUI. I perform the following steps:

1. Create an empty project.
2. Create a test suite for all the planned test cases.
3. For each test case, add a JDBC test step that guarantees the consistency of the database.
4. Add assertions to check, count, validate that just the registries that were intended to be deleted were removed

Though I must say I'm not really sure this is the easiest way to tackle the problem, I talk from my experience.

Good luck