CODEWITHSUNDEEP
sql
Ryan O'Donnell
Ryan O'Donnell

Reputation: 617

Determine if a SQL Insert/Update statement affects the result from a stored Select Statement

Thought this would be a good place to ask for some "brainstorming." Apologies if it's a little broad/off subject.

I was wondering if anyone here had any ideas on how to approach the following problem:

First assume that I have a select statement stored somewhere as an object (this can be the tree form of the query). For example (for simplicity):

SELECT A, B FROM table_A WHERE A > 10;

It's easy to determine the below would change the result of the above query:

INSERT INTO table_A (A,B) VALUES (12,15);

But, given any possible Insert/Update/Whatever statement, as well as any possible starting Select (but we know the Selects and can analyze them all day) I'd like to determine if it would affect the result of the Select Statement.

It's fine to assume that there won't be any "outside" queries, and that we know about all the queries being sent to the DB. It is also assumed we know the DB schema.

No, this isn't for homework. Just a brain teaser I've been thinking about and started to get stuck on (obviously, SQL can get very complicated.)

Upvotes: 1

Views: 65

Answers (2)

Philip Kelley
Philip Kelley

Reputation: 40289

Based on the reply to the comment, I'd say that without additional criteria, this ranges between very hard and impossible.

Very hard (leastways, it would be for me) because you'd have to write something to parse and interpret your SQL statements into a workable frame of reference for your goals. Doable, but can it be worth the effort?

Impossible because some queries transcend phrases like "Byzantinely complex". (Think nested queries, correlated subqueries, views, common table expressions, triggers, outer joins, and who knows what all.) Without setting criteria such as "no subqueries, no views or triggers, no more than X joins" and so forth, the problem becomes open-ended enough to warrant an NP Complete answer.

Upvotes: 1

PulseLab
PulseLab

Reputation: 1579

My first thought would be to put a trigger on table_A, where if any of the columns you're affecting (col A in this case) changes to meet (or no longer meet) the condition (> 10 here), then the trigger records that an "affecting" change has taken place.

E.g. have another little table to record a "last update timestamp", which the trigger could pop a getdate() into when it detects such a change.

Then, you could check that table to see if the timestamp has changed since the last time you ran the select query - if it has, then you know you need to re-run it, if it hasn't, then you know the results would be the same.

The table could hold many such timestamps (one per row, perhaps with the table/trigger name as a key value in another column) to service many such triggers.

Advantage? Being done in a trigger on the table means no risk of a change that could affect the select statement being missed.

Disadvantage? I guess depending on how your select statements come into existence, you might have an undesirable/unmanageable overhead in creating the trigger(s).

Upvotes: 1

Related Questions