Post reply

Help on Trigger

  • November 2, 2011 at 3:28 am

    #259434

    Hi Guys,

    Need your help.

    I have A table called [Period] and i have insert,update,deleted trigger on it.

    In a trigger i m inserting the records into [PeriodLog] Table where i have Primary key on Id Column and identity on it.

    When i am trying to insert into a table its throwing me Primary key Violation error.

    Please help me in fixing this issue.

    Thanks in advance.

    Regards,

    Vijay

  • November 2, 2011 at 3:39 am

    #1402024

    Is your trigger attempting to insert into the identity column?

    Please post the DDL for the tables involved and the triggers.

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • November 2, 2011 at 3:52 am

    #1402034

    Hi,

    No its not trying to insert into identity column.

    below is the Table structure.

    CREATE TABLE PeriodLog

    (

    ID INT PRIMARY KEY IDENTITY(1,1),

    Message VARCHAR(10),

    Operation CHAR (1)

    )

    GO

    CREATE TRIGGER [dbo].[TR_PeriodID_IUD] ON [dbo].[Period]

    FOR INSERT, UPDATE, DELETE NOT FOR REPLICATION

    AS

    DECLARE @EditDate Datetime

    SET NOCOUNT ON

    SELECT @Editdate = GETDATE()

    INSERT dbo.PeriodLog (Message,Operation )

    SELECT Message ,'D'

    FROM deleted

    INSERT dbo.PeriodLog (Message,Operation )

    SELECT Message ,'I'

    FROM deleted

    SET NOCOUNT OFF

    --------------------------------

    Even if i try to insert the record directly in to PeriodLog table(Trying to execute the below code) , its throwing the same error.

    INSERT dbo.PeriodLog (Message,Operation )

    SELECT Message ,'I'

    Adding: We have some 20000000+ records in PeriodLog Table.

    Regards,

    Vijay

  • November 2, 2011 at 6:08 am

    #1402092

    Hi Guys,

    I got the problem.

    Issue: DBCC CHECKIDENT ( 'dbo.PVAPeriodIDLog')

    Checking identity information: current identity value '29141918', current column value '29142774'.

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Solution: ( 'dbo.PVAPeriodIDLog', RESEED )

    But my concern is , in what case this issue occers?

    Please help me in understanding this.

    Thanks,

    Vijay

  • November 2, 2011 at 3:07 pm

    #1402430

    Vijay,

    Since your current identity value is less than the current column value, there is a good possibility that the trigger is trying to insert a value that already exists for the primary key. Try reseeding the table to match the current column value.

    I hope I am clear enough!

    - Rex

  • November 2, 2011 at 11:53 pm

    #1402500

    Hi,

    I reseed the table and the issue is fixed. But i want to know how this problem occurred.

    Thanks,

    Vijay

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply