Triggers, Stored Procedures and SELECTs

[LucieLastic]

hi All

I'll try to explain what my problem is, hope its understandable.

I have a table (say ValuesTable) which will contain calculated data extracted from T1 and T2. The user will write a SELECT statement on ValuesTable and it should trigger a 'refresh' of ValuesTable depending on a key field.

The idea I have at the moment (I'm not a T-SQL expert either, so I'm learning as I go along):

-Have a Trigger on the SELECT
-Trigger will call a Stored Proc which handles the refresh which will refresh the table the user is querying - possible?

Problem/query:
T1 is linked to T2 by 1 key field. T1 has several key fields. ValuesTable has several key fields. ValuesTable needs data from T1 and T2 for refresh.

The user may do a select on ValuesTable and not include the 1 key field needed for T2 when the Stored proc tries to refresh the table. So I need to check what fields have been entered in the select list - is this possible and would I do it in the trigger or pass the select string to the stored proc? If the 1 key field is missing in the select, then the stored proc will have to refresh the entire table rather than records relating to the 1 key field. - The proc would have to loop for all to match the key field in T2. I need to parse the SELECT statement before I can do anything and this 1 key field will be used if it's in the select or it will have to loop for each instance of it in T2.

Hope that makes some sense. Really need help with this one.

Hope someone can help.
lou

 

[JeanNiBee]

I'm very confused.

Can you show the structue for your T!, T2 and ValuesTable?

Then re-iterate your question.

Also, one thing I can tell you, don't use a SELECT trigger to update your values. Flip your solution and use UPDATE and INSERT triggers on T1 and T2 to fire only when the data is changed and have THEM update the ValuesTable.

When someone selects from ValuesTable you should assume the data is up to date.


-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR][/COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects

 

[LucieLastic]

hi JeanNiBee

Please don't waste any more thought on this at the moment as we're having a rethink about the whole thing.

lou