areifste
asked on
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".
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
ASKER
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
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
ASKER
Anyone know whats wrong?
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.
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
ASKER
i will try when i get back into work tommorrow. yes Food is the database name, dbo.requestmaster is the view name
ASKER
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?
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?
ASKER
Alright, I think the issue is that i am trying to place the trigger on a view. Any suggesstions.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I believe that http:#a24932088 provides a working solution.
But this should work.
Try:
Open in new window