Today I am sharing an approach from Maurice which uses views and triggers on views to present a consistent interface to consumers. This technique works differently from the one we presented yesterday using views, table value functions and stored procedures to maintain a static interface.
Instead, Maurice exposes the views directly to consumers. Updates and inserts are propagated to one or more tables based upon instead of triggers created on the views, thus making them updatable. This technique allows for the view to change by adding new columns without requiring the user interface to specify each value. Indeed, in the trigger you can handle default values for new columns not supplied by older code.
Because this design works specifically on the data level without using stored procedures, it integrates more easily with tools such as nHibernate or Entity Framework. Without modifying your data bindings on these ORM tools a modified view with new columns will continue to work with older code. It will also work with newer code that implements newly added columns. Both the old and new code continue to work concurrently, which is the principle of Open/Closed at the root level.
I’m not sure how this approach works from a performance perspective. But, it certainly does allow extension in a unique way.
Following is the detailed submission from Maurice. If you would like to add your thoughts feel free to submit it here online, or drop an email to btaylor@sswug.org.
Cheers,
Ben
Maurice Writes:
My approach is to make an interface that an application can use in a invariant manner. The interface is not static, but it may be used statically.
I make a pseudo view that basically is just a mean to present rows of parameter to an instead of trigger that does processing. Instead of trigger allows inserts to any view.. There is no storage of any data, but the instead of trigger sees the row inserted form pseudo INSERTED table and can do processing from it.
As you know an Insert statement let you specify the columns that you want to insert to. This is why the application can use in a invariant manner. The interface is not static, because you can add more columns (which are new parameters placeholder), but it still may be used as before, not supplying the new columns. If a new part of the application is aware of the new parameters and can supply it, why not.
Let’s say actually all the application does insert to the vfiew vCallBusineesProcessA using Insert into vCallBusinessProcessA (p1, p2) values (1, ‘hi’). The contract is to actually specify p1 and p2 to call businessProcessA.
If you add a column (let’s say p3) to the view and modify the trigger to take into account that p3 is specified or not, you have to change nothing to application that use it. So actual inserts still work. But now you created an opening open to use the new feature of businessProcessA by supplying p3, and only if needed. For example Insert into vCallBusinessProcessA (p1, p2, p3) values (1, ‘hi’, ‘newStuff’). From there trigger can handle the difference any way it want. This is hidden to the callers who not know who to supply p3, but great for new one who knows.
You have now potentially 2 contracts onto the same interface. Note that the same can be done inside the trigger to call another process that handle the new parameter using the same approach. For example, for a given value of parameter P2, you can in the trigger find out the parameter P3 and call the new business process AB. For new callers who already live with the new parameter p3 and then are able to supply p3, why not use directly the new interface?. For old callers that should have to deal with p3, but doesn’t know, the old interface still works. If you do this you have two possibilities for new callers: continue to use the old interface and supply p3 when you know it, or use directly the new interface.
Here is example code:
-- original contract **********************************
drop view vCallBusinessProcessA
go
create view vCallBusinessProcessA as
select cast (null as int) as p1,
cast (null as nvarchar(max)) as p2
go
create trigger callBusinessProcessA on vCallBusinessProcessA
instead of insert
as
Begin
If @@rowcount = 0
set nocount on
-- original contract
Select 'Processing param p1 and p2 for BusinessProcessA ...........', * From Inserted
End
go
-- SOME DEMO
insert into vCallBusinessProcessA (p1,p2) values (1, 'first')
go
-- ******************* evolution of original interface ********************
-- original contract IS STILL VALID but now you can also
-- use the same interface to supply a new param p3
-- ******************* evolution of original interface ********************
drop view vCallBusinessProcessA
-- flush previous trigger version
go
Create view vCallBusinessProcessA as
select cast (null as int) as p1,
cast (null as nvarchar(max)) as p2,
cast(null as int) as p3 –- new param
go
-- new interface for direct calls of BusinessProcessAB with p1,p2,p3
-- implement a view to handle the
-- new contract p1,p2,p3 so you can also use directly vCallBusinessProcessAB
-- A alternate way for new callers who work with the new parameter
-- and know how to supply it to get new features.
drop view vCallBusinessProcessAB
go
create view vCallBusinessProcessAB as
select cast (null as int) as p1,
cast (null as nvarchar(max)) as p2,
cast(null as int) as p3
go
create trigger callBusinessProcessAB on vCallBusinessProcessAB
instead of insert
as
Begin
If @@rowcount = 0 return
set nocount on
Select 'Processing param p1 p2 and p3 for BusinessProcessAB ...........', * From Inserted
End
go
-- Adapt processing to keep the same interface for actual
-- processes that call BusinessProcessA through it,
-- Also opening the same interface to new param p3, which
-- will remain not mandatory for existing callers
-- because p3 may be computed in CallBusinessProcessA for existing callers.
create trigger callBusinessProcessA on vCallBusinessProcessA
instead of insert
as
Begin
If @@rowcount = 0 return
set nocount on
-- original contract, but extend feature when p2 <> hello or p3 is unspecified
If exists(Select * From Inserted Where p2 <> 'hello' And p3 is null)
Select 'Processing param p1 and p2 for BusinessProcessA the old way,
when p3 is not needed when p2 <> hello value .......', *
From Inserted
Where p2 <> 'hello' And p3 is null
-- process CallBusinessProcessAB only when p2 value = 'Hello'
-- or p3 is directly supplied
-- if p3 isn't supplied (for existing callers using only p1 and p2),
-- p3 value is computed
If exists(Select * From Inserted Where p2 = 'hello' Or p3 is not null)
Insert into vCallBusinessProcessAB (p1, p2, p3)
Select p1, p2, isnull(p3, case when p1 between 100 and 200 then p1*1000 else p1 End)
From Inserted Where p2 = 'hello' or p3 is not null
End
Go
-- SOME DEMO
insert into vCallBusinessProcessA (p1, p2) Values (1, 'hi')
-- call from existing callers
insert into vCallBusinessProcessA (p1, p2) Values (1, 'hello')
-- deduce p3 for existing callers
insert into vCallBusinessProcessA (p1, p2, p3) Values (1, 'hehe', 10000)
-- call directly with p3 for new code
insert into vCallBusinessProcessAB (p1, p2, p3) Values (1, 'hehe', 10000)
-- use directly the new interface