Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Worst Practice - Triggering External Events Expand / Collapse
Author
Message
Posted Saturday, February 7, 2004 6:08 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Wednesday, June 11, 2014 5:42 PM
Points: 33,007, Visits: 15,132
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/column






Follow me on Twitter:
@way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #99220
Posted Monday, February 23, 2004 12:34 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 31, 2007 8:20 AM
Points: 885, Visits: 1

Hi Steve

You raise a good point with the article.  I believe it all comes down to architecture; a framework that really spells some of the basic principals your applications will take to suffice some end requirements.  A classic example is what you described, which can be wrapperd into the framework rules for end-user notification of events - in a structured fashine. 

This discussion also turns it head toward end-users and applications developers; the roles each should be playing in "solution definition".  Be very careful not to fall in the classic trap of end user dictating the solution.  Your example is a case in question I believe.  Here we have business owners tells us that we need to email xyz when something happens.  What tends to happen in the world of tech is the "no problem, we will write a trigger and.." sort of approach; rather, we should be asking more questions about the why and where-fors, only to find its a nasty business process issue that we are simply automating.  Tech can solve most problems these days in some fashine, we just need to make sure its the right problem.

Just thinking aloud

Cheers

Ck




Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Post #101675
Posted Monday, February 23, 2004 2:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:38 AM
Points: 7,034, Visits: 6,747

Good article. Must admit though I have only used triggers once to implement cascade deletes (I know problems, problems ) when I converted an Access database. Recently considered it as a method for recording a log of data updates.

This is something I will bear in mind for the future, it's great to get info like this as it will make me stop and think that one solution is not the only one.

I do wonder, that many people use triggers and I don't, that there is something wrong in the way I design databasea and build systems  or maybe I'm just lucky and don't have large complex systems




Far away is close at hand in the images of elsewhere.

Anon.

Post #101691
Posted Monday, February 23, 2004 9:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 6, 2005 10:53 AM
Points: 107, Visits: 1

Ok... you've convinced me... I was implementing a trigger that would email if certain rules were met.

I'm not saying that sending notifications to users (or running some other process) on data change is a bad idea. Although I think this is not the best way to handle things, that's a separate discussion. I'm focusing on the use of a trigger to drive these external events.

I hope you're writing that discussion soon.... How else would you do it without getting in a tangled mess (in this case, I filter the INSERTED rows and do a EXEC(sqlstatement) based on matches in a trig_rules table -- It seems that it would be hard to pass off a copy of INSERTED that would be "safe" if the trigger were to fire multiple times quickly:sick

Does the "if it fails, it gets rolled back" apply to an AFTER trigger?

 




Post #101770
Posted Monday, February 23, 2004 9:44 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Wednesday, June 11, 2014 5:42 PM
Points: 33,007, Visits: 15,132

I actually hadn't planned on the followup for "how to do this", but I've gotten a bunch of responses, so maybe I should.

I've got a couple of systems that I've used and I'll takle one shortly. Basically it involves "staging" the data that needs to be sent out and then using the staging area as a basis for notifications.








Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #101776
Posted Monday, February 23, 2004 7:30 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, June 21, 2011 10:03 AM
Points: 577, Visits: 102

Great article.  Writing a follow up - about how to "stage" the data and then scheduling external processing- would be awesome.  I've implemented it here but would love to here about someone elses methods.

Another thing not to do in triggers...Don't use Linked Server!  I would consider linked SQL servers to be external, as they require a connection that can fail for reasons outside of SQLs control.  Instead, I use a staging table that get's moved to the linked server as a batch.  I've tested this and performance is bad enough when the network is fast; I'd hate to see it when the network is bogged down.

An UNPROVEN problem I'm also having with triggers is when writing from one database to another on the same server.  For some reason I'm seeing a noticable performance degradation when I update a table in a different database.  I'm not sure this is not environment related, but I'm checking it out.

cl



Signature is NULL
Post #101856
Posted Tuesday, February 24, 2004 2:06 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:00 AM
Points: 2,888, Visits: 1,773

If I have a database dependent external process then I usually use a trigger to insert records into a logging table or tables.

For example, a new page gets published in a CMS so all subscribers should get an e-mail.

The trigger captures the publishing process and writes the page id to my logging table.  End of trigger.

An scheduled external process does a SELECT TOP n from my logging table and sends batches of e-mail.

This achieves two things.

  • The logging table acts as a giant out-box.
  • The external process with its SELECT TOP n is set to send e-mail in batches that are small enough to be digested by the mail server.


LinkedIn Profile
Newbie on www.simple-talk.com
Post #101886
Posted Tuesday, February 24, 2004 10:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 6, 2005 10:53 AM
Points: 107, Visits: 1

An scheduled external process does a SELECT TOP n from my logging table and sends batches of e-mail.

What does this?  Do you use a timed sql job, or do you have an external program (service?) running that queries the table every n seconds?

TIA,
Thor

PS.  Does all this staging stuff still make sense if the trigger is an after trigger?
It looks like I'll need to run a report-generating program using xp_cmdshell (doh!), and I don't want it fubaring the database...




Post #102027
Posted Tuesday, February 24, 2004 12:14 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, June 21, 2011 10:03 AM
Points: 577, Visits: 102

You can use whatever scheduling mechanism you want, really, all it has to do is make a database connection and execute a proc.  Personally, I usually use SQL jobs and DTS packages, as they're built in and pretty easy to use.

It's usually a process that "wakes up" every X amount of time and checks for new records to be processed (in the table being updated by the trigger).  For example, you could send an email for each row, update a web page, whatever (DTS is a good way to do these sorts of things if you're not a SQL guru).

This makes sense only in after triggers, really.  If the process needs to occur before the table with the trigger is updated then this won't work.  Rarely would an external process actually have to occur BEFORE the insert or update occurs.



Signature is NULL
Post #102060
Posted Tuesday, February 24, 2004 12:53 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 6, 2005 10:53 AM
Points: 107, Visits: 1

[From the article]
Which rolls back your update. Which may result in an error to your users or worse, removing a bunch of data they updated and not correctly telling them that the transaction was rolled back. Especially if it was a significant amount of data entry. Or the person is the VP of Sales

This makes sense only in after triggers, really.  If the process needs to occur before the table with the trigger is updated then this won't work.  Rarely would an external process actually have to occur BEFORE the insert or update occurs.

Can an after trigger roll back data if it fails?  I thought the data was "there" by the time the trigger fires....  If it can't alter the data, is there any problem with this "Worst Practice"?


 




Post #102068
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse