SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Is a SQL trigger what i need?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Codeman
Starting Member

United Kingdom
2 Posts

Posted - 11/07/2013 :  14:42:18  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 11/07/2013 :  15:07:44  Show Profile  Reply with Quote
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

United Kingdom
2 Posts

Posted - 11/07/2013 :  15:22:12  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 11/08/2013 :  00:57:56  Show Profile  Reply with Quote
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
Starting Member

Bangladesh
5 Posts

Posted - 11/10/2013 :  21:38:30  Show Profile  Reply with Quote
Thanks for nice shearing I have been learnt something from here..........
Thanks a lot.

unspammed
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000