Instead of Triggers in Object-Relational (O/R) Mapping

Introduction

One of the challenges you are confronted with when storing objects to a relational table structure is how to deal with inheritance.

There are three main inheritance mapping strategies (the link in brackets shows a respective example from Wikipedia.org):

• Table per class hierarchy (Single Table Inheritance): A single table is created and for each attribute in the class hierarchy, a column is created on this table.
• Table per subclass (Class Table Inheritance): A table is created for each class. A column for each attribute of the class (not including inherited attributes) is created. A foreign key column is defined referencing the table of the parent class.
• Table per concrete class (Concrete Table Inheritance): A table is created for each class. For each attribute of the class (including the inherited ones), a column is created.

For a more detailed description, also see http://docs.jboss.org/hibernate/orm/4.1/manual/en-US/html/ch10.html.

Not all O/R mapping frameworks do however natively support mapping strategies and more than often, an O/R mapping framework is not used at all for whatever reason. It is still not unusual that developers need to manually maintain the object to relational schema mapping.

This tip shows how instead of triggers can be used to make this task easier.

Background

From a database developer's point of view, table per class hierarchy and table per concrete class have some serious disadvantages:

• Table per class hierarchy: Not Null constraints cannot effectively be used as columns for attributes that are not in the base class, all have to be declared as nullable. As for each attribute that exists in the class hierarchy in any class, a column has to be set up, the table can become quite large with many columns not populated depending on what object is stored in a given row.
• Table per concrete class: For all attributes of a parent class, the same columns (with possibly the same constraints) have to be created on each table that holds objects of the derived class. Primary key constraints over all objects in the class hierarchy is not possible as they are distributed over several tables.

Database developers therefore tend to prefer a table per subclass strategy as this strategy avoids redundant definition of columns and leads to more streamlined table schema.

From an OO developer's perspective, the table per subclass however is the most difficult to work with as it means that an object gets distributed over more than one table.

The solution presented here uses the approach to present a table per table per concrete class view on the outside (e.g. to the OO developer) but use a table per subclass on the inside. The translation between the two is taken care of by an instead of trigger. Unlike most approaches where the O/R mapping logic is fully maintained in the OO layer, this approach moves some of it to the database.

Using the Code

Instead of triggers are executed instead of the triggering action as described per below:

INSTEAD OF triggers are executed instead of the triggering action. These triggers are executed after the inserted and deleted tables reflecting the changes to the base table are created, but before any other actions are taken. They are executed before any constraints, so can perform preprocessing that supplements the constraint actions.

(See http://technet.microsoft.com/en-us/library/aa214491%28v=sql.80%29.aspx)

They may also be defined on views to propagate table operations that would modify data in more than one underlying table. This technique will serve as the base.

To demonstrate this, we will assume a class hierarchy as per below which should be mapped to a relational Table schema.

To map these, we will first set up the below tables for Person and Employee which implement the table per subclass approach.

CREATE TABLE tblPerson
   (
    ID          int PRIMARY KEY,
    Name        varchar(10),
   )
GO
CREATE TABLE tblEmployee
   (
    ID                 int PRIMARY KEY,
    Department       varchar(10),
    CONSTRAINT FKEmpPer FOREIGN KEY (ID)
    REFERENCES tblPerson (ID) ON DELETE CASCADE ON UPDATE CASCADE
   )
GO

Based on these tables, we then set up the views which implement the table per concrete class approach.

CREATE VIEW Person AS
SELECT ID, Name
FROM tblPerson
GO

CREATE VIEW Employee AS
SELECT P.ID as ID, P.Name, E.Department
FROM tblPerson P
JOIN tblEmployee E ON P.ID = E.ID
GO

What is missing are the instead of triggers to propagate modifications from the views to the underlying tables which is the translation between the table per concrete class and table per subclass approach.

CREATE TRIGGER tiu_io_Employee ON Employee
INSTEAD OF INSERT, UPDATE
AS
BEGIN
    MERGE tblPerson AS t
    USING (SELECT ID, Name from inserted) AS s (ID, Name)
    ON (t.ID = s.ID)
    WHEN MATCHED THEN 
        UPDATE SET Name = s.Name
    WHEN NOT MATCHED THEN    
        INSERT (ID, Name)
        VALUES (s.ID, s.Name);

    MERGE tblEmployee AS t
    USING (SELECT ID, Department from inserted) AS s (ID, Department)
    ON (t.ID = s.ID)
    WHEN MATCHED THEN 
        UPDATE SET Department = s.Department
    WHEN NOT MATCHED THEN    
        INSERT (ID, Department)
        VALUES (s.ID, s.Department);

END
GO

CREATE TRIGGER td_io_Employee ON Employee
INSTEAD OF DELETE
AS
BEGIN
    DELETE tblPerson
    WHERE ID IN (SELECT ID FROM deleted)
END
GO

We are now ready to run some modifications to demonstrate how the link between views and tables work.

INSERT Employee (ID, Name, Department)
VALUES (1, 'Peter', 'IT')
GO
INSERT Employee (ID, Name, Department)
VALUES (2, 'Susan', 'Finance')
GO
INSERT Person (ID, Name)
VALUES (3, 'Guy')
GO

SELECT * FROM Person
SELECT * FROM Employee
GO

-- Output --------------------------------------------

ID          Name
----------- ----------
1           Peter
2           Susan
3           Guy

(3 row(s) affected)

ID          Name       Department
----------- ---------- ----------
1           Peter      IT
2           Susan      Finance

(2 row(s) affected)

-- End Output -----------------------------------------

UPDATE Employee 
SET Name = 'Sue', Department = 'Accounting'
WHERE ID = 2
GO

SELECT * FROM Person
SELECT * FROM Employee
GO

-- Output --------------------------------------------

ID          Name
----------- ----------
1           Peter
2           Sue
3           Guy

(3 row(s) affected)

ID          Name       Department
----------- ---------- ----------
1           Peter      IT
2           Sue        Accounting

(2 row(s) affected)

-- End Output -----------------------------------------

DELETE Employee
GO

SELECT * FROM Person
SELECT * FROM Employee
GO

-- Output --------------------------------------------

ID          Name
----------- ----------
3           Guy

(1 row(s) affected)

ID          Name       Department
----------- ---------- ----------

(0 row(s) affected)

-- End Output -----------------------------------------

DELETE Person
GO

SELECT * FROM Person
SELECT * FROM Employee
GO

-- Output --------------------------------------------

ID          Name
----------- ----------

(0 row(s) affected)

ID          Name       Department
----------- ---------- ----------

(0 row(s) affected)

-- End Output -----------------------------------------

As the examples above show, the trigger processes correctly inserts, updates, deletes done to the views. These views can be used as the interface to an OO layer that manipulates data in the database through these views.

Points of Interest

The O/R mapping approach presented here is quite straightforward but requires some coding effort as for each derived class a view and a set of triggers need to be set up.

Using views as the interface to an OO layer do however offer some advantages: The views can be designed to correspond to the class hierarchy and the names of views and columns in the view can be chosen to exactly match the class and attribute names in the OO layer. This would allow the usage of more simple and less complex frameworks to produce the SQL to be sent to the database. Furthermore, the database table schema underlying the views can be designed relatively independently from the OO model making it relatively easy to construct an OO layer on top of an existing database table schema.

History

• 1^st March, 2014: Initial version