SQL: The PostgreSQL Way - PostgreSQL: Up and Running - page 122

RETURN NEW ;
END IF ;
IF ( TG_OP = 'UPDATE' ) THEN
IF
ROW ( OLD . fact_type_id , OLD . tract_id , OLD . yr , OLD . val , OLD . perc ) !=
ROW ( NEW . fact_type_id , NEW . tract_id , NEW . yr , NEW . val , NEW . perc )
THEN
UPDATE census . facts AS f
SET
tract_id = NEW . tract_id ,
yr = NEW . yr ,
fact_type_id = NEW . fact_type_id ,
val = NEW . val ,
perc = NEW . perc
WHERE
f . tract_id = OLD . tract_id AND
f . yr = OLD . yr AND
f . fact_type_id = OLD . fact_type_id ;
RETURN NEW ;
ELSE
RETURN NULL ;
END IF ;
END IF ;
END ;
$$
LANGUAGE plpgsql VOLATILE ;
Handle deletes. Delete only the record with matching keys in the OLD record.
Handle inserts.
Handle the updates. Use the OLD record to determine which records to update
with the NEW record data.
Update rows only if at least one of the columns from facts table has changed.
Next, we bind the trigger function to the view, as shown in Example 7-5 .
Example 7-5. Bind trigger function to view
CREATE TRIGGER census . trig_01_vw_facts_ins_upd_del
INSTEAD OF INSERT OR UPDATE OR DELETE ON census . vw_facts
FOR EACH ROW EXECUTE PROCEDURE census . trig_vw_facts_ins_upd_del ();
Now when we update, delete, or insert into our view, it will update the underlying facts
table instead:
UPDATE census . vw_facts SET yr = 2012 WHERE yr = 2011 AND tract_id =
'25027761200' ;
This will output a note:
Query returned successfully: 56 rows affected, 40 ms execution time.