Solved

Email Trigger

Posted on 2009-07-13
11
418 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Local Continuous Replication is a cost effective and quick way of backing up Exchange server data. The following article describes the steps required to configure Local Continuous Replication. Also, the article tells you how to restore from a backup…
Pop culture is prime bait for hackers seeking to infect user’s computers and mobile devices with malicious malware. Hackers know exactly what the latest trends are online and know how to use them to their advantage.
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

679 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