Solved

Email Trigger

Posted on 2009-07-13
11
412 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
  • 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
 
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Utilizing an array to gracefully append to a list of EmailAddresses
Marketers need statistics and metrics like everybody else needs oxygen. In this article we explain how to enable marketing campaign statistics for Microsoft Exchange mail.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In this video we show how to create a Distribution Group 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 >>…

758 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now