Tuesday, August 5, 2008

Updating Static Cursors

Ever been happy because you normalized some tables, wrote a query that was way faster than it was before, only to find out later you now have to re-architect a portion of your application? Yep, your fancy join is way faster, but that simple grid interface that used to be a live cursor is now static, and users can't just update it directly anymore.

In version 7.1 (2004) support for triggers on views was added to Advantage. I'd like to take a quick look at how this feature allows you to update static cursors.

Consider the following grid interface to a bug tracking table:



Lets say you modified your schema and added a users table with additional information about each user; location and department. You also modified tables that reference users to store the userID now instead of the username. As a result, instead of a simple:


SELECT bugid, description, owner FROM bugs;

Your query now looks something like this:


SELECT b.bugid, b.description, u.name as "owner" 
FROM bugs b, users u
WHERE b.ownerID = u.id;

All is well, except users can no longer directly update cursor rows, as the result set consists of a join of two tables.

An elegant solution is to first create a view using the query above:


CREATE VIEW BugPreview AS
SELECT b.bugid, b.description, u.name
FROM bugs b, users u
WHERE b.ownerID = u.id


Next, create an INSTEAD OF UPDATE trigger on the view, handling the update yourself using your knowledge of the relationship between the two tables:


CREATE TRIGGER UpdateBugPreview
  ON BugPreview
  INSTEAD OF 
  UPDATE 
  BEGIN 
  declare @new cursor as select * from __new;

  open @new;
  fetch @new;

  try
    -- update fields in the bugs table
    update bugs set description = @new.description,
      ownerID = (select id from users where name = @new.name)
    where bugid = @new.bugid;
  finally
    close @new;
  end try;
  END;


Cursors using the BugPreview view can now be updated directly. Restoring live cursor functionality to a dataset that is really composed of two physical tables.

This technique allows you to normalize data and increase query performance while still maintaining simple "single table" front-end interfaces to your datasets. Adding INSTEAD OF DELETE and INSTEAD OF INSERT triggers in a similar fashion can provide DELETE and INSERT functionality on this static cursor as well.

Note there is some additional maintenance if you use this technique. You now need to be aware of these triggers if you modify the list of columns displayed by the view. Any changes to the column list will require some slight modifications to the triggers. This example also requires a unique user name, and doesn't handle the name not existing yet (you could catch this and insert a record, however).

In my next post I will include a screencast showing an implementation of this solution from start to finish.

1 comment:

Carl Rempel said...

This advice will definitely get put to good use! Thanks JD.

Post a Comment