July 27, 2012 at 3:10 am
Hello All,
I am trying to add the records from one table to other table. But getting the below error.
Warning: The join order has been enforced because a local join hint is used.
Msg 50000, Level 16, State 1, Procedure TR_SessionClientCode_I, Line 36
Invalid attempt to enter duplicate KeySessions,KeyClientCodeType,ClientCodeValue in SessionClientCode
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
Query
INSERT INTO sessionclientcode
(
KeySessionClientCode,
KeySessions,
KeyClientCode,
UpdOperation,
UpdDate,
KeyClientCodeType,
ClientCodeValue
)
SELECT
KeySessionClientCode,
KeySessions,
KeyClientCode,
UpdOperation,
UpdDate,
KeyClientCodeType,
ClientCodeValue
FROM temp_sessionclientcode
I did check the existence of duplicate record but couldnt find anything.
SELECT sc.KeySessions,sc.KeyClientCodeType,sc.ClientCodeValue FROM sessionclientcode sC join temp_sessionclientcode TC
ON SC.KeySessions = TC.KeySessions
and sc.KeyClientCodeType = tc.KeyClientCodeType
and sc.ClientCodeValue = tc.ClientCodeValue
GROUP BY sc.KeySessions,sc.KeyClientCodeType,sc.ClientCodeValue
HAVING COUNT(*) > 1
Also check the Insert trigger listed below.
if (Update(UpdOperation) or Update(KeySessions) or Update(KeyClientCodeType) or Update(ClientCodeValue)) and Exists(
SELECT * FROM inserted i INNER LOOP JOIN SessionClientCode x ON
(i.KeySessions=x.KeySessions and i.KeyClientCodeType=x.KeyClientCodeType and i.ClientCodeValue=x.ClientCodeValue)
WHERE i.updoperation IN (0, 1) AND x.updoperation IN (0, 1) GROUP BY x.KeySessions,x.KeyClientCodeType,x.ClientCodeValue
HAVING COUNT(*) > 1)
BEGIN
RAISERROR( 'Invalid attempt to enter duplicate KeySessions,KeyClientCodeType,ClientCodeValue in SessionClientCode', 16, -1 )
ROLLBACK TRAN
RETURN
END/*
Can anyone let me know how can i find the duplicate records?
Is it possible to find the record using temp table in RAISERROR part?
July 27, 2012 at 5:01 am
Thanks everyone.
Issue has been resolved.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply