ALTER TRIGGER [dbo].[tgr_msgOut] 
ON [dbo].[msgCounterOut_Tb]
AFTER INSERT
AS
DECLARE
@visId int,
@gsmnoI nvarchar(30),
@senderI nvarchar(30),
@receiverI nvarchar(30),
@msgI nvarchar(30),
@senttimeI nvarchar(30),
@receivertimeI nvarchar(30),
@referenceI nvarchar(30),
@statusI nvarchar(30),
@operatorI nvarchar(30),
@msgtypeI nvarchar(30)

Select TOP 1 @gsmnoI=sender from msgCounterOut_Tb

Select @visId=vis_Id from VisboxDetails_Tb where GSMno = @gsmnoI

Select TOP 1 @gsmnoI=sender,@senderI=sender,@receiverI=receiver,@msgI=msg,@senttimeI=sender,@receivertimeI=receivedtime,@referenceI=reference,@statusI=status,@operatorI=operator,@msgtypeI=msgtype from msgCounterOut_Tb where sender=@gsmnoI

Insert into testTb(fk_visbox_Id,sender,receiver,msg,senttime,receivedtime,reference,status,operator,msgtype) 
VALUES (@visId,@senderI,@receiverI,@msgI,@senttimeI,@receivertimeI,@referenceI,@statusI,@operatorI,@msgtypeI)


(Code to split @msg from above to insert its details to another table named details_TB)

SQL Exception:

Cannot insert the value NULL into column 'fk_visbox_Id', table 'PakUjalaDb.dbo.testTb'; column does not allow nulls. INSERT fails. The statement has been terminated.

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ALTER TRIGGER [dbo].[TRG_MessageInBox]
  ON  [dbo].[messagein]
   FOR INSERT
AS
BEGIN
DECLARE
@visId int,
@gsmnoV nvarchar(30),
@gsmnoO nvarchar(30),
@msg nvarchar(160),
@gsmnot nvarchar(30),

@sendert nvarchar(30),
@receivert nvarchar(30),
@msgt nvarchar(30),
@senttimet nvarchar(30),
@receivertimet nvarchar(30),
@referencet nvarchar(30),
@statust nvarchar(30),
@operatort nvarchar(30),
@msgtypet nvarchar(30),
@voltage nvarchar(30),
@north nvarchar(30),
@east nvarchar(30),
@mtime nvarchar(30),
@height nvarchar(30),
@temperature nvarchar(30),
@luman nvarchar(30),
@comprasser nvarchar(30)

-------------Select TOp 1 gsmno from msgOUT'

SET @gsmnoO =(Select TOP 1 receiver from messagein ORDER BY id DESC)

------------ Select TOp 1 gsmno vis_Id and msg values from respected tables

Select  TOP 1 @visId=v.vis_Id,@gsmnoV=v.GSMno,@gsmnot=m.receiver,@msg =m.msg,
@sendert =sender,@receivert=receiver,@senttimet=senttime,@receivertimet=receivedtime,@referencet=reference,@operatort=operator,@msgtypet=msgtype
FROM  messagein m
INNER JOIN 
VisboxDetails_Tb v
ON
m.receiver =v.GSMno
where receiver=@gsmnoO  ORDER BY id DESC

--select @visId as ID ,@gsmnoV,@gsmnoO,@msg,@gsmnot,@sendert,@receivert as receno,@msgt,@senttimet,@receivertimet,@referencet,@statust,@operatort,@msgtypet,@voltage,@north,@east ,@mtime,@height,@temperature,@luman,@comprasser 

------------- Creating virtual table for storing splited values

DECLARE @msgChunks TABLE(
fvis_Id int,
--gsmnoI nvarchar(30),
senderI nvarchar(30),
receiverI nvarchar(30),
msgI nvarchar(160),
senttimeI nvarchar(100),
receivertimeI nvarchar(100),
referenceI nvarchar(100),
operatorI nvarchar(100),
msgtypeI nvarchar(160),
voltage nvarchar(30),
north nvarchar(30),
east nvarchar(30),
mtime nvarchar(30),
height nvarchar(30),
temperature nvarchar(30),
luman nvarchar(30),
comprasser nvarchar(30)
);

--------------- Splitting string and storing it ot a temporary table

DECLARE @CHARACTERS TABLE (CHARS VARCHAR(2))
INSERT INTO @CHARACTERS VALUES
('N'), ('E'), ('M'), ('H'), ('T'), ('V'), ('L'), ('C'), ('XX');
DECLARE @STRING VARCHAR(500);
--SET @STRING=@msg+'XX';
SET @STRING='2449.555N06704.2855EM0701H071T44.098V11.764L0.372C1'+'XX';
DECLARE @len int,
@result int ;
SET @result =CHARINDEX('N',@STRING)

SET @len =LEN(@STRING);

IF(@len > 0 AND @result > 0)

BEGIN

Select @visId as answerID

BEGIN
	WITH CTE AS (
		SELECT  CHARINDEX(CHARS,@STRING,1) x
			   , CAST(REVERSE(LEFT(REVERSE(LEFT(@STRING,
				CHARINDEX(CHARS,@STRING,1)-1)), PATINDEX('%[^0-9,.]%',
				REVERSE(LEFT(@STRING,CHARINDEX(CHARS,@STRING,1)-1)) + 'Z')-1))
				AS VARCHAR(50)) AS RESULT
		FROM        @CHARACTERS 
	)
----------------Insert data into virtual table 
	Insert Into @msgChunks (fvis_Id,senderI,receiverI,msgI,senttimeI,receivertimeI,referenceI,operatorI,msgtypeI,north,east,mtime,height,temperature,voltage,luman,comprasser)
	Select @visId,@sendert,@receivert,@msg,@senttimet,@receivertimet,@referencet,@operatort,@msgtypet,MAX([1]) Col1,MAX([2]) Col2,MAX([3]) Col3,MAX([4]) Col4,MAX([5]) Col5,MAX([6]) col6,MAX([7]) col7,MAX([8]) col8
	From (SELECT 1 pid,ROW_NUMBER() OVER(Order by x) pivotcol,* 
				FROM CTE 
				WHERE LEN(RESULT)>0) s
		pivot(max(RESULT) 
			FOR pivotcol in ([1],[2],[3],[4],[5],[6],[7],[8])) pvt
	group by pid

SELECT fvis_Id,senderI,receiverI,msgI,senttimeI,receivertimeI,referenceI,operatorI,msgtypeI,north,east,mtime,height,temperature,voltage,luman,comprasser FROM @msgChunks 

----------------Insert the data to TestIn which is a main table

INSERT INTO testIn(fk_visbox_Id,sender,receiver,msg,senttime,receivedtime,reference,operator,msgtype,mnorth,meast,mtime,mheight,mtemperature,mvoltage,mluman,mcompraser)
SELECT fvis_Id,senderI,receiverI,msgI,senttimeI,receivertimeI,referenceI,operatorI,msgtypeI,north,east,mtime,height,temperature,voltage,luman,comprasser FROM @msgChunks

Select TOP 1 * from testIn ORDER BY testIn_Id DESC

END
END

ELSE

IF(@len > 0 AND @result = 0)
BEGIN
Select @visId as answerID

DECLARE 
@msgerror nvarchar(160)

SET @msgerror ='Status not sent by OZEKI!Request again'

INSERT INTO testIn(fk_visbox_Id,sender,receiver,msg,senttime,receivedtime,reference,operator,msgtype,mnorth,meast,mtime,mheight,mtemperature,mvoltage,mluman,mcompraser)
VALUES(@visId,@sendert,@receivert,@msgerror,@senttimet,@receivertimet,@referencet,@operatort,@msgtypet,'null','null','null','null','null','null','null','null')

Select TOP 1 * from testIn ORDER BY testIn_Id DESC

END
END

CREATE TABLE dbo.theResult (
theResultNo INT IDENTITY (1,1) PRIMARY KEY,
Message NVARCHAR(56) NOT NULL)
Go
CREATE TABLE dbo.OutputDetail (
theResultNo INT FOREIGN KEY REFERENCES dbo.theResult(theResultNo),
Message NVARCHAR(56) NOT NULL,
north nvarchar(30),
east nvarchar(30),
mtime nvarchar(30),
height nvarchar(30),
temperature nvarchar(30),
voltage nvarchar(30),
luman nvarchar(30),
comprasser nvarchar(30))
GO
CREATE TRIGGER CaptureOutput 
ON dbo.theResult
FOR INSERT
AS
BEGIN
BEGIN TRY
	WITH Characters (CharindexValues) AS
		(SELECT * FROM (VALUES ('N'), ('E'), ('M'), ('H'), ('T'), ('V'), ('L'), ('C'), ('XX')) CiV(Val)),
	Points AS 
		(SELECT theResultNo, CharindexValues, CHARINDEX(CharindexValues, Message+'XX') Point, ROW_NUMBER() OVER (ORDER BY CHARINDEX(CharindexValues, Message+'XX')) [rows]
		FROM Characters
		CROSS JOIN inserted),
	StringOut AS 
		(select a.theResultNo, a.CharindexValues, a.Point, a.rows, CASE WHEN a.Point > 0 THEN SUBSTRING(Message, CASE WHEN b.Point IS NOT NULL THEN b.Point ELSE 0 END+1, CASE WHEN a.Point IS NOT NULL THEN a.Point ELSE 1 END - CASE WHEN b.Point IS NOT NULL THEN b.Point+1 ELSE 1 END) ELSE NULL END AS StringOut
		from inserted
		JOIN Points a on a.theResultNo = inserted.theResultNo
		left join Points b on b.rows = a.rows - 1),
	PivotOut AS
		(select theResultNo, [1] AS North, [2] AS East, [3] AS mtime, [4] AS Height, [5] AS Temp, [6] AS Voltage, [7] AS Luman, [8] AS Comprasser
		From (SELECT theResultNo, rows, StringOut
					FROM StringOut 
					WHERE LEN(StringOut)>0) s
			pivot(max(StringOut) 
				FOR rows in ([1],[2],[3],[4],[5],[6],[7],[8])) pvt
		group by theResultNo,[1],[2],[3],[4],[5],[6],[7],[8])
	INSERT dbo.OutputDetail
	SELECT inserted.theResultNo, CASE WHEN CHARINDEX('N',Message) > 0 THEN Message ELSE 'Status not sent by OZEKI!Request again' END,
			CASE WHEN CHARINDEX('N',Message) > 0 THEN North ELSE NULL END, 
			CASE WHEN CHARINDEX('N',Message) > 0 THEN East ELSE NULL END, 
			CASE WHEN CHARINDEX('N',Message) > 0 THEN mtime ELSE NULL END, 
			CASE WHEN CHARINDEX('N',Message) > 0 THEN Height ELSE NULL END, 
			CASE WHEN CHARINDEX('N',Message) > 0 THEN Temp ELSE NULL END, 
			CASE WHEN CHARINDEX('N',Message) > 0 THEN Voltage ELSE NULL END, 
			CASE WHEN CHARINDEX('N',Message) > 0 THEN Luman ELSE NULL END, 
			CASE WHEN CHARINDEX('N',Message) > 0 THEN Comprasser ELSE NULL END
	FROM inserted
	JOIN PivotOut ON PivotOut.theResultNo = inserted.theResultNo
--	select * from Points
END TRY
BEGIN CATCH
    DECLARE @ErrLine INT = ERROR_LINE(), @ErrNum INT = ERROR_NUMBER(), @ErrMsg NVARCHAR(1024) = ERROR_MESSAGE(), @ErrSev INT = ERROR_SEVERITY(), @ErrState INT = ERROR_STATE()
RAISERROR('Something went wrong at line %d, err number %d, with message %s, severity %d, State %d',16,1, @ErrLine, @ErrNum, @ErrMsg, @ErrSev, @ErrState)
ROLLBACK
END CATCH
--COMMIT
END
GO
INSERT theResult (Message)
VALUES ('2449.555N06704.2855EM0701H071T44.098V11.764L0.372C1')
INSERT theResult (Message)
VALUES ('2459.545N06514.2455E1M0731H072T45.198V10.754L0.362C1')
INSERT theResult (Message)
VALUES ('100155.55E2455E1M0731H072T45')
GO
SELECT * FROM dbo.theResult
SELECT * FROM dbo.OutputDetail
GO

DROP TABLE dbo.OutputDetail
DROP TABLE dbo.theResult