Solved

Email Trigger

Posted on 2009-07-13
11
415 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

New-MailboxSearch Powershell Command and step by step approach to Search and Extract Emails form Exchange 2013 Journaling server.
Granting full access permission allows users to access mailboxes present in their database. By giving full access permission one can open and read the content of any mailbox but cannot send emails from that mailbox.
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 >>…
In this video we show how to create a mailbox database 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 Servers >> Data…

809 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