InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 22:21:12 GMT20http://www.sqlservercentral.com/Forums/Topic1365750-392-1.aspxAs Stewart said that will only work for single row updates but it also will fail if the table is empty. The values you are getting into your variables are NOT the ones that are being inserted, they are values already present in the table.Wed, 10 Oct 2012 08:35:23 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1365750-392-1.aspxThe trigger as written will only be able to work with one row at a time.there would be a lot of data lost if a large set of data were to be inserted in one operation.As I advised in my previous post, read up about using the "inserted" virtual table and change your code to cater for using data sets.I have created a mock-up situation, using your given details (not all the columns, just some critical ones...[code="sql"]CREATE TABLE dbo.theResult (theResultNo INT IDENTITY (1,1) PRIMARY KEY,Message NVARCHAR(56) NOT NULL)GoCREATE 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))GOCREATE TRIGGER CaptureOutput ON dbo.theResultFOR INSERTASBEGINBEGIN 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 PointsEND TRYBEGIN 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)ROLLBACKEND CATCH--COMMITENDGOINSERT 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')GOSELECT * FROM dbo.theResultSELECT * FROM dbo.OutputDetailGODROP TABLE dbo.OutputDetailDROP TABLE dbo.theResult[/code]Wed, 10 Oct 2012 04:40:53 GMTStewart "Arturius" Campbellhttp://www.sqlservercentral.com/Forums/Topic1365750-392-1.aspxMY Trigger CODE:[code="sql"]ALTER TRIGGER [dbo].[TRG_MessageInBox] ON [dbo].[messagein] FOR INSERTASBEGINDECLARE@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 tablesSelect 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=msgtypeFROM messagein mINNER JOIN VisboxDetails_Tb vONm.receiver =v.GSMnowhere 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 valuesDECLARE @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 tableDECLARE @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 ;[b][/b]SET @result =CHARINDEX('N',@STRING)SET @len =LEN(@STRING);IF(@len > 0 AND @result > 0)BEGINSelect @visId as answerIDBEGIN 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 pidSELECT 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 tableINSERT 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 @msgChunksSelect TOP 1 * from testIn ORDER BY testIn_Id DESCENDENDELSEIF(@len > 0 AND @result = 0)BEGINSelect @visId as answerIDDECLARE @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 DESCENDEND[/code]The trigger is working great for me.I need to check the characters in string.If it doesn't have them all then goto else if condition and any other suggestions to improve the trigger code .Kindly check and repliTue, 09 Oct 2012 23:08:58 GMTmaida_rhhttp://www.sqlservercentral.com/Forums/Topic1365750-392-1.aspxThanks alot for the links related to triggers ,As I am serching for some useful stuff related to trigger but didnt got anything related to thatFri, 28 Sep 2012 05:18:21 GMTmaida_rhhttp://www.sqlservercentral.com/Forums/Topic1365750-392-1.aspxThen maybe start by reading up on triggers[url]http://www.sqlteam.com/article/an-introduction-to-triggers-part-i[/url][url]http://www.sqlteam.com/article/an-introduction-to-triggers-part-ii[/url][url]http://msdn.microsoft.com/en-us/library/ms189799.aspx[/url]Fri, 28 Sep 2012 05:08:02 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1365750-392-1.aspxI am new to triggers and also to T-SQL,just did simple queries with joins in sql server.Ii dont have any idea of that :(Fri, 28 Sep 2012 04:55:02 GMTmaida_rhhttp://www.sqlservercentral.com/Forums/Topic1365750-392-1.aspxFirst. have you considered using the [url=http://msdn.microsoft.com/en-us/library/ms191300(v=sql.105).aspx]inserted[/url] virtual table in your trigger?using this value will be of benefit if you are processing numerous rows at a time.Fri, 28 Sep 2012 04:49:47 GMTStewart "Arturius" Campbellhttp://www.sqlservercentral.com/Forums/Topic1365750-392-1.aspxI need to implement trigger on INSERTION and insert the same record to another table but a column value is showing null.I check the query individually it return a value and trigger code also execute when i remove check i.e.NOT NULL from fk_visbox_Id.I tryed and check again and again but cant find any solution.[code="sql"]ALTER TRIGGER [dbo].[tgr_msgOut] ON [dbo].[msgCounterOut_Tb]AFTER INSERTASDECLARE@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_TbSelect @visId=vis_Id from VisboxDetails_Tb where GSMno = @gsmnoISelect 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=@gsmnoIInsert 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)[/code]ERROR :[code="sql"]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.[/code]It didnt get value of gsmno (i.e. Select TOP 1 @gsmnoI=sender from msgCounterOut_Tb)If I remove chk from column (i.e. fk_vis_Id) then triger insert data in both tables successfully.Any solution this problemFri, 28 Sep 2012 04:24:16 GMTmaida_rh