TPBasintek
asked on
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_trig ger] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[INSERT_HISTORY_trig ger]
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
USE [database]
GO
/****** Object: Trigger [dbo].[INSERT_HISTORY_trig
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[INSERT_HISTORY_trig
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.