Solved

Email from Insert Trigger-SQL

Posted on 2008-06-26
8
1,087 Views
Last Modified: 2012-05-05
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
Comment
Question by:meteorelec
  • 4
  • 4
8 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21873267
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
 
LVL 2

Author Comment

by:meteorelec
ID: 21873337
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21873361
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
 
LVL 2

Author Comment

by:meteorelec
ID: 21873746
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 21873793
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
 
LVL 2

Author Comment

by:meteorelec
ID: 21874196
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21874343
is the product a varchar field?

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

Open in new window

0
 
LVL 2

Author Comment

by:meteorelec
ID: 21874474
Brilliant,

thats sorted, thanks a million
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

911 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now