Creating an update rule for a view

I have a view that looks like this.... called bb_po_view

 bb_child_po_bb_child_po.trx_id as "Unique",
 substr(bb_child_po_bb_child_po.create_date,1,16) as "Received Date" ,
bb_child_po_bb_child_po.sender as "Sender Name" ,
bb_child_po_status_id_concept.status_name as "Status" ,
bb_master_po_bb_master_po.st_control_num as "Control Number" ,
bb_child_po_bb_child_po.po_num as "PO #" ,
bb_master_po_bb_master_po.source_file_type as "Source Recieved" ,
bb_master_po_bb_master_po.source_file_uri as "Source URI" ,
bb_child_po_bb_child_po.file_type as "Document Sent" ,
bb_child_po_bb_child_po.file_uri as "Doc Location" ,
bb_child_po_bb_child_po.ui_filter_num as "UI"
 FROM bb_child_po_bb_child_po, bb_child_po_trx_id_concept, bb_child_po_status_id_concept, bb_master_po_bb_master_po
 WHERE bb_child_po_bb_child_po.trx_id = bb_child_po_trx_id_concept.trx_id
 AND bb_child_po_trx_id_concept.status_id = bb_child_po_status_id_concept.status_id
 AND bb_child_po_bb_child_po.master_trx_id = bb_master_po_bb_master_po.trx_id
 bb_master_po_bb_master_po.trx_id as "Unique",
 substr(bb_master_po_bb_master_po.create_date,1,16) as "Received Date" ,
bb_master_po_bb_master_po.sender as "Sender Name" ,
'Custom' as "Status" ,
bb_master_po_bb_master_po.st_control_num as "Control Number" ,
bb_master_po_bb_master_po.po_num as "PO #" ,
bb_master_po_bb_master_po.source_file_type as "Source Recieved" ,
bb_master_po_bb_master_po.source_file_uri as "Source URI" ,
bb_master_po_bb_master_po.target_file_type as "Document Sent" ,
bb_master_po_bb_master_po.target_file_uri as "Doc Location" ,
bb_master_po_bb_master_po.ui_filter_num as "UI"
 FROM bb_master_po_bb_master_po
 WHERE bb_master_po_bb_master_po.sender = 'Test'

when I try to update that view with the statement below, I get the following error

UPDATE bb_po_view
SET ui_filter_num = 3
WHERE  "Unique" = '01791694-4555-41a4-8546-16abe39e16d4'

Sep  1 17:28:58 [26007]: [31034] ERROR:  Cannot update a view without an appropriate rule

I am having problems creating this rule... can anyone tell me what it should be?


Who is Participating?
earth man2Connect With a Mentor Commented:
You need the unconditional do instead nothing rule to make updates to the view valid, see documentation on CREATE RULE.

Two more rules should be defined each with the WHERE clause specified for updates to the two underlying tables.
earth man2Commented:
Why don't you update the underlying table instead ?

update bb_child_po_bb_child_po set ui_filter_num = 3 where trx_id = '01791694-4555-41a4-8546-16abe39e16d4';

also view bb_po_view has no column called ui_filter_num you have aliased it as "UI".
georgiaAuthor Commented:
unfortunately that is not an option due to a constraint within the system I am working - has to be updated through the view :(
earth man2Commented:
    TO table [ WHERE condition ]
    DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }

To ensure you have a database that supports VIEW UPDATE RULES try this for starters.

CREATE OR REPLACE RULE bb_po_view_update AS ON update TO bb_po_view DO INSTEAD NOTHING;

how can you diferentiate between updates to bb_master_po_bb_master_po and bb_child_po_bb_child_po ?

You need to put that condition in the where claws.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.