[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 426
  • Last Modified:

Email Trigger

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
areifste
Asked:
areifste
  • 5
  • 4
1 Solution
 
BrandonGalderisiCommented:
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
 
areifsteAuthor Commented:
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
 
areifsteAuthor Commented:
Anyone know whats wrong?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
BrandonGalderisiCommented:
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
 
areifsteAuthor Commented:
i will try when i get back into work tommorrow. yes Food is the database name, dbo.requestmaster is the view name
0
 
areifsteAuthor Commented:
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
 
areifsteAuthor Commented:
Alright, I think the issue is that i am trying to place the trigger on a view.  Any suggesstions.
0
 
BrandonGalderisiCommented:
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
 
BrandonGalderisiCommented:
I believe that http:#a24932088 provides a working solution.
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now