September 28, 2012 at 4:24 am
I 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.
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)
ERROR :
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.
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 problem
September 28, 2012 at 4:49 am
This was removed by the editor as SPAM
September 28, 2012 at 4:55 am
I 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 🙁
September 28, 2012 at 5:08 am
Then maybe start by reading up on triggers
http://www.sqlteam.com/article/an-introduction-to-triggers-part-i
http://www.sqlteam.com/article/an-introduction-to-triggers-part-ii
http://msdn.microsoft.com/en-us/library/ms189799.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 28, 2012 at 5:18 am
Thanks alot for the links related to triggers ,As I am serching for some useful stuff related to trigger but didnt got anything related to that
October 9, 2012 at 11:08 pm
MY Trigger CODE:
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
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 repli
October 10, 2012 at 4:40 am
This was removed by the editor as SPAM
October 10, 2012 at 8:35 am
As 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.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply