Im looking at solving a problem at work but im not sure if its possible.
Our software at work uses a SQL database, we can run reports from this or schedule reports to email automatically, im wanting to set triggers in SQL (if this is what they are for) to monitor a field (integer) and if the field reaches a certain number then SQL or an external program can run the reports automatically instead of waiting for the scheduled time to run (the scheduler is a separate program outside of SQL and outside of our software).
Any ideas where to start? I do all my programming in VB.Net
Triggers are really just stored procedures. But they are fired based on database events so they are perfect for associating something like a value change to process. However, that trigger process happens within the same transaction as the event that fired it. That is critical to understand because you generally don't want processes - especially outside the database processes like executables - to be in the same transaction as a user transaction. Because if anything goes wrong with the process then the user transaction is rolled back. Also the time it takes the trigger process to run is added on to whatever the user was doing.
So triggers are very powerful - but like Spiderman says, "with great power comes great responsibility"
One alternative is to schedule a sql job or a windows service to run very frequently and only send a report when the conditions are met. That way the report is real-enough-time but the reporting is not tied to database transactions.
you can just create a sql agent job to execute a procedure which checks for a value and then do the processing. then this job can scheduled as per your convenience.
Is a SQL trigger what i need?
Posted - 11/07/2013 : 14:42:18
