Have created this trigger
It completes successfully and enters into the right tables, however when it all processes through on the table that it inserts into the table the Long Message section of the trigger has "NULL" inside in there.
It is not often that I look at SQL really. The actual part of the script that converts the data to XML string on its own works and makes a string. However when it is in the trigger it doesn't populate anything.
///////////////////////////////////////////
USE [SysproCompanyP]
GO
/****** Object: Trigger [dbo].[trig_DNIP] Script Date: 28/09/2015 11:25:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--=======================================
-- Author : Thomas Mullins
-- Create date : 02/09/2015
-- Description : Email event on a dispatch note being printed
-- Update
-- Update
--=======================================
ALTER TRIGGER [dbo].[trig_DNIP]
ON [dbo].[MdnMaster]
AFTER INSERT
AS
BEGIN
DECLARE @ConstCompany AS CHAR(1)
DECLARE @SourceCompanyFull AS CHAR(40)
DECLARE @DispatchNote AS CHAR(15)
DECLARE @SalesOrder AS CHAR(6)
DECLARE @CustomerName AS CHAR(30)
DECLARE @Customer AS CHAR(7)
DECLARE @var_LongMessage AS CHAR (255)
SET @SourceCompanyFull = DB_NAME()
SET @ConstCompany = UPPER(RIGHT(RTRIM(@SourceCompanyFull),1))
IF @ConstCompany = 'S'
BEGIN
SET @ConstCompany = '0'
END
SELECT
@DispatchNote = RTRIM(DispatchNote),
@SalesOrder = RTRIM(SalesOrder),
@CustomerName = RTRIM(CustomerName),
@Customer = RTRIM(Customer)
FROM INSERTED
SET NOCOUNT ON;
IF @Customer in ('CN01PIO', 'CN01WUH') --AND datepart(yyyy, @CreationDate) = datepart(yyyy, getdate() and datepart(mm, @CreationDate = datepart(mm, @CreationDate) = datepart (mm,getdate()) and datepart(dd, @CreationDate) = datepart(dd, getdate()
BEGIN
SET @var_LongMessage = (SELECT (STUFF((
SELECT ', ' + RTRIM(WRL.StockCode) + ' ' + RTRIM(WRL.Job) + ' '
FROM SysproCompanyP.dbo.MdnDetailLot (nolock) AS MDL
LEFT OUTER JOIN SysproCompanyP.dbo.WipReservedLots (nolock) AS WRL on WRL.Lot = MDL.Lot
WHERE MDL.DispatchNote = RTRIM(@DispatchNote)
GROUP BY WRL.StockCode, WRL.Job
FOR XML PATH('')
), 1, 2, '')
))
INSERT INTO [SysproCompanyC].[dbo].[EmailEvents]
([SourceCompany] ,[SourceCompanyFull] ,[ParentValue] ,[CFFName] ,[CFFData] ,[TxtFileExtension] ,[DateEntered] ,[LongMessage])
VALUES
(@ConstCompany ,@SourceCompanyFull ,@SalesOrder ,@Customer ,@DispatchNote ,'DNIP' ,GETDATE() ,@var_LongMessage)
INSERT INTO [Piolax_Logging].[dbo].[EventLogging] ([Company],[EventType],[Source],[Annotation],[NumberOfRecords],[DateTime])
VALUES (@ConstCompany ,'INFO' ,'[trig_DNIP]' ,'Dispatch Note Printed ' + @DispatchNote + @ConstCompany + @SourceCompanyFull + @SalesOrder + @Customer + 'DNIP' + HOST_NAME(),1 ,GETDATE())
END
END
If anybody could give my any info that would be great, thanks.
