Monday, December 31, 2007

Using the INSTEAD OF Trigger in SQL Server 2005

I, like probably a lot of people, am going to have to do a bit of DB refactoring in order to be able to take advantage of Linq to SQL and/or the ADO.NET Entity Framework.  While the framework is certainly flexible, there's plenty of databases out there that were designed without an O/R layer in mind, and there's definitely spots where you can pigeonhole yourself if you didn't take O/R into account during the initial design.

So, one great way to simplify your model is to create views that abstract the complicated underlying model.  Views work great for just about any read-only operation, however, when you get into the inserting and deleting is when you hit some heavy limitations.  From the SQL Server 2005 BOL:

You can modify the data of an underlying base table through a view, as long as the following conditions are true:

  • Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.
  • The columns being modified in the view must directly reference the underlying data in the table columns. The columns cannot be derived in any other way, such as through the following:
    • An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, and VARP.
    • A computation. The column cannot be computed from an expression that uses other columns. Columns that are formed by using the set operators UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a computation and are also not updatable.
  • The columns being modified are not affected by GROUP BY, HAVING, or DISTINCT clauses.
  • TOP is not used anywhere in the select_statement of the view together with the WITH CHECK OPTION clause.

The first one is obviously the biggest issue (at least for me).  It's often likely that you've got more than one table that aggregates into a single entity.

So, one option you have then is the INSTEAD OF trigger.  Apparently this feature has been around since SQL 2000, but in my ignorance, haven't heard of it until recently.  The INSTEAD OF triggers can be placed on any view or table to replace the standard action of the INSERT statement. 

As with all triggers, this is one of those "Use with great caution" features, as it definitely convolutes the normal execution path.  That said, it's pretty powerful, and gets you around most of the updatable view limitations.

Now, I'm going to show a simple example.  I'm perfectly aware that this example easy to do in the ADO.NET Entity Framework.  This example doesn't represent my own database issues, and why I am investigating going this route.  Just trying to keep it simple for this example.  If you'd like to hear about my database issues, go ahead and comment, and I'd love to discuss and see if there's better options out there.

So, here's the schema:

db schema

Now, in my application, I want to represent a single entity called Employee, that is an aggregation of the Person attributes and the Employee attributes.  So, what I can do then is create a view.

CREATE VIEW [dbo].[Employees]
AS
SELECT     dbo.Employee.EmployeeID, dbo.Employee.Salary, dbo.Person.FirstName, dbo.Person.LastName, dbo.Person.PersonID
FROM         dbo.Employee INNER JOIN
                      dbo.Person ON dbo.Employee.PersonID = dbo.Person.PersonID

Now, this view is going to work great, except when I want to update the data in the tables.  Now, I can write some insert code using the new Linq To SQL functionality:

MyDatabaseClassesDataContext ctx = new MyDatabaseClassesDataContext();
 
Employee emp = new Employee();
emp.FirstName = "Jim";
emp.LastName = "Fiorato";
emp.Salary = 400000;
 
ctx.Employees.InsertOnSubmit(emp);
 
ctx.SubmitChanges();

However, that will throw an exception like the following: 

System.Data.SqlClient.SqlException: View or function 'dbo.Employees' is not updatable because the modification affects multiple base tables.

So, now what I can do is create the INSTEAD OF trigger on the view.  I'll just do an example of the insert trigger.  That trigger will look like this (note this doesn't support multiple items in the inserted rowset at all):

CREATE TRIGGER [dbo].[TR_INSERT_Employees]
   ON [dbo].[Employees]
   INSTEAD OF INSERT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
 
    DECLARE @PersonID AS UNIQUEIDENTIFIER
    DECLARE @EmployeeID AS UNIQUEIDENTIFIER
 
    SET @PersonID = NEWID();
    SET @EmployeeID = NEWID();
 
    -- Person Table
    INSERT INTO Person (PersonID, FirstName, LastName)
    SELECT @PersonID, FirstName, LastName FROM inserted;
 
    -- Employee Table
    INSERT INTO Employee (EmployeeID, PersonID, Salary)
    SELECT @EmployeeID, @PersonID, Salary FROM inserted;
 
END

This all makes me wonder if we're just pushing the impedance mismatch work-around further into the database from the code.  Is that a good or bad thing?  It's likely bad, but just how bad?  I can't tell yet. 

I think people usually feel better about what's in their database, vs. what's in their code, because relational databases are something you can wrap you head around, where boundaries are clear, and people make quick and mostly accurate assumptions about what is going on.  Whereas, in you C# code, you'll often find it hard to grasp it all, because it could be doing so many things, in so many different ways.

This makes me feel better about doing it this way, but still, I wish my data model was more simple, and more clean, but it's a huge undertaking to change a model.  Migration scripts, backward compatibility issues, etc.  I think I need to try this direction right now.  I'll let you know if it doesn't work for me.

3 comments:

Unknown said...

thanks sir It is a very gud example

Pravesh Singh said...

Very informative post. Its really helpful for me and beginner too. Check out this link too its also having a nice post related to this post over the internet which also explained very well...

Triggers in SQL Server

Darren Fitzgibbon said...

Late response to this post but, this won't work for multi-row inserts, as all the rows in that insert will receive the same ids, probably breaking your primary key constraints to boot.

In short this is very very bad. You will need to loop over all the rows in the inserted table to achieve what you want.