Trigger for Table Updates

Question

Post reply

Trigger for Table Updates

TJT

SSCommitted

Points: 1926
More actions
March 3, 2016 at 12:31 pm

#308617

Hi,
I was wondering if anyone had some advice or example for creating a trigger that fires when a specific table is updated. Every time an entry is added to this table, I would like this trigger to fire.

Viewing 12 posts - 1 through 11 (of 11 total)

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

Jon.Morisi SSChampion Points: 12846 More actions · Answer 1

CREATE TRIGGER reminder2

ON Sales.Customer

AFTER INSERT, UPDATE, DELETE

AS

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'AdventureWorks2012 Administrator',

@recipients = 'danw@Adventure-Works.com',

@body = 'Don''t forget to print a report for the sales force.',

@subject = 'Reminder';

GO

CREATE TRIGGER (Transact-SQL)

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

Don't know what you want your trigger to do but be careful. Triggers fire once per operation, not once per row. So you need to create set based queries in your triggers. And stay away from sending emails directly from your trigger like the previous example does. You do NOT want your inserts to wait on that. If you want to send emails it is better to put the data in a holding table and have another process pick up the information for emails on a schedule.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

TJT SSCommitted Points: 1926 More actions · Answer 3

What I need to do is send an email every time an entry is added to a specific table. Any ideas or suggestion on how best to this are appreciated.

Lowell SSC Guru Points: 323518 More actions · Answer 4

Sean Lange (3/3/2016)
Don't know what you want your trigger to do but be careful. Triggers fire once per operation, not once per row. So you need to create set based queries in your triggers. And stay away from sending emails directly from your trigger like the previous example does. You do NOT want your inserts to wait on that. If you want to send emails it is better to put the data in a holding table and have another process pick up the information for emails on a schedule.

sp_send_dbmail would not cause a wait, right? it's already an asynchronous service broker.

as long as permissions to send the mail are taken care of(so the trigger doesn't fail with the error user does not have permission to sp_send_dbmail) I'm under the impression , sending an email in a trigger would not cause a delay.

the old database mail from 2000 days, (whatever that was, I'm blocking it out the memory willfully now), yes, that was synchronous and evil.

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!

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

Lowell (3/3/2016)
Sean Lange (3/3/2016)
Don't know what you want your trigger to do but be careful. Triggers fire once per operation, not once per row. So you need to create set based queries in your triggers. And stay away from sending emails directly from your trigger like the previous example does. You do NOT want your inserts to wait on that. If you want to send emails it is better to put the data in a holding table and have another process pick up the information for emails on a schedule.
sp_send_dbmail would not cause a wait, right? it's already an asynchronous service broker.
as long as permissions to send the mail are taken care of(so the trigger doesn't fail with the error user does not have permission to sp_send_dbmail) I'm under the impression , sending an email in a trigger would not cause a delay.
the old database mail from 2000 days, (whatever that was, I'm blocking it out the memory willfully now), yes, that was synchronous and evil.

You are probably right Lowell. I don't make a habit of sending emails from triggers. I think if I were doing it I would do it that way just to be sure but it is probably overkill with an asynchronous broker. Thanks for the correction.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

TJT SSCommitted Points: 1926 More actions · Answer 6

Now if I want to grab information from this new row that has been entered, what is the best way before sending the email?

CREATE TRIGGER reminder2

ON Sales.Customer

AFTER INSERT, UPDATE, DELETE

AS

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'AdventureWorks2012 Administrator',

@recipients = 'danw@Adventure-Works.com',

@body = 'Don''t forget to print a report for the sales force.',

@subject = 'Reminder';

GO

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

TJT (3/3/2016)
Now if I want to grab information from this new row that has been entered, what is the best way before sending the email?
CREATE TRIGGER reminder2
ON Sales.Customer
AFTER INSERT, UPDATE, DELETE
AS
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'AdventureWorks2012 Administrator',
@recipients = 'danw@Adventure-Works.com',
@body = 'Don''t forget to print a report for the sales force.',
@subject = 'Reminder';
GO

You missed the point. You will need to look at the inserted or deleted virtual tables to retrieve those values. And then you MUST use a loop. This is why I suggest using a holding table. You can have your insert in the trigger be set based, including the values. Then have another process that uses a cursor to iterate RBAR over the holding table.

And since you say you want values for new rows you don't want to have your trigger fire for updates or deletes right?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

TJT SSCommitted Points: 1926 More actions · Answer 8

Hi SSCoach, do you have an example of a holding table? Also do you have an example of retrieving data from the " inserted or deleted virtual tables"?

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

TJT (3/3/2016)
Hi SSCoach, do you have an example of a holding table? Also do you have an example of retrieving data from the " inserted or deleted virtual tables"?

You cannot support such a thing with the knowledge you currently don't have. You really need to study up on triggers before you even think of using them. Please see the following URL for a start. I recommend you read it all and try things on a test bed before you write production code.

https://msdn.microsoft.com/en-us/library/ms189799.aspx

After that, I strongly recommend that you do a search on "triggers in SQL Server" using Google and learn some more. Triggers are not to be handled in a trivial manner. They will eat your face off if you don't program them correctly.

--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)

Jack Corbett SSC Guru Points: 184394 More actions · Answer 10

I'd also recommend reading this [/url], since I wrote it.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

Jack Corbett (3/4/2016)
I'd also recommend reading this [/url], since I wrote it.

And an excellent article for getting started with triggers. A must read!!

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/