How to set up an updated view with Firebird attachment?

Question

I have three tables, call them GRANDPARENT , PARENT and CHILD . PARENT has a column FK before GRANDPARENT PK, and CHILD has a column FK before PARENT PK. So far so good.

Now I want to customize a view containing all the information in CHILD , plus 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 complicated. But here is the hard part: I want to be able to INSERT or UPDATE for this view, and all relevant data will be written to the CHILD table, and the GRANDPARENT_ID value, if any, should be ignored.

I did some Googling, and apparently it should be possible to set up an updatable view like this with “triggers”, but it doesn't explain anywhere what I have to do with 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 have WHERE clauses and may or may not contain any arbitrary columns in the table.

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

Answer 1

Well, you do this using triggers, as you already know :)

This is true, you can use all the functions available in triggers (i.e. OLD and NEW ) ... if you use Firebird 2.1 or later, you can use UPDATE or INSERT , or you can use INSERTING and UPDATING to determine is updated or inserted into a trigger with several actions. 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