• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 382
  • Last Modified:

Create SQL Trigger and the Email HTML table

I am trying the email a HTML table after an event is triggered and I can't seem to get the @query section working.  This works if I do not try to send the query results.


USE [database]
GO
/****** Object:  Trigger [dbo].[INSERT_HISTORY_trigger]  ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[INSERT_HISTORY_trigger]
ON  [dbo].[HISTORY]
AFTER INSERT
AS

declare @PartID varchar(50)
SET @PartID = (SELECT PRIMARY_KEY FROM INSERTED)


SET NOCOUNT ON

IF NOT EXISTS(SELECT 1
FROM INSERTED I, [db].[dbo].[VE_VALIDATE] PCV
WHERE(I.TBL_NAME = 'PART') AND (I.COL_NAME = 'PRODUCT_CODE') AND (I.ACTION = 'UPDATE')AND(I.PRIMARY_KEY = PCV.[Part ID]) AND (I.CREATE_DATE = PCV.[Part Maint Change Date]))

 RETURN

declare @query nvarchar (max)
set @query =
      
N'<table>' +
N'<caption>Product Code change</caption>'+
N'<tr><th>PM changed By</th><th>PM Old Product Code</th><th>PM New Product Code</th><th>Order Type</th><th>Order ID</th><th>Line or Lot</th><th>Status</th><th>Part ID</th><th>Part Desc</th><th>Current Product Code on Order</th></tr>'+

cast(
(SELECT
        td = V.[Part Maint Changed By],'',
      td = V.[Old Product Code],'',
      td = V.[New Product Code],'',
      td = V.[Document],'',
      td = V.[ID],'',
      td = V.[Line/Lot],'',
      td = V.[STATUS],'',
      td = V.[Part ID],'',
      td = V.[Part Description],'',
      td = V.[Order Qty],'',
      td = V.[Complete Qty],'',
      td = V.[Current Product Code],''
      FROM [db].[dbo].[VE_VALIDATE] V, INSERTED TR
      WHERE V.[Part ID] = TR.PRIMARY_KEY and V.[Part Maint Change Date] = TR.CREATE_DATE
      ORDER BY [ID] DESC
      for xml path ('tr'), type) as nvarchar(max)) +
      N'</table>'

SELECT @QUERY


DECLARE @subss VARCHAR(1024)
DECLARE @bodys VARCHAR(1024)

SET @subss = @PartID+' Product Code Change Notification'
SET @bodys = '*** THIS WILL AFFECT GL ACCOUNTS ***'

    BEGIN
        EXEC msdb.dbo.sp_send_dbmail
          @profile_name = 'SQLAgentProfile',
          @recipients = 'myname@mydomain.com',
          @importance ='High',
          @subject = @subss ,
          @execute_query_database = db,

              @body = @bodys,

-- I think something is messing up here

              @query = 'SET NOCOUNT ON; SELECT @QUERY',
              --@query = 'SELECT @QUERY',
              --@query_no_truncate = 1,
          @BODY_FORMAT = 'HTML'


    END
0
TPBasintek
Asked:
TPBasintek
1 Solution
 
jogosCommented:
@query = 'SET NOCOUNT ON; SELECT @QUERY';

This is exacly what is passed as the @query parameter

You  don't want to pass the value ' @QUERY' but the content of that variable so

declare @queryCmd nvarchar (max)
set @queryCmd = 'SET NOCOUNT ON; SELECT ' + @QUERY;

Open in new window


and as parameter in  your exec
@query = @queryCmd,

Open in new window

0

Featured Post

Industry Leaders: 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!

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