SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER TRIGGER [trgDownloadAfterInsert]
ON [Download]
AFTER INSERT
AS
-- =============================================
-- Author:
-- Description: Populate XML Column after Raw inserted
-- =============================================
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER OFF; -- Insert with double quotes
DECLARE @ErrorMessage NVARCHAR(4000), @ErrorProcedure NVARCHAR(100), @ErrorSeverity INT, @ErrorState INT, @ErrorLine INT;
DECLARE
@RawTextnvarchar(max),
@Linenvarchar(max),
@NewLinenvarchar(max),
@LineTransformnvarchar(max),
@SqlStrnvarchar(4000),
@FileIDint,
@MaxRowsint,
@RecordCountint,
@Pointerint,
@SglQuotenvarchar(1) = Char(39),
@DblQuotenvarchar(1) = Char(34),
@Starnvarchar(1) = Char(42),
@EOLvarchar(2) = CHAR(13)+CHAR(10),
@MatchCharvarchar(2),
@ColListnvarchar(max),
@InsertListnvarchar(max),
@ValsList nvarchar(max),
@Pattern1nvarchar(20),
@Pattern2 nvarchar(20),
@Pattern3 nvarchar(20),
@Pattern4 nvarchar(20),
@Pattern5 nvarchar(20),
@Pattern6 nvarchar(20),
@Pattern7 nvarchar(20),
@Pattern8 nvarchar(20),
@Pattern9 nvarchar(20),
@Delimiter varchar(1) = Char(44),
@Xml xml,
@Pos int = 0,
@Nextpos int = 1,
@left int, @right int, @len int
;
DECLARE @TmpTable TABLE
(RowID int identity(1,1)
,Lines nvarchar(max)
);
--======= Convert CVS and place in Table =========
-- Get Raw column contents AND SELECT @RawText = CONVERT(VARCHAR(max), X.[Raw]), @FileID = [FileID]
FROM(
SELECT I.[Raw], I.[FileID]
FROM inserted AS I
JOIN [ISFD].[Download] AS D ON I.[FileID] = D.[FileID]) X
;
-- Start ConvertSET @Pattern1 = N'%[A-Z]'+@SglQuote+N' [A-Z]%';
SET @Pattern2 = N'%[A-Z]'+@SglQuote+N'[A-Z]%';
SET @Pattern3 = N'%[A-Z]'+@SglQuote+N' ';
SET @Pattern4 = N'%.[A-Z]'+@SglQuote+N'[A-Z]%';
SET @Pattern5 = N'%'+@SglQuote+N'THE GATE'+@SglQuote+N'%';
SET @Pattern6 = N'%[A-Z]'+@SglQuote+N'CE.%';
SET @RecordCount = 0;
WHILE @Nextpos < Len(@RawText) - 1
BEGIN
SET @Line = '';
SET @Nextpos = CHARINDEX(@EOL, @RawText, @Nextpos + 1);
SET @Line = SUBSTRING(@RawText, @Pos, @NextPos - @Pos);
SET @Line = REPLACE(@Line, @EOL, '');
IF @RecordCount > 0
BEGIN
-- Massage data
SET @Line = UPPER(@Line);
SET @Line = REPLACE(@Line, CHAR(44) + CHAR(44), CHAR(44) + N'NULL' + CHAR(44));
SET @len = LEN(@Line);
-- Easier to parse columns
IF PATINDEX(@Pattern1, @Line)>0
SET @Line = LEFT(@Line, PATINDEX(@Pattern1, @Line)) + @Star + RIGHT(@Line, LEN(@Line)-(PATINDEX(@Pattern1, @Line)+1))
WHILE PATINDEX(@Pattern2, @Line)>0
SET @Line = LEFT(@Line, PATINDEX(@Pattern2, @Line)) + @Star + RIGHT(@Line, LEN(@Line)-(PATINDEX(@Pattern2, @Line)+1));
IF PATINDEX(@Pattern3, @Line)>0
SET @Line = LEFT(@Line, PATINDEX(@Pattern3, @Line)) + @Star + RIGHT(@Line, LEN(@Line)-(PATINDEX(@Pattern3, @Line)+1));
IF PATINDEX(@Pattern4, @Line)>0
SET @Line = LEFT(@Line, PATINDEX(@Pattern4, @Line)+1) + @Star + RIGHT(@Line, LEN(@Line)-(PATINDEX(@Pattern4, @Line)+2));
IF PATINDEX(@Pattern5, @Line)>0
SET @Line = REPLACE(@Line, @SglQuote+N'THE GATE'+@SglQuote, @Star+N'THE GATE'+@Star);
IF PATINDEX(@Pattern6, @Line)>0
SET @Line = LEFT(@Line, PATINDEX(@Pattern6, @Line)) + @Star + RIGHT(@Line, LEN(@Line)-(PATINDEX(@Pattern6, @Line)+1));
-- All apostrophes replaced, change quotes
SET @Line = REPLACE(@Line, @DblQuote, @SglQuote);
--print @Line;
--PRINT len(@Line);
set @LineTransform = '';
SET @Pointer = 1;
-- Encapsulate comma delimited column values
while @Pointer < len(@Line)
begin
if substring(@Line, @Pointer, 1) = @Delimiter
begin
set @LineTransform = @LineTransform + @Delimiter;
set @Pointer = @Pointer + 1;
--print @LineTransform;
end
if substring(@Line, @Pointer, 1) = @SglQuote
begin
if LEN(@LineTransform) > 0
set @LineTransform = @LineTransform + substring(@Line, @Pointer, charindex(@SglQuote, @Line, @Pointer+1)-(@Pointer-1))
else
set @LineTransform = substring(@Line, @Pointer, charindex(@SglQuote, @Line, @Pointer + 1));
set @Pointer = CHARINDEX(@SglQuote, @Line, @Pointer + 1) + 1;
--print @LineTransform;
end
if substring(@Line, @Pointer, 1) <> @Delimiter and substring(@Line, @Pointer, 1) <> @SglQuote
begin
set @LineTransform = @LineTransform + @SglQuote;
--print @LineTransform;
IF charindex(@Delimiter, @Line, @Pointer) > 0
begin
set @LineTransform = @LineTransform + substring(@Line, @Pointer, charindex(@Delimiter, @Line, @Pointer)-@Pointer);
set @Pointer = charindex(@Delimiter, @Line, @Pointer);
end
ELSE
begin
set @LineTransform = @LineTransform + substring(@Line, @Pointer, LEN(@Line)-(@Pointer-1));
set @Pointer = LEN(@Line);
end
--print @LineTransform;
set @LineTransform = @LineTransform + @SglQuote;
--print @LineTransform;
end
end
END
IF @RecordCount = 0
SET @Line = REPLACE(@Line, ' ','');
IF LEN(@LineTransform)>0
BEGIN
-- Massage data back
SELECT @LineTransform = REPLACE(@LineTransform, @SglQuote + 'NULL' + @SglQuote, 'NULL');
SELECT @LineTransform = REPLACE(@LineTransform, @SglQuote, @DblQuote);
-- Replace stars last
SELECT @LineTransform = REPLACE(@LineTransform, @Star, @SglQuote);
SELECT @LineTransform = REPLACE(@LineTransform, '*', @SglQuote);
SET @Line = @LineTransform;
END
SET @Pos = @Nextpos;
--PRINT @Line;
IF @Line IS NOT NULL
BEGIN
INSERT @TmpTable ([Lines]) VALUES (@Line);
SET @RecordCount = @RecordCount + 1;
END
END -- Converted Raw in @TmpTable
--================================================
--======= Convert Lines into XML =================
BEGIN TRY
-- Create Columns
SELECT @ColList = [Lines] FROM @TmpTable WHERE RowID=1;
SET @ColList = N'[' + REPLACE(@ColList, ',', '],[') + N']';
-- Create Column Definition and dynamic create table ##row
SELECT @InsertList = [Lines] FROM @TmpTable WHERE RowID=1;
SET @InsertList = N'[ID] int identity(1,1), [' + REPLACE(@InsertList, ',', '] nvarchar(200), [') + N'] nvarchar(200)';
SET @SqlStr = N'CREATE TABLE ##row (' + @InsertList + N')';
EXEC sp_executesql @SqlStr;
-- Populate ##row with converted data
SELECT @MaxRows=MAX(RowID) FROM @TmpTable;
SET @Nextpos=2;
WHILE @Nextpos <= @MaxRows
BEGIN
BEGIN TRY
SELECT @ValsList = Lines FROM @TmpTable WHERE RowID=@Nextpos;
SET @SqlStr = N'INSERT INTO ##row (' + @ColList + N') VALUES (' + @ValsList + N')';
EXEC sp_executesql @SqlStr;
SET @Nextpos = @Nextpos + 1;
END TRY
BEGIN CATCH
SET @Nextpos = @Nextpos + 1;
SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorProcedure = ISNULL(ERROR_PROCEDURE(),''), @ErrorLine = CAST(ISNULL(ERROR_LINE(),'') AS INT);
-- Save Error
SELECT @ErrorMessage = N'Proc:' + @ErrorProcedure +
N', Line:' + CAST(@ErrorLine AS NVARCHAR) +
N', Exception when inserted row ' + CAST(@Nextpos AS nvarchar) + N', ' + @ErrorMessage;
-- Write error to ErrorLog
IF @ErrorMessage IS NOT NULL
BEGIN
EXEC [dbo].[usp_Error_I] @V_MESSAGE=@ErrorMessage;
BREAK;
END
END CATCH
END
-- Query ##row for XML
SET @SqlStr = N'SELECT @LineOut = (SELECT * FROM ##row FOR XML AUTO, TYPE, ELEMENTS XSINIL, ROOT(''rowset''))';
DECLARE @Params nvarchar(100);
SET @Params = N'@LineOut xml OUTPUT'
EXEC sp_executesql @SqlStr, @Params, @LineOut=@Xml OUTPUT;
-- Update XML column
BEGIN TRAN;
UPDATE [ISFD].Download WITH (TABLOCK)
SET [XML] = REPLACE(REPLACE(convert(nvarchar(max), Cast(@Xml AS NVARCHAR(MAX))),'<_x0023__x0023_row>','
','')
WHERE [FileID] = @FileID;
COMMIT TRAN;
END TRY
BEGIN CATCH
-- Rollback
IF @@TRANCOUNT > 0 ROLLBACK TRAN;
SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorProcedure = ERROR_PROCEDURE(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE(),@ErrorLine = ERROR_LINE();
-- Save Error
SELECT @ErrorMessage = N'Proc:' + ISNULL(@ErrorProcedure,'') +
N', Line:' + CAST(ISNULL(@ErrorLine,'') AS NVARCHAR) +
N', State:' + CAST(ISNULL(@ErrorState,'') AS NVARCHAR) +
N', Severity:' + CAST(ISNULL(@ErrorSeverity,'') AS NVARCHAR) +
N', ' + @ErrorMessage;
-- Write error to ErrorLog
IF @ErrorMessage IS NOT NULL
EXEC [dbo].[usp_Error_I] @V_MESSAGE=@ErrorMessage;
-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH
--================================================