ViewState 101

TRULY Understanding ViewState - Infinities Loop

ViewState is a very misunderstood animal. I would like to help put an end to the madness by attempting to explain exactly how the ViewState mechanism works, from beginning to end, and from many different use cases, such as declared controls vs. dynamic controls.

I wish I had written this

Five Simple Database Design Errors You Should Avoid

Clear, concise and on the money.

This article covers:

(1) Common Lookup Tables
(2) Check Constraint conundrum
(3) Entity-Attribute-Value Table
(4) Application Encroachments into DB design
(5) Misusing Data values as Data Elements

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