none
Trigger cannot insert data (cannot support operations that write to the log file)

    Question

  • I am having the most frustrating problem with a trigger, and am hoping someone can shed light on the problem.  I quote the trigger in full below, with all try/catch constructs and debug trace statements, most of them commented out.

    What I am trying to achieve is to split a coded string inserted or updated into a primary table into a secondary "normalised" data table.  The problem is that the attempt to insert data into the secondary table fails.  With the error traps in place, the result is:
     The transaction ended in the trigger. The batch has been aborted.
    If I disable the error-trapping, the result is:
     The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
    I have traced things up to the INSERT statement, and all is well up to that point.  My test case is a direct update of the primary table: UPDATE EndowmentDetail SET QualityMeasures = '1=0.5;430=Insitu 5284t' WHERE EndowmentDetailKey=<id value>.  Obviously, a simple test case can be set up by inserting that key into the table.

    If I disable the insert statements, the trigger correctly iterates through the multiple entities in the coded string I supply, but the attempt to commit the transaction then fails - probably reasonable, since there is nothing to commit.


    The table with the trigger has the following minimal definition:

    CREATE TABLE [dbo].[EndowmentDetail](
     [EndowmentDetailKey] [int] IDENTITY(1,1) NOT NULL,
     [QualityMeasures] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
     CONSTRAINT [PK_EndowmentDetail] PRIMARY KEY CLUSTERED
    (
     [EndowmentDetailKey] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]

    The table to be updated has the definition:

    CREATE TABLE [dbo].[EndowmentDetailQualMeasures](
     [EndowmentDetailKey] [int] NOT NULL,
     [QMKey] [int] NOT NULL,
     [StringValue] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
     [NumericValue] [float] NULL,
     CONSTRAINT [PK_EndowmentDetailQualMeasures] PRIMARY KEY CLUSTERED
    (
     [EndowmentDetailKey] ASC,
     [QMKey] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]

    The trigger is on the first table.  Basically, what it attempts to do is:
    - get a cursor from the Inserted table
    - for each row of the cursor, get the key and coded value (1=a;2=b;3=c and so on)
    - delete any existing rows for the primary record key
    - split the coded value on the separators
    - insert a record into the secondary table for each key/value pair, and if the value is numeric, add in a cast of the value as a float

    The trigger appears below.

    ALTER TRIGGER dbo.tr_EndowmentDetailSplit
     ON EndowmentDetail
     AFTER INSERT,UPDATE
    AS
    BEGIN
     -- SET NOCOUNT ON added to prevent extra result sets from
     -- interfering with SELECT statements.
     SET NOCOUNT ON;

     BEGIN TRANSACTION

     BEGIN TRY
      BEGIN TRY DEALLOCATE curInserted END TRY BEGIN CATCH END CATCH

      -- variables for splitting apart a quality-measure string
      DECLARE @Separator1 char(1), @Separator2 char(1), @List varchar(max)
      SET @Separator1 = ';'
      SET @Separator2 = '='
      DECLARE @Key varchar(10), @KeyValue varchar(MAX), @Value varchar(MAX), @Pos1 int, @Pos2 int

      -- variables for handling the records which have been modified
      DECLARE @EndowmentDetailKey int, @QualityMeasures varchar(max)
      DECLARE @QMkey varchar(5), @QMnum float, @QMvalue varchar(max)
      DECLARE curInserted CURSOR FOR
       SELECT EndowmentDetailKey, QualityMeasures
       FROM Inserted
       WHERE ISNULL(QualityMeasures, '') <> ''
     
      --PRINT 'Loop 1'
      OPEN curInserted

      FETCH NEXT FROM curInserted INTO @EndowmentDetailKey, @QualityMeasures
      --PRINT @QualityMeasures
      BEGIN TRY
       DELETE FROM dbo.EndowmentDetailQualMeasures WHERE EndowmentDetailKey = @EndowmentDetailKey
      END TRY
      BEGIN CATCH
      END CATCH

      WHILE @@FETCH_STATUS = 0
      BEGIN
       PRINT 'Loop 2'
       SET @List = LTRIM(RTRIM(@QualityMeasures))+ @Separator1
       SET @Pos1 = CHARINDEX(@Separator1, @List, 1)
       IF REPLACE(@List, @Separator1, '') <> ''
       BEGIN
        -- split out one key/value pair at a time
        WHILE @Pos1 > 0
        BEGIN
         SET @KeyValue = LTRIM(RTRIM(LEFT(@List, @Pos1 - 1)))
         IF LTRIM(RTRIM(@KeyValue)) <> ''
         BEGIN
          -- split the key from the value
          SET @Pos2 = CHARINDEX(@Separator2, @KeyValue, 1)
          IF @Pos2 > 0
          BEGIN
           SET @Key = LEFT(@KeyValue, @Pos2 - 1)
           SET @Value = RIGHT(@KeyValue, LEN(@KeyValue) - @Pos2)
    print 'Endowment detail key = ' + cast(@EndowmentDetailKey as varchar)
    print 'QM key = ' + cast(@Key as varchar)
    print 'QM value = ' + cast(@Value as varchar)
    print 'Value is numeric? ' + cast(ISNUMERIC(@Value) as varchar)
           IF ISNUMERIC(@Value) = 1
           BEGIN
            PRINT 'String only'
            INSERT INTO dbo.EndowmentDetailQualMeasures (EndowmentDetailKey, QMKey, StringValue)
             VALUES (@EndowmentDetailKey, CAST(@KeyValue AS int), CAST(@Value AS varchar(60)))
           END
           ELSE
           BEGIN
            PRINT 'String and float'
            INSERT INTO dbo.EndowmentDetailQualMeasures (EndowmentDetailKey, QMKey, StringValue, NumericValue)
             VALUES (@EndowmentDetailKey, CAST(@KeyValue AS int), CAST(@Value AS varchar(60)), CAST(@Value AS float))
           END
          END
         END
         SET @List = RIGHT(@List, LEN(@List) - @Pos1)
         SET @Pos1 = CHARINDEX(@Separator1, @List, 1)
        END
       END

       FETCH NEXT FROM curInserted INTO @EndowmentDetailKey, @QualityMeasures
      END
    --  print 'close curInserted'

      CLOSE curInserted
      DEALLOCATE curInserted
    --  print 'committing'
      COMMIT TRANSACTION

     END TRY
     BEGIN CATCH
    print 'rollback'
      ROLLBACK TRANSACTION  
     END CATCH
    END

    • Moved by John C GordonMicrosoft employee Wednesday, June 17, 2009 9:06 PM Makes more sense in the context of the engine behavior. (From:SQL Server Data Access)
    Wednesday, June 17, 2009 7:57 AM

All replies