How to set up an updatable view with a join in Firebird?

Question

I've got three tables, let's call them GRANDPARENT, PARENT and CHILD. PARENT has a FK column to GRANDPARENT's PK, and CHILD has a FK column to PARENT's PK. So far, so good.

Now I want to set up a view that contains all the information in CHILD, plus the PK from GRANDPARENT. So:

CREATE VIEW CHILD_VIEW
(
  ID,
  PARENT_ID,
  OTHER_STUFF,
  GRANDPARENT_ID
)
AS
SELECT 
  C.ID,
  C.PARENT_ID,
  C.OTHER_STUFF,
  C.GRANDPARENT_ID
FROM CHILD C
join PARENT P on P.ID = C.PARENT_ID;

Not too hard. But here's the tricky part: I want to be able to INSERT or UPDATE to this view, and have all relevant data get written to the CHILD table, and the GRANDPARENT_ID value, if any, should be ignored.

I've done some Googling around and apparently it should be possible to set up an updatable view like this "by using triggers," but it doesn't explain anywhere what I'm supposed to do with the triggers to achieve this effect. I think I know more or less how to handle the INSERT case, but what about the UPDATE case? UPDATE statements have WHERE clauses, and can contain or not contain any arbitrary column(s) in the table.

For example, how do I use a trigger to transform something like update CHILD_VIEW set (blah blah blah) where ID = 5 into update CHILD set (blah blah blah excluding GRANDPARENT_ID) where ID = 5?

Answer 1

Well, you do it by using triggers, as you already found out :)

It really is that simple, you can use all the features available in triggers (ie OLD and NEW contexts)... if you're using Firebird 2.1 or newer then you can use the UPDATE OR INSERT statement, or you can use the INSERTING and UPDATING context variables to detect is it update or insert in multi-action trigger. Or, of course, you can write separate ON UPDATE and ON INSERT triggers...

So your trigger might look something like this

CREATE TRIGGER CHILD_VIEW_Save FOR CHILD_VIEW
ACTIVE BEFORE INSERT OR UPDATE POSITION 10
AS
BEGIN
  IF(NEW.ID IS NULL)THEN NEW.ID = GEN_ID(GEN_Child, 1);
  UPDATE OR INSERT INTO CHILD (ID, PARENT_ID, OTHER_STUFF, GRANDPARENT_ID)
            VALUES(NEW.ID, NEW.PARENT_ID, NEW.OTHER_STUFF, NEW.GRANDPARENT_ID);
END