t-sql 2012 use a trigger

Question

t-sql 2012 use a trigger

wendy elizabeth

SSCoach

Points: 16915
More actions
August 7, 2016 at 12:54 pm

#315960

In a change to a .net 2010 application that uses a sql server 2012 database, I am thinking of using a trigger when a duplicate record has been generated. This trigger would send outlook 2013 or office 365 email message to selected users. I am thinking of doing this instead of having the duplicate record email message sent out in the .net application. Thus would you show me the code and/or point me to a url that will show me how to accomplish my goal?

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63595 More actions · Answer 1

How is the data being inserted? By an SSIS package? Why not flag the duplicate there and manage the problem close to the data?

you don't need .NET at all for this, even if you do set up an alert. You could do it all inside the SSIS package.

If you did all this inside your package, you wouldn't need a trigger at all.

Lowell SSC Guru Points: 323546 More actions · Answer 2

Does the existence of duplicates actually break anything, or is it just a cleanup task because the .net application doesn't check for duplicates before adding the data? the urgency is the main factor on how to consider address this.

I'm assuming it will take a while for the .Net application to be modified to fix the issue, right? whether it's a third party app, or a long dev cycle, you cannot just fix it immediately in the app?

if this is a work around for the application's missing logic, you could fix the data with an instead of trigger, instead of just notifying that bad data exists.

the better thing i think, is to just create a job that tests for duplicates, and sends an email, instead of a trigger that does it;

do you really need near-real time emails? what's wrong with a job that runs every ten minutes? or every hour? or once a day?

do you already have database mail set up so you can send an email? I can post some code examples of either a trigger or a job step, but that assumes mail is already set up.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

pietlinden SSC Guru Points: 63595 More actions · Answer 3

Lowell brings up an interesting point that I totally forgot about. tightly coupling data insertion/triggers with e-mail can cause your inserts to fail if your mail server fails. (I remember Jeff Moden pointing that out to me a while back...)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

If you do this with a trigger, you MUST make it asynchronous!! I would either put the necessary information into a simple queue-style table or use SQL Server Service Broker. You cannot make an INSERT wait on (or fail because of) access to external resources like sending an email.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 5

To repeat what I stated on the following forum for this exact same question...

http://forums.sqlteam.com/t/t-sql-2012-trigger-and-generate-email-message/7349/2

Wouldn't it be easier to just prevent the duplicate "record" to begin with?

Apparently SQLTeam.Jassie1 = SSC.Wendy Elizabeth.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 6

pietlinden (8/7/2016)
Lowell brings up an interesting point that I totally forgot about. tightly coupling data insertion/triggers with e-mail can cause your inserts to fail if your mail server fails. (I remember Jeff Moden pointing that out to me a while back...)

I no longer know if that's true or not. With the changes they've made since way back when, they might have made it so that SQL Server caches the info locally if the mail server is down and allows the trigger to continue as it normally would. I've not had the opportunity to test that for real lately and don't know what will happen now.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lowell SSC Guru Points: 323546 More actions · Answer 7

TheSQLGuru (8/7/2016)
If you do this with a trigger, you MUST make it asynchronous!! I would either put the necessary information into a simple queue-style table or use SQL Server Service Broker. You cannot make an INSERT wait on (or fail because of) access to external resources like sending an email.

database mail is an asynchronous Service broker already, right? the old mail was synchronous, but that was SQL2000 days.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 8

Lowell (8/7/2016)
TheSQLGuru (8/7/2016)
If you do this with a trigger, you MUST make it asynchronous!! I would either put the necessary information into a simple queue-style table or use SQL Server Service Broker. You cannot make an INSERT wait on (or fail because of) access to external resources like sending an email.
database mail is an asynchronous Service broker already, right? the old mail was synchronous, but that was SQL2000 days.

I think that's correct. I do know that they're at least queued.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)