Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Email Trigger

Posted on 2009-07-13
11
Medium Priority
?
423 Views
Last Modified: 2013-12-18
I am trying to create a trigger that will email the results of a record that was submitted through asp website into sql server database (2005).

Would like the trigger to fire an email after a new record is submitted.
Would like the email itself to be a summary or the entire record.
The Table View name is requestMaster.
The Database Name is Food.
Do I need to list off all the fields in the Table?

I am receiving this error when I execute.

Msg 102, Level 15, State 1, Procedure AlertRequest, Line 11
Incorrect syntax near '@squery'.
Msg 137, Level 15, State 2, Procedure AlertRequest, Line 16
Must declare the scalar variable "@squery".


CREATE TRIGGER Food.AlertRequest 
   ON  Food.RequestMaster 
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Insert statements for trigger here
@squery = SELECT * from RequestMaster
 
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = DBMail,
    @recipients = 'areifste@gmail.com',
    @query = @squery,
    @subject = @message,
    @attach_query_result_as_file = 1 ;
END
GO

Open in new window

0
Comment
Question by:areifste
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
11 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24840751
You would be best served to use a queue table and write the new records to be mailed to it.  This way the mail server doesn't interfere with your trigger.

But this should work.

Try:


CREATE TRIGGER Food.AlertRequest 
   ON  Food.RequestMaster 
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
declare @squery nvarchar(max) 
    -- Insert statements for trigger here
set @squery = 'SELECT * from inserted'
 
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = DBMail,
    @recipients = 'areifste@gmail.com',
    @query = @squery,
    @subject = @message,
    @attach_query_result_as_file = 1 ;
END
GO

Open in new window

0
 

Author Comment

by:areifste
ID: 24842560
I think we are on the right track.

I am getting the following error:

Msg 8197, Level 16, State 4, Procedure AlertRequest, Line 1
Object 'Food.RequestMaster' does not exist or is invalid for this operation.

I appreciate your help!

Food is the database name,RequestMaster is the View name
CREATE TRIGGER Food.AlertRequest 
   ON  Food.RequestMaster 
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
declare @squery nvarchar(max)
declare @message nvarchar(max)
    -- Insert statements for trigger here
set @squery = 'SELECT * from inserted'
set @message = 'A New Food Request has been made.'
 
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = DBMail,
    @recipients = 'areifste@gmail.com',
    @query = @squery,
    @subject = @message,
    @attach_query_result_as_file = 1 ;
END
GO

Open in new window

0
 

Author Comment

by:areifste
ID: 24843907
Anyone know whats wrong?
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24844370
is food your schema name or your database name?

change create trigger to this.  I missed the trigger name.  I'm guessing your schema is dbo and your database is food.  Correct as necessary.

Although I don't know how well a sp_send_dbmail will work pulling from inserted.  I've never tried.
CREATE TRIGGER Food_AlertRequest 
   ON  Food.dbo.RequestMaster 
   AFTER INSERT
AS 
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
declare @squery nvarchar(max) 
    -- Insert statements for trigger here
set @squery = 'SELECT * from inserted'
 
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = DBMail,
    @recipients = 'areifste@gmail.com',
    @query = @squery,
    @subject = @message,
    @attach_query_result_as_file = 1 ;
END
GO

Open in new window

0
 

Author Comment

by:areifste
ID: 24845058
i will try when i get back into work tommorrow. yes Food is the database name, dbo.requestmaster is the view name
0
 

Author Comment

by:areifste
ID: 24849200
No luck still telling me.  

Msg 8197, Level 16, State 4, Procedure Food_AlertRequest, Line 1
Object 'dbo.RequestMaster' does not exist or is invalid for this operation.

any recommendations?
0
 

Author Comment

by:areifste
ID: 24852303
Alright, I think the issue is that i am trying to place the trigger on a view.  Any suggesstions.
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 2000 total points
ID: 24932088
Try qualifying your insert with the DB name.  It seems to be resolving the table name from inserted but it is executing in the MSDB database.

I don't know if this will work but...


CREATE TRIGGER Food_AlertRequest 
   ON  Food.dbo.RequestMaster 
   AFTER INSERT
AS 
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
declare @squery nvarchar(max) 
    -- Insert statements for trigger here
set @squery = 'SELECT * from food.dbo.inserted'
 
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = DBMail,
    @recipients = 'areifste@gmail.com',
    @query = @squery,
    @subject = @message,
    @attach_query_result_as_file = 1 ;
END
GO

Open in new window

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 25547927
I believe that http:#a24932088 provides a working solution.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

The new Gmail Phishing Scam going around is surprising even the savviest of users with its sophisticated techniques.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In this video we show how to create a Contact in Exchange 2013. We show this process by using the Exchange Admin Center. Log into Exchange Admin Center.: First we need to log into the Exchange Admin Center. Navigate to the Recipients >> Contact ta…
In this video we show how to create an Address List in Exchange 2013. We show this process by using the Exchange Admin Center. Log into Exchange Admin Center.: First we need to log into the Exchange Admin Center. Navigate to the Organization >> Ad…

705 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