[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Creating conditional AFTER trigger to email notification

Posted on 2009-04-28
12
Medium Priority
?
657 Views
Last Modified: 2012-05-06
Hi there,  I hope you can help me out.  We are using Microsoft SQL 2005.  We have a SQL table called IM_ITEM.  This is an item table for our retail store.  In that table is a column called IS_ECOMM_ITEM.  Which is the column that says if that item is an ecommerce item.  We have a seperate online store.  I need to know when any items are added, modified, or deleted that have this IS_ECOMM_ITEM equal to Y.  I am only concerned with the rows that have this set to Y.  So basically I need SQL server to notify me by email when any item (row) in this IM_ITEM table that has the IS_ECOMM_ITEM = Y is changed in any way.  It looks like a trigger might be my best bet here.  I can use sp_send_dbmail to send the email.  I have been trying different ideas, but I can't seem to get it to work.  I am not the best at the T-SQL syntax.  How would you set this up?  I attached the code I have so far.  There is probably quite a bit wrong with it.  One thing I am concerned with too is if there is more than one item updated at one time I would need to account for multiple rows in the insert and delete table right?  Thanks for your help with this.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Author
-- Create date: April 28, 2009
-- Description:	Trigger to send notification email when ecommerce items are updated
-- =============================================
CREATE TRIGGER UATR_ECOMMERCE_ITEM_CHANGES_NOTIFICATION ON dbo.IM_ITEM
FOR INSERT, UPDATE, DELETE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Insert statements for trigger here
 
	IF EXISTS (SELECT * FROM inserted
		WHERE IS_ECOMM_ITEM = 'Y' or SELECT * FROM deleted
		WHERE IS_ECOMM_ITEM = 'Y')
		
		EXEC msdb.dbo.sp_send_dbmail 
			@profile_name='SQL Server Notifications',
			@recipients='myemail@domain.com',
			@subject='Change to IM_ITEM table with ecommerce selected',		
			@query='SELECT * FROM inserted and SELECT * FROM deleted',
			@attach_query_result_as_file=1
END
GO

Open in new window

0
Comment
Question by:sqwasi
  • 6
  • 6
12 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24254827
I would never directly send out an email on a trigger.

I insert the necessary data into a queue table, and let a job that runs regularly (or that can also be started by the trigger) to do the send email stuff.
more generic solution, and avoiding that the code that raises the trigger fails due to any problems sending out the email...
0
 
LVL 2

Author Comment

by:sqwasi
ID: 24254875
How would I do this then?  I basically need to be notified as soon as an item that has the IS_ECOMM_ITEM equal to 'Y'.  I can create a table if we need to, but I wanted to make it as simple and easy as possible.  I don't need anything complicated.  The email that is sent just need to show me what item was updated, WHAT was updated and WHAT it used to be.  I am not very good at coding SQL so I could use more specific instruction if possible.  Thank you.
0
 
LVL 2

Author Comment

by:sqwasi
ID: 24254975
So basically what you are saying is that I need to create a queue table.  Then create a trigger that writes updated information to that table.  Then you say to create a job that runs regularly to grab those rows and send them by email.  I can create the table no problem.  How would the trigger look?  How do you make it so that the queue tables doesn't continue to grow with records?  What do you mean by create a job?  Where do you do that?  Thanks.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24254999
well, your code is almost fine, except this line:
@query='SELECT * FROM inserted and SELECT * FROM deleted',

it must be a valid sql specified there, for example:

@query='SELECT * FROM inserted UNION ALL SELECT * FROM deleted',

however, that will make it difficult to see which values were the old ones, and which the new ones.
presumably, you want to join the 2 tables using the primary key of the table (assuming that value never changes):
@query='SELECT i.id, i.col1, i.col2, d.col1 old_col1, d.col2 old_col2 FROM inserted i JOIN deleted d on d.ID = i.ID ',

Open in new window

0
 
LVL 2

Author Comment

by:sqwasi
ID: 24255121
OK.  You make a good point though.  If I was to set it up like that is it not going to finish this update if the trigger fails for some reason?   The updating of these items is really important and I don't want to interfere with it.  So those changes need to go through no matter what happens with the trigger.  It shouldn't be based on the trigger.  Is that what you were saying about putting it into a queue table?

Also, I am sorry, but your @query code doesn't quite make sense to me.  I am guessing the i.id means insert table ID column?  i.col1 would be the first column in the insert table that I need to pull out.  However, I am not sure what the old_col1 is?  Or where it says FROM inserted i.  Do I replace the i with something?  Sorry for the dumb questions.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24255187
>Is that what you were saying about putting it into a queue table?
yes

>Also, I am sorry, but your @query code doesn't quite make sense to me.
let me explain:
>I am guessing the i.id means insert table ID column?
yes

> i.col1 would be the first column in the insert table that I need to pull out.
yes

> However, I am not sure what the old_col1
let me start off with the FROM part:
>FROM inserted i JOIN deleted d on d.ID = i.ID

the INSERTED table get's the alias I in the query, while the DELETED get's the alias d.
so, to refer to the column(s) of the new values, use i.col_name, for the old values, d.col_name.

the "old_col1" is the alias name given to the d.col1, because i.col1 already returns a column name "col1", and you cannot return the same column name twice in the query results, unless you give them distinct names.

so, the FROM part has to be kept as is, except that you might need to change "ID" by the real name of the primary key column
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24255195
I forgot: the FROM ... JOIN is to get the "old" and the "new" values together into 1 result row...
0
 
LVL 2

Author Comment

by:sqwasi
ID: 24255277
Thanks.  Let me see if I can give some more info to help clarify this.  As I mentioned the table name is IM_ITEM.  The primary key column is ITEM_NO (this is the item number).  When it sends the email I would like it to show all of the items (rows) that were changed and to list the ITEM_NO and exactly WHAT was changed.  So I wouldn't think I could specify any specific row like you have in your example. I would just need to give me the item_no, maybe the DESCR column and then the rest would be what information had changed.  Does that make sense?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24255316
> So I wouldn't think I could specify any specific row like you have in your example.
note: INSERTED and DELETED do contain ONLY the rows that did get updated, not the whole table by default!

if you need to find out what exactly changed can be done with some expression...
do you need to show DESCR of before and/or after?
0
 
LVL 2

Author Comment

by:sqwasi
ID: 24255438
Angellll, before I get too far down the wrong track in sending an email from a trigger, lets go back to what you said at first.  Lets turn this around.  Lets send this to a queue table.  Scratch what I have been saying.  OK.  So I create a table.  Then I would create a trigger that says for every insert, update, and delete on this IM_ITEM table where the IS_ECOMM_ITEM = 'Y' write those changes in this queue table.  Then I would create a SQL agent Job that would then periodically email me the latest updates and then remove them from the table.  Would that be correct.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 24255456
yes, that is the approach I use usually.
0
 
LVL 2

Author Comment

by:sqwasi
ID: 24255521
Well, your the genius, so I will go that route.  Now I will just dig around and see if I can figure out the exact code to do this.  Its only fare I ask another question for the code if I need to.  Thanks you have been a big help.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Loops Section Overview

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question