• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

Creating an update rule for a view

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

SELECT
 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
  UNION
  SELECT
 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?

Thanks!


0
georgia
Asked:
georgia
  • 3
1 Solution
 
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".
0
 
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 :(
0
 
earth man2Commented:
CREATE [ OR REPLACE ] RULE name AS ON event
    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.



0
 
earth man2Commented:
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.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now