November 24, 2003 at 11:47 am
I have an app that issues select statements for one view in the db. I need to add some functionality, which involves updating a table based on the select statement. I can't alter the app, so I must work within the DB.
Is there a way to do this in SQL 2k? It seems triggers aren't available for select queries.
My current solution, which I don't really like, is to run a trace to a table in the db, and a trigger on the trace result table does the required work. It works, sort of, but it's a roundabout method to say the least, and not very portable with the db.
Any better ideas? Please?
Data: Easy to spill, hard to clean up!
November 25, 2003 at 1:58 am
Could you move the work of the select statement to a stored procedure and include the update stuff there, thus keeping the work that needs to be done together, then call the procedure from your app?
What is the select statement doing & what is the purpose of the subsequent update?
Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
November 25, 2003 at 9:22 am
Thanks for the response - here is some more info: I would love to have a stored procedure, but there's a problem: I can't change the app. There are many reasons for this (chief reason - I don't have the source code). A new app is in development which will solve the problem, but is a few months away.
Meanwhile the only "hook" I have to work with is the select query, generated by the app, which I can't change. The only way I figured to catch the "hook" is to use a profiler trace with results to a table, then attach an insert trigger to the table.
My question to everyone in the SQL Server Central Community: is there a better way?
Here is the query - only the numbers change based on the user actions.
Select * from ORVv_VideoBlock where lineid = 107 and (588045 >= StartLocation and 588045 < EndLocation or 588045 <= StartLocation and 588045 > EndLocation) order by ID
Data: Easy to spill, hard to clean up!
November 25, 2003 at 12:30 pm
I can't think of a way. To me it seems odd that you would need to track something based on a select and not a data modification. What are you wanting to get out of the data modification you wish to add to the select?
November 25, 2003 at 9:12 pm
Well...The purpose of the app is to manage railroad track assets (track charts). The select in question determines the video still to display, based on the location the user clicks on the track chart. Problem is, we have multiple video displaying different angles (forward, backward, side, overhead), or the same angle performed on a different date. The app was designed to only handle one video. It's easy to have the ORVv_VideoBlock view refer to different video information, but how to tell the db to switch videos? To minimize confusion, I want to let users do this from within the app. The app issues no updates, inserts, deletes, or stored procedures. My idea is to pick "hot spots" where clicking would cycle the videos, and/or have users triple-click to activate video rotation.
I incorporated both ideas in the triggers to a profiler trace table, and it works, although it doesn't seem an ideal solution. Sure wish I could trigger off a select!
Data: Easy to spill, hard to clean up!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply