Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

I need to implement trigger on INSERTION and insert the same record to another table but avalue is showing null :( Expand / Collapse
Author
Message
Posted Friday, September 28, 2012 4:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 7:39 AM
Points: 46, 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
Post #1365750
Posted Friday, September 28, 2012 4:49 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:18 AM
Points: 3,925, Visits: 5,112
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”
Post #1365764
Posted Friday, September 28, 2012 4:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 7:39 AM
Points: 46, 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
Post #1365768
Posted Friday, September 28, 2012 5:08 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:41 AM
Points: 42,827, Visits: 35,957
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 2008, MVP
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

Post #1365776
Posted Friday, September 28, 2012 5:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 7:39 AM
Points: 46, 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

Post #1365782
Posted Tuesday, October 9, 2012 11:08 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 7:39 AM
Points: 46, 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
Post #1370675
Posted Wednesday, October 10, 2012 4:40 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:18 AM
Points: 3,925, Visits: 5,112
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”
Post #1370829
Posted Wednesday, October 10, 2012 8:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:32 PM
Points: 13,302, Visits: 12,168
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 Moden's 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)
Post #1370972
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse