Use Database Insert Trigger to Publish Reports

Is it possible to distribute reports based on a database insert trigger, using the standard edition of SQL Server 2008 R2? The trigger will be looking for a certain column to have a certain value, which will indicate that it is ti开发者_JAVA百科me to fire off a report. This will not happen very often, probably a couple of times a day.

This must be a common scenario, and yet I can't find any information on it, which must mean that I'm asking the wrong question. (I am completely new to SQL Server.)

TSQL using FireEvent?

Thanks,

Mike

Is there any aversion to checking for the value in the application, then execute and deliver the report from there using the report execution webservice? I don't like the idea of database triggers kicking off external processes. Too many clunky configuration dependencies and opportunities for failure for my taste.

If you really want to pursue this, this seems to be least complicated way (it requires reporting services and application database to on same instance):

• Create reporting services subscription that delivers report in desired format
• Open the SQL Server agent job list and get the unique identifier of the job name that corresponds to the subscription that was created (you'll have to look at job creation and last/next run datetime to find which job relates to your subscription)
• Create trigger that executes the msdb.dbo.sp_start_job procedure, passing the job name that matches your subscription

See what I mean about clunky dependencies? I would definitely stick with checking values and delivering the report in the application layer if at all possible.

The reason you do not want to use a trigger for this is that you will not be able to insert/update/delete if reporting is down.

Can you have some slack time (5 minutes or so)? If so do a trigger to send to a differnt table that the change was made and have a job to look at that table for the change every five minutes.