SQL Server 2000 Odd Insert Trigger Behavior -- Recursive Like

Question

Hello,

I have a trigger in a table in SQL Server 2000, this is the trigger:

ALTER Trigger [dbo].[Set_Asignado_State] ON [dbo].[Tables] 
FOR INSERT AS 
BEGIN     
    DECLARE @area varchar(1)
    SELECT @area = Table_Area_ID FROM inserted

    IF (@area = 'L')
    BEGIN
     INSERT INTO Table_History
     SELECT  (SELECT TOP 1 Table_Area_Id AS Table_Area_Id FROM inserted) AS Table_Area_Id,
       (SELECT SUBSTRING( CAST( YEAR( GETDATE() ) AS VARCHAR), 3, 2) ) AS Table_Year, 
       (SELECT TOP 1 Table_Seq AS Table_Seq FROM inserted) AS Table_Seq, 
       (SELECT TOP 1 ID FROM Table_Status WHERE Description = 'Asignado') AS Status, 
       '' AS Responsible, 
       (SELECT TOP 1 OrigDept FROM inserted) AS User_Responsible, 
       GETDATE() AS [DateTime],
       'None' AS Comments
     FROM Tables
     WHERE Tables.Table_Area_Id = (SELECT TOP 1 Table_Area_Id AS Table_Area_Id FROM inserted) AND
        Tables.Table_Year = (SELECT SUBSTRING(CAST(YEAR(GETDATE()) AS VARCHAR), 3, 2) )

     IF @@ERROR <> 0
     BEGIN
      DECLARE @errorMsg NVARCHAR(256)
      SET @errorMsg = @@ERROR; 

      PRINT 'Error Inserting in Table_History'

     END
    END
END

Now, when they insert a record in "Tables", the trigger is called but it keeps inserting many, many records on Table_History.

I've talked with my peers and there seems that there is nothing wrong! =S Any clues about this? Am I doing something wrong?

Thanks in advance =)

Answer 1

Is there another trigger on the Table_History table thats inserting into Tables?

Answer 2

Triggers are set based, so doing
IF (@area = 'L') will only look at the first record from the inserted table. Try adding the check as part of your WHERE clause:

WHERE Tables.Table_Area_Id = (SELECT TOP 1 Table_Area_Id AS Table_Area_Id FROM inserted) AND
              Tables.Table_Year = (SELECT SUBSTRING(CAST(YEAR(GETDATE()) AS VARCHAR), 3, 2) )  
 AND SUBSTRING(Tables.Table_Area_Id, 1, 1) = 'L'

Are you sure that the main Select statement does only return the one row? I have noticed that you have used the Top(1) qualifier extensively. If it returns more than one row then there will be more than one row inserted into your history table.

Perhaps as a blind shot, try putting in a Top(1) for the outermost select:

    INSERT INTO Table_History
    SELECT TOP(1) (SELECT TOP 1 ...

Otherwise it looks as though you have some recursion going on in the trigger. I cannot see it, but if you want, you can turn off recursive triggering for the whole database, since it is a database option.