Databases: fixing the legacy dirts

We were brainstorming a little bit on how to solve some of the most common issues every company faces, specially those with rapid growth jumping from small to medium business. The thing is, most of the tables and structure are, in terms of modeling, really dirty, with full of duplication, wrong indexing, crappy namings, etc.

One optimum solution would be to simply re-model the system. Wow, that is easy to say. I wish it was that easy to implement. Thinking on a simple billing table that is used for basically everything on a company, changing it would mean changing all reports, all interfaces, all behind-the-scene processes, applications, etc. Ok, too much work, in deed.

What if we could create a new model and at the same time use the legacy just pointing to this new model? Like a view or something... We could implement a new table or tables to serve the purpose of this specific legacy table, and leave a view with the same signature (names, columns, etc) that would work transparently for the rest of the legacy system.

If you are just talking about a single table, that's not actually so hard thing to do, but re-modeling and putting order to the mess usually means more than that. The problem we need to solve then is: how to create this view that would behave just like the work as if the table was there, but pointing to a better modeled environment?

Searching a bit, we found an interesting approach to that solution, which is to trick the insert/update triggers of the view:

---

-- Create a sample table A
CREATE TABLE tableNameA
(
   tableNameId int PRIMARY KEY,
   valueA  varchar(10)
)

-- Create a sample table B
CREATE TABLE tableNameB
(
   tableNameId int PRIMARY KEY,
   valueB  varchar(10)
)
GO

-- Create a view which looks to both tables A and B (join)
CREATE VIEW tableName
AS
   SELECT
       coalesce(tableNameA.tableNameId, tableNameB.tableNameId) AS tableNameId,
       tableNameA.valueA,
       tableNameB.valueB
   FROM  tableNameA
   FULL OUTER JOIN tableNameB
   ON tableNameA.tableNameId = tableNameB.tableNameId
GO

-- Create the trigger that will enable the trick to insert in both tables separately 
CREATE TRIGGER tableName_insteadOFInsert
ON tableName
INSTEAD OF INSERT
AS
   BEGIN
       SET NOCOUNT ON
      
       INSERT INTO tableNameA(tableNameId,valueA)
       SELECT tableNameId, valueA
       FROM inserted
       WHERE valueA IS NOT null
      
       INSERT INTO tableNameB(tableNameId,valueB)
       SELECT tableNameId, valueB
       FROM inserted
       WHERE valueB IS NOT null
   END
GO

-- Create the trigger that will enable the trick to update both tables separately 
CREATE TRIGGER tableName_insteadOFUpdate
ON tableName
INSTEAD OF UPDATE
AS
   BEGIN
       SET NOCOUNT ON
      
       UPDATE tableNameA
       SET valueA = ( SELECT valueA FROM inserted )
       WHERE tableNameId = ( SELECT tableNameId FROM inserted )
      
       UPDATE tableNameB
       SET valueB = ( SELECT valueB FROM inserted )
       WHERE tableNameId = ( SELECT tableNameId FROM inserted )
   END
GO

-- Done!

-- Testing...

-- Insert some data into the tables A and B thru the view 
INSERT INTO tableName(tableNameId, valueA, valueB) VALUES (1, NULL, '10')
INSERT INTO tableName(tableNameId, valueA, valueB) VALUES (2, '20', '20')
INSERT INTO tableName(tableNameId, valueA, valueB) VALUES (3, '30', NULL)
GO

-- Displaying the results on screen    
SELECT * FROM tableName
GO

-- Update some data of the tables A and B thru the view 
UPDATE tableName SET valueA=100, valueB = null WHERE tableNameId = 2
GO

-- Displaying the results on screen    
SELECT * FROM tableName
GO

-- Just clean up this mess!
DROP TABLE tableNameA
DROP TABLE tableNameB
DROP TABLE tableName
GO

-- Cool, isn't it? (EOF)



If you try to insert directly into the view without adding the InsteadOf triggers, this is the error SQL-Server will return to you

Server: Msg 4406, Level 16, State 1, Line 1
Update or insert of view or function 'tableName' failed because it contains a derived or constant field.

And here is the result of this example:

(result of the inserts)

tableNameId valueA     valueB    
----------- ---------- ----------
1           NULL       10
2           20         20
3           30         NULL

(result of the update on id=2)

tableNameId valueA     valueB    
----------- ---------- ----------
1           NULL       10
2           100        NULL
3           30         NULL

Source:
[MSDN]: INSTEAD OF INSERT Triggers
[MSDN]: INSTEAD OF UPDATE Triggers