Can I use a Trigger in this scenario?

Question

Hi folks,

I have a table (ie. ReportData) which contains some calculated data -> eg. classroom average scores, number of students, avg grade point, etc. etc. lots of report like data. Works great.

Now, in some of my other tables (eg. the ClassRoom table) if i change some data in there, I need to update the ReportData table.

So I thought that if i do any INSERT, UPDATE or DELETE on the ClassRoom table, then i can run my stored procedure which updates the ReportData table, data.

SO i thought a trigger would be good for this. But, I'm not sure. Why? Well, the stored procedure which is used to do all the custom math will actually occasionaly query the ClassRoom table. So will it access the most recent (eg. Changed) data?

eg. If i change the field 'NumberOfStudents' from a 4 -> 5. Will the stored procedure (which will end up checking that field, on the ClassRoom table, retrieve a 4 or 5 as the data?

Thanks :)

NOTE: ClassRooms, etc are just fake for the purpose of this discussion.

Answer 1

The rule of transactions is that you can always see you own changes to the data.

Answer 2

Maybe I have some misuderstanding on your question, but SQL Server 2008 has two types of triggers: AFTER (that fires after the changes will applied) and INSTEAD OF (you can do what you want before of changes of the table applied). If you want to update the ClassRoom table and then recalculate the ReportData table using new values - just use AFTER trigger.