SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Worst Practice - Triggering External Events


Worst Practice - Triggering External Events

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62250 Visits: 19102
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
My Blog: www.voiceofthedba.com
ckempste
ckempste
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1361 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"
David Burrows
David Burrows
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9548 Visits: 9751

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.


thormj
thormj
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 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?





Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62250 Visits: 19102

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
My Blog: www.voiceofthedba.com
Calvin Lawson
Calvin Lawson
SSC Eights!
SSC Eights! (870 reputation)SSC Eights! (870 reputation)SSC Eights! (870 reputation)SSC Eights! (870 reputation)SSC Eights! (870 reputation)SSC Eights! (870 reputation)SSC Eights! (870 reputation)SSC Eights! (870 reputation)

Group: General Forum Members
Points: 870 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
David.Poole
David.Poole
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7530 Visits: 3284

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
thormj
thormj
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 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...





Calvin Lawson
Calvin Lawson
SSC Eights!
SSC Eights! (870 reputation)SSC Eights! (870 reputation)SSC Eights! (870 reputation)SSC Eights! (870 reputation)SSC Eights! (870 reputation)SSC Eights! (870 reputation)SSC Eights! (870 reputation)SSC Eights! (870 reputation)

Group: General Forum Members
Points: 870 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
thormj
thormj
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 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"?






Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search