Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

Email from Insert Trigger-SQL

I am looking to send an email everytime a record is inserted into a table, i have a attached the code i have in the trigger,

and below is the message i am getting when i attemp to insert into the table:

Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 478
Query execution failed: Msg 208, Level 16, State 1, Server LINE500V7\SAGE, Line 2
Invalid object name 'inserted'.
Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 478
Query execution failed: Msg 208, Level 16, State 1, Server LINE500V7\SAGE, Line 2
Invalid object name 'inserted'.
ALTER TRIGGER [scheme].[email_insert] 
   ON  [scheme].[opdetm] 
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
 
EXEC msdb.dbo.sp_send_dbmail 
@profile_name = '1',
@recipients = 'eoin.conway@meteorelectrical.com',
@subject = 'subject',
@body = 'body',
@query = 
'SELECT order_no FROM INSERTED with (NOLOCK)',
@execute_query_database = 'demo'
END

Open in new window

0
meteorelec
Asked:
meteorelec
  • 4
  • 4
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the "inserted" is not available in the sp_send_dbmail procedure context.

you will have to make it like this:
ALTER TRIGGER [scheme].[email_insert] 
   ON  [scheme].[opdetm] 
   AFTER INSERT
AS 
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON; 
DECLARE @sql VARCHAR(1000)
SET @sql = 'SELECT ' + cast(order_no as varchar(100)) + ' order_no ' from inserted
 
EXEC msdb.dbo.sp_send_dbmail 
@profile_name = '1',
@recipients = 'eoin.conway@meteorelectrical.com',
@subject = 'subject',
@body = 'body',
@query = @sql,
@execute_query_database = 'demo'
END 

however, that will only send 1 of the values of order_no in case several orders are inserted at once.

Open in new window

0
 
meteorelecAuthor Commented:
Msg 156, Level 15, State 1, Procedure email_insert, Line 16
Incorrect syntax near the keyword 'from',

thats what i'm getting when i try to modify the trigger to that.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry...
ALTER TRIGGER [scheme].[email_insert] 
   ON  [scheme].[opdetm] 
   AFTER INSERT
AS 
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON; 
DECLARE @sql VARCHAR(1000)
SELECT @sql = 'SELECT ' + cast(order_no as varchar(100)) + ' order_no ' 
from inserted
 
EXEC msdb.dbo.sp_send_dbmail 
@profile_name = '1',
@recipients = 'eoin.conway@meteorelectrical.com',
@subject = 'subject',
@body = 'body',
@query = @sql,
@execute_query_database = 'demo'
END 

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
meteorelecAuthor Commented:
how would i add the order_line_no, i tryed to

@query = @OrderNo,@OrderLineNo,

but that didn't work,
declare @OrderNo CHAR(10)
declare @OrderLineNo CHAR(5)
 
select @OrderNo = [order_no] from inserted with (NOLOCK)
select @OrderLineNo = [order_line_no] from inserted with (NOLOCK)
 
Begin
 
EXEC msdb.dbo.sp_send_dbmail 
@profile_name = '1',
@recipients = 'eoin.conway@meteorelectrical.com',
@subject = 'subject',
@body = 'body',
@query = @OrderNo,
@execute_query_database = 'demo'
 
	End

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
here we go:
ALTER TRIGGER [scheme].[email_insert] 
   ON  [scheme].[opdetm] 
   AFTER INSERT
AS 
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON; 
DECLARE @sql VARCHAR(1000)
SELECT @sql = 'SELECT ' + cast(order_no as varchar(100)) + ' order_no ,' 
 + cast(order_line_no as varchar(100)) + ' order_line_no '
from inserted
 
EXEC msdb.dbo.sp_send_dbmail 
@profile_name = '1',
@recipients = 'eoin.conway@meteorelectrical.com',
@subject = 'subject',
@body = 'body',
@query = @sql,
@execute_query_database = 'demo'
END 

Open in new window

0
 
meteorelecAuthor Commented:
I am trying to add a value using a join, but it doesn't seem to be working...

remove the below line it works fine,

+ cast(d.product as varchar(100)) + ' product '
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
DECLARE @Cost FLOAT
DECLARE @sql VARCHAR(1000)
 
SELECT @Cost = [cost]from inserted with (NOLOCK)
SELECT @sql = 
'SELECT ' 
+ cast(i.order_no as varchar(100)) + ' OrderNo ,' 
+ cast(i.order_line_no as varchar(100)) + ' OrderLineNo ,'
+ cast(d.product as varchar(100)) + ' product '
from inserted i
JOIN scheme.opdetm d
ON i.order_no = d.order_no
AND i.order_line_no = d.order_line_no
 
IF @Cost=0
 
BEGIN 
 
EXEC msdb.dbo.sp_send_dbmail 
@profile_name = '1',
@recipients = 'eoin.conway@meteorelectrical.com',
@subject = 'subject',
@body = 'body',
@query = @sql,
@execute_query_database = 'demo'
 
END
END

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
is the product a varchar field?

if yes, change that line to:
+  '''' + replace(d.product , '''', '''''') + ''' product '

Open in new window

0
 
meteorelecAuthor Commented:
Brilliant,

thats sorted, thanks a million
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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