Fun with triggers

What Happened:
1) Insert occurs - fires insert trigger (value 10)
2) Insert trigger updates record (value = 20) - fires update trigger
3) Update trigger updates record (value = 30) this does NOT fire a recursive call to the trigger
4) Update trigger writes hist record
5) Original insert trigger writes hist record
Hmmm interesting....



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[MainTable]
( [MainTableID] [int] IDENTITY(1,1) NOT NULL
, [TheValue] [int] NOT NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[MainTable_Hist]
( [MainTable_HistID] [int] IDENTITY(1,1) NOT NULL
, [Created] [datetime] NOT NULL
CONSTRAINT [DF_MainTable_Hist_Created] DEFAULT (GETDATE())
, [MainTableID] [int] NOT NULL
, [TheValue] [int] NOT NULL
) ON [PRIMARY]

GO

CREATE TRIGGER [dbo].[trgMainTable_UPD]
ON [dbo].[MainTable]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;

DECLARE @ThisValue AS INT
      , @ThisID AS INT

SELECT @ThisID = MainTableID
     , @ThisValue = TheValue
FROM inserted

IF ( @ThisValue < 100 )
  UPDATE dbo.MainTable
  SET TheValue = @ThisValue + 10
  WHERE MainTableID = @ThisID

  INSERT INTO [dbo].[MainTable_Hist] ( [MainTableID], [TheValue] )
  SELECT MainTableID, TheValue
  FROM Inserted
END
GO

CREATE TRIGGER [dbo].[trgMainTable_INS] ON [dbo].[MainTable]
AFTER INSERT AS
  BEGIN
    SET NOCOUNT ON;
    DECLARE @ThisValue AS INT
    DECLARE @ThisID AS INT

    SELECT @ThisID = MainTableID , @ThisValue = TheValue
    FROM inserted IF ( @ThisValue < 100 )

    UPDATE dbo.MainTable
    SET TheValue = @ThisValue + 10
    WHERE MainTableID = @ThisID

    INSERT INTO [MainTable_Hist] ( [MainTableID], [TheValue] )
    SELECT MainTableID, TheValue
    FROM Inserted
END
GO

INSERT INTO [MainTable] ( [TheValue] ) VALUES ( 10 )
GO

--==--==--==--==--==--
SELECT * FROM [MainTable] ORDER BY MainTableID

MainTableID TheValue
1           30 

SELECT * FROM [MainTable_Hist] ORDER BY MainTable_HistID

MainTable_HistID Created                  MainTableID  TheValue
1                2009-10-05 14:33:02.553  1            20
2                2009-10-05 14:33:02.553  1            10