Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Creating an update rule for a view

Posted on 2004-09-02
Medium Priority
Last Modified: 2012-05-05
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?


Question by:georgia
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
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".

Author Comment

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 :(
LVL 22

Expert Comment

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

LVL 22

Accepted Solution

earth man2 earned 1000 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.

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
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.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

636 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