SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


I need to implement trigger on INSERTION and insert the same record to another table but avalue is...


I need to implement trigger on INSERTION and insert the same record to another table but avalue is showing null :(

Author
Message
maida_rh
maida_rh
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 111
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
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8887 Visits: 7281
First. have you considered using the inserted virtual table in your trigger?
using this value will be of benefit if you are processing numerous rows at a time.

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
maida_rh
maida_rh
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 111
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 Sad
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87227 Visits: 45272
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

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


maida_rh
maida_rh
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 111
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
maida_rh
maida_rh
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 111
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
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8887 Visits: 7281
The 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...
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



____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26064 Visits: 17531
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search