Schema Refactoring with Views

Database triggers are generally pretty awful. Anyone who has had to deal with a database heavily laden with DML triggers knows this and avoids using them. If anyone tells me that they must use a trigger, I can always prove that there’s a better way that doesn’t bury dependencies and business logic so deeply in the database. However, there’s one special case where I find triggers helpful.

Refactoring a schema to support new work is seemingly impossible when you must also serve legacy applications that cannot be easily changed. Views provide an interesting  abstraction that can give you the flexibility you need in this case. Acting like façades over the table structures evolving beneath them, views may use instead-of triggers to translate the data back and forth between the old and new formats, making it seem to the legacy applications that the database hasn’t changed. Let me start with a simple  Address table to demonstrate the idea.

Now suppose that we need to make two changes to this table without disrupting legacy applications that depend on the Address table remaining defined as it has been for some time. First of all, to separate the subject areas of the database better, we’ve decided that we must move the Address table from the [dbo] schema into a schema named [geo]. Next, the individual [Latitude] and [Longitude] attributes must be replaced with a single column of type GEOGRAPHY to support some enhanced geographic processing functions being added for new applications. The new Address table will be created like this:

Moving the data into the new table is straightforward using the GEOGRAPHY::Point function:

After fixing up any foreign key references from the old Address table to the new one, we’re ready to drop the old table and create a new view in its place:

The new view is named exactly like the dropped Address table so SELECT queries work just as they did before:

Now let’s try to insert some data into the view:

The insert fails with the message:

Update or insert of view or function ‘dbo.Address’ failed because it contains a derived or constant field.

The problem is that the view is based on some logic for parsing out latitude and longitude parts from a GEOGRAPHY type. The view doesn’t know how to convert the individual latitude and longitude components back into a GEOGRAPHY so let’s provide an instead-of trigger to do that:

With the INSTEAD OF INSERT (IOI) trigger in place, the insert statement tried before now works. We should add INSTEAD OF UPDATE (IOU) and INSTEAD OF DELETE (IOD) triggers to the view to make sure those operations continue to work for legacy applications, too:

With those triggers in place, the following statements work as we had hoped:

In closing, I’ll admit that this pattern has some potential problems that you may need to address. If you’re using an Object-Relational Mapping (ORM) tool that dynamically inspects and validates metadata in the database, it may get confused by the use of a view where it once found a real table. Also, what the Microsoft Entity Framework (EF) refers to as Navigation Properties, representing the foreign key relationships between tables, may break using this pattern. Also, the UPDATE trigger does not allow any way to update the primary key value as currently implemented. That’s certainly possible using the [deleted] row set provided to the trigger. However, since modifying surrogate, primary keys isn’t commonly expected or allowed, I didn’t provide that more complex implementation. Lastly, you’ll find that as your evolving database design drifts further and further from what the legacy applications use, the harder it will be to maintain the views and their triggers. My Schema Refactoring Pattern, as I call it, is best to employ when you have a firm date in hand when you know the old schema can be deprecated. Triggers are still evil so you should have a solid plan when you begin the refactoring process to stop using them as soon as possible.

One thought on “Schema Refactoring with Views”

Comments are closed.