Solved

Creating an update rule for a view

Posted on 2004-09-02
4
276 Views
Last Modified: 2012-05-05
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
Comment
Question by:georgia
  • 3
4 Comments
 
LVL 22

Expert Comment

by:earth man2
ID: 11964518
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
 

Author Comment

by:georgia
ID: 11964605
unfortunately that is not an option due to a constraint within the system I am working - has to be updated through the view :(
0
 
LVL 22

Expert Comment

by:earth man2
ID: 11966208
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
 
LVL 22

Accepted Solution

by:
earth man2 earned 250 total points
ID: 11966466
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now