locked
Invalid attempt to enter duplicate records. The transaction ended in the trigger. The batch has been aborted. RRS feed

  • Question

  • 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?

    Friday, July 27, 2012 9:10 AM

Answers

  • You can add this part in the trigger's code:

    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
    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	
    
    RAISERROR( 'Invalid attempt to enter duplicate KeySessions,KeyClientCodeType,ClientCodeValue in SessionClientCode', 16, -1 )
    	ROLLBACK TRAN
    	RETURN
    END

    So, when you'll re-try your operation in SSMS, you should see the rows that caused the error message in a trigger.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by Chickoo79 Friday, July 27, 2012 10:59 AM
    Friday, July 27, 2012 10:12 AM

All replies

  • You can add this part in the trigger's code:

    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
    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	
    
    RAISERROR( 'Invalid attempt to enter duplicate KeySessions,KeyClientCodeType,ClientCodeValue in SessionClientCode', 16, -1 )
    	ROLLBACK TRAN
    	RETURN
    END

    So, when you'll re-try your operation in SSMS, you should see the rows that caused the error message in a trigger.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by Chickoo79 Friday, July 27, 2012 10:59 AM
    Friday, July 27, 2012 10:12 AM
  • Thanks for the quick update.

    I will do the changes and revert back.

    Thanks a lot for your help.

    Friday, July 27, 2012 10:14 AM
  • Thanks man.

    Issue got resolved.

    • Marked as answer by Chickoo79 Friday, July 27, 2012 11:00 AM
    • Unmarked as answer by Naomi N Friday, July 27, 2012 11:01 AM
    Friday, July 27, 2012 10:59 AM