A common misconception with SQL Server views is that only indexed views can provide update capability. Actually, any view that is schema-bound can be enabled for update by simply using an “INSTEAD OF” trigger.  Although an indexed view may also be setup with a trigger, it is not required.  The view does not need to obey the constraints of an indexed view other than being schema-bound since the trigger writes to the base tables.

There are a few scenarios where this is useful. One is in the case of tables that utilize a master-secondary design pattern.  In many scenarios, there may be central objects that manifest themselves in different forms requiring different attributes. Often the central objects need to be accessed singularly for their core attributes so splitting up the objects into multiple tables makes running queries that need objects of both types together awkward and forces the use of UNION queries. The other complexity involved with separating objects based on type into separate tables is that they often share relationships to common child objects. To support the different types of interfaces for different object types, multiple views can be configured.

One example of this is a name and address system I worked on a long time ago related for a non-profit organization. It turns out that donors may be organizations or individuals or married couples. Different attributes are associated with each. To further complicate matters, a donor could be a member – that is a member could donate to a donor. The member might also be married and the organization need to track the household unit associated with married members.

The solution was to create a generic entity object and then satellite objects for individual, household, or organization. This allowed the name to be linked to the outside systems without having to maintain multiple relationships and then allow the relationships between the entities to be maintained in another table. In this example an organization could donate as well as an individual which would show up on a donation report, whereas the organization attributes were not muddled up with individual attributes.

Below is the database diagram representing the common entity approach for name/address. Note that in addition to the master-secondary relationships, there are relationships between the objects such as the organizational contact for an organization entity or the spouse for a household entity. From the diagram, it is not clear as to the direction of the 1-to-1 relationships, but it is is from Entity – that is a row on Org, Household, or Person must first have a row in Entity with the Entity Id.

In order to support interfaces that need to insert/update different types of entities, views can be created to support the specific type of entity. The view is coupled with an INSTEAD OF trigger so that adding data to the view will execute the insert tables. Only the main Entity table uses the identity property. The other tables primary keys are all based on the entity table key. The below triggers provide a couple of interfaces to adding new entities. The first inserts a singular person and generate both the entity and the person table.

The second trigger could add a new organization with the assumption that the contact person was already added as a person entity.

The third trigger shows how cascading can be used for even more elaborate scenarios including inserting into other views. This example provides the columns for the contact person entity as well as the organization entity rather than the person entity key and inserted into the person table as well as the organization and main entity tables.  It leverages the prior two triggers by inserting directly into the views.

CREATE VIEW v_PersonEntity WITH SCHEMABINDING
AS SELECT FirstName, LastName, Suffix, WorkPhone, CellPhone, HomePhone, PrimaryPhone
FROM dbo.Person p
GO

CREATE VIEW v_OrgEntity WITH SCHEMABINDING
AS SELECT ContactPersonId, FederalId, EntityName
FROM dbo.Org o
INNER JOIN dbo.Entity e
    ON o.EntityId = e.EntityId
GO

CREATE VIEW v_OrgEntity_WithContact WITH SCHEMABINDING
AS SELECT FederalId, EntityName, FirstName, LastName, WorkPhone
FROM dbo.Org o
INNER JOIN dbo.Entity e
    ON o.EntityId = e.EntityId
INNER JOIN dbo.Person p
    ON p.EntityId = o.ContactPersonId
GO

CREATE TRIGGER v_Person_ITrig ON dbo.v_PersonEntity
   INSTEAD OF INSERT
AS BEGIN
    IF (SELECT COUNT(*) FROM inserted) > 1 BEGIN
        RAISERROR (‘View only supports adding 1 person at a time’,16,1)
    END
    DECLARE @EntityID INT
    — Create the main entity row
    INSERT INTO dbo.Entity (EntityName, EntityType, MainPhoneNumber)
    SELECT
        i.FirstName + ‘ ‘ + i.LastName, ‘P’
        ,CASE
            WHEN PrimaryPhone = ‘H’ THEN HomePhone
            WHEN PrimaryPhone = ‘C’ THEN CellPhone
            WHEN PrimaryPhone = ‘W’ THEN WorkPhone
        END
    FROM inserted i

    — Create the secondary person row
    SELECT @EntityId = SCOPE_IDENTITY()
    INSERT into dbo.Person (EntityId, FirstName, LastName, Suffix,
    WorkPhone, CellPhone, HomePhone, PrimaryPhone)
    SELECT @EntityId, FirstName, LastName, Suffix,
    WorkPhone, CellPhone, HomePhone, PrimaryPhone
    FROM inserted
END
GO

CREATE TRIGGER v_OrgEntity_ITrig on dbo.v_OrgEntity
   INSTEAD OF INSERT
AS BEGIN
    IF (SELECT COUNT(*) FROM inserted) > 1 BEGIN
        RAISERROR (‘View only supports adding 1 person at a time’,16,1)
    END
    DECLARE @EntityID INT
   
    — Create the main entity row
    INSERT INTO dbo.Entity (EntityName, EntityType)
    SELECT EntityName, ‘O’
    FROM inserted i

    — Create secondary org row
    SELECT @EntityId = SCOPE_IDENTITY()
    INSERT into dbo.Org
    (EntityId, ContactPersonId, FederalId)
    SELECT @EntityId, ContactPersonId, FederalId
    FROM inserted

END
GO

CREATE TRIGGER v_OrgEntity_WithContact_ITrig on dbo.v_OrgEntity_WithContact
   INSTEAD OF INSERT
AS BEGIN
    IF (SELECT COUNT(*) FROM inserted) > 1 BEGIN
        RAISERROR (‘View only supports adding 1 person at a time’,16,1)
    END
    DECLARE @EntityID INT
   
    — Could use the view to create the person and entity rows, but
    — then there is a challenge with getting the identity inserted
    — since SCOPE_IDENTITY is nulled out after the last add to the
    — person table
    INSERT INTO dbo.Entity (EntityName, EntityType, MainPhoneNumber)
    SELECT
        i.FirstName + ‘ ‘ + i.LastName, ‘P’, WorkPhone
    FROM inserted i

    — Create the secondary person row
    SELECT @EntityId = SCOPE_IDENTITY()
    INSERT into dbo.Person (EntityId, FirstName, LastName,
    WorkPhone, PrimaryPhone)
    SELECT @EntityId, FirstName, LastName,
    WorkPhone, ‘W’
    FROM inserted

    — Create the organization and entity row using the org view
    INSERT into dbo.v_OrgEntity
    (ContactPersonId, FederalId, EntityName)
    SELECT @EntityId, FederalId, EntityName
    FROM inserted
END
GO

Below shows how all tables are affected by insert to the single Org with contact view:

These triggers illustrate the use of insert, however update and delete actions may also be utilized in INSTEAD OF triggers. Below is an example that does both an insert and update for one of the tables that I utilize in my universal problem resolution framework utilized in my dissertation. Also, in the prior example, the triggers only work for 1 row insertion at a time because of the need to capture the identity associated with the new entity due to the fact that triggers operate against the entire row set rather than individually on each row. In the below example, a temporary table and a pre-defined sequencing approach is utilized to support multiple simultaneous insertions. The coalesce for the update causes omitted items to remain as they were before.

ALTER TRIGGER [Data].[view_Problem_Item_ITrig]
   ON  [Data].[view_Problem_Item]
   INSTEAD OF INSERT
AS
BEGIN
    — SET NOCOUNT ON added to prevent extra result sets from
    — interfering with SELECT statements.
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION
        –SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
        DECLARE @EntityTypeId MONEY = Data.udf_GetItemTypeId(‘Data.Entity’)
        DECLARE @NextEntityId MONEY — = Data.udf_GetNextItemId(@EntityTypeId)

        DECLARE @ProblemTypeId MONEY = Data.udf_GetItemTypeId(‘Data.Problem’)
        DECLARE @NextProblemId MONEY — = Data.udf_GetNextItemId(@ProblemTypeId)
        SELECT @NextProblemId = MAX(ItemId) FROM Data.Item  WITH (HOLDLOCK, UPDLOCK)

        SET XACT_ABORT ON

        SELECT  
            (CONVERT(MONEY,ROW_NUMBER() OVER (ORDER BY ItemName)) / 10000.00) — – .0001
             + @NextProblemId AS ItemId,
            ActivationTaskDetailId,
            DefineActionTreeId,
            SolveActionTreeId,
            ProblemDefinitionState,
            ProblemSolutionState,
            CurrentStepNumber,
            ItemName,
            TypeId,
            VersionNumber,
            IsSystemItem,
            ItemPath,
            AutoGenerated,
            RootProblemId,
            BranchProblemId,
            IsRoot,
            ActivationValue,
            ProblemCheckSum,
            BranchStepNumber,
            OutcomeReason
        INTO #Problem FROM inserted
        WHERE (ItemId IS NULL OR ItemId = 0)

        INSERT Data.Item  WITH (ROWLOCK)
            (
            ItemId,
            ItemName,
            TypeId,
            VersionNumber,
            IsSystemItem,
            ItemPath,
            AutoGenerated,
            RootProblemId
            )
        SELECT
            ItemId,
            ItemName,
            COALESCE(TypeId,@ProblemTypeId),
            COALESCE(VersionNumber,1.0),
            COALESCE(IsSystemItem, 0),
            COALESCE(ItemPath, ItemName),
            COALESCE(AutoGenerated,0),
            RootProblemId
        FROM #Problem

        INSERT Data.Problem  WITH (ROWLOCK)
        (
            ProblemId,
            ActivationTaskDetailId,
            DefineActionTreeId,
            SolveActionTreeId,
            ProblemDefinitionState,
            ProblemSolutionState,
            CurrentStepNumber,
            BranchProblemId,
            IsRoot,
            ActivationValue,
            ProblemCheckSum,
            BranchStepNumber,
            OutcomeReason
        )
        SELECT
            ItemId,
            ActivationTaskDetailId,
            DefineActionTreeId,
            SolveActionTreeId,
            ProblemDefinitionState,
            ProblemSolutionState,
            COALESCE(CurrentStepNumber,0.0),
            BranchProblemId,
            COALESCE(IsRoot,0),
            ActivationValue,
            ProblemCheckSum,
            BranchStepNumber,
            OutcomeReason
        FROM #Problem

        UPDATE item  WITH (ROWLOCK)
        SET ItemName = COALESCE(i.ItemName, item.ItemName),
            TypeId = COALESCE(i.TypeId, item.TypeId),
            VersionNumber = COALESCE(i.VersionNumber,item.VersionNumber),
            IsSystemItem = COALESCE(i.IsSystemItem,item.IsSystemItem),
            ItemPath = COALESCE(i.ItemPath,item.ItemName),
            AutoGenerated = COALESCE(i.AutoGenerated, item.AutoGenerated),
            RootProblemId = COALESCE(i.RootProblemId, item.RootProblemId)
        FROM inserted i
        INNER JOIN Data.Item item 
            ON i.ItemId = item.ItemId 
        WHERE (i.ItemId IS NOT NULL AND i.ItemId <> 0)

        UPDATE p WITH (ROWLOCK)
        SET ActivationTaskDetailId = COALESCE(i.ActivationTaskDetailId, P.ActivationTaskDetailId),
            DefineActionTreeId = COALESCE(i.DefineActionTreeId, p.DefineActionTreeId),
            SolveActionTreeId = COALESCE(i.SolveActionTreeId, p.SolveActionTreeId),
            ProblemDefinitionState = COALESCE(i.ProblemDefinitionState, p.ProblemDefinitionState),
            ProblemSolutionState = COALESCE(i.ProblemSolutionState, p.ProblemSolutionState),
            CurrentStepNumber = COALESCE(i.CurrentStepNumber, p.CurrentStepNumber),
            BranchProblemId = COALESCE(i.BranchProblemId, p.BranchProblemId),
            ActivationValue = COALESCE(i.ActivationValue, p.ActivationValue),
            ProblemCheckSum = COALESCE(i.ProblemCheckSum, p.ProblemCheckSum),
            OutcomeReason = COALESCE(i.OutcomeReason, p.OutcomeReason),
            Complexity = COALESCE(
        FROM inserted i
        INNER JOIN Data.Problem p
            ON i.ItemId = p.ProblemId 
        WHERE (i.ItemId IS NOT NULL AND i.ItemId <> 0)

        DROP TABLE #Problem
       
        COMMIT
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK
        EXEC dbo.usp_RethrowError
    END CATCH

END