Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Is a SQL trigger what i need?

Author  Topic 

Codeman
Starting Member

2 Posts

Posted - 2013-11-07 : 14:42:18
First, hi people i hope you can help me please.

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

cheers
Mark

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-11-07 : 15:07:44
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.

Be One with the Optimizer
TG
Go to Top of Page

Codeman
Starting Member

2 Posts

Posted - 2013-11-07 : 15:22:12
TG,

thanks for your reply, it seems very complicated and could possibly go pear shape, ill look into scheduling a vb.net code to run this.

thanks for your time
Mark
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-08 : 00:57:56
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.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

emam razib

5 Posts

Posted - 2013-11-10 : 21:38:30
Thanks for nice shearing I have been learnt something from here..........
Thanks a lot.

unspammed
Go to Top of Page
   

- Advertisement -