Replace view with a trigger

I have a view which I was hoping to create an index over which is failing because I would need to index a computed column (this fails with error code 2729 "...cannot be used in an index or statistics or as a partition key because it is non-deterministic". I am hoping to be able to replace the view with a trigger or set of triggers which would maintain an indexed table to . The view is quite simple.

Given a table:

CREATE TABLE SourceData (
  ItemId int NOT NULL,
  KeyId int NOT NULL,
  Value varchar(MAX) NULL
)

I created the view:

CREATE VIEW DateView WITH SCHEMABINDING
AS
SELECT CONVERT(DATETIME, Value) As KeyDate,
ItemId FROM dbo.SourceData WHERE KeyId=123

I then try to 开发者_如何学运维create an index on the view:

CREATE NONCLUSTERED INDEX IX_DateView ON dbo.DateView (
  [KeyDate] ASC
)
INCLUDE ( [ItemId]) 

which fails.

I haven't really used triggers before, but I understand I should be able to use them to detect and filter any changes to the underlying SourceData table and apply them to a new table to replace the DateView

---

You can materialize the KeyDate as a real datetime column, and maintain it using a trigger (on insert and update)

Add the column as a real datetime

alter table SourceData add KeyDate datetime

Create a trigger to maintain it

create trigger CRU_SourceData
on SourceData
after insert, update
as
begin
if update(Value)
    update SourceData
    set KeyDate = Convert(datetime,inserted.Value)
    from inserted
    -- assuming itemID uniquely identifies the record
    where inserted.ItemId=SourceData.ItemID
end

Create the view

CREATE VIEW DateView WITH SCHEMABINDING
AS
SELECT KeyDate, ItemId FROM dbo.SourceData
WHERE KeyId=123

Now this will work

CREATE NONCLUSTERED INDEX IX_DateView ON dbo.DateView (
  [KeyDate] ASC
)
INCLUDE ( [ItemId])