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

Answer 1

Can´t you do a set based operations by directly querying the inserted tables ? As far as I can see that, there are no statements that cannot be done without a cursor.

-Jens

---

Jens K. Suessmeyer http://blogs.msdn.com/Jenss