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
)
Insights on Authentic Intelligence - Bob Leithiser, Ph.D. 