Home » Developer & Programmer » Forms » Update data in view (6i)
Update data in view [message #418671] Tue, 18 August 2009 01:36 Go to next message
Messages: 69
Registered: July 2009
I created a view in program unit (procedure).
when new form instance I call this
	V_COMMAND  varchar2(5000);

V_COMMAND := 'Create or replace view Challan_view as 
		 select  qry1.dcno,qry1.dcdate,qry1.customername,qry1.perscode,qry1.persname,qry1.commission,qry1.netrate,
	   qry2.idcqty, nvl(qry1.dcqty,0) - nvl(qry2.idcqty,0) Remaining

(select a.dcno,a.dcdate,a.customername,a.perscode,a.persname,a.commission,a.netrate,
	   from masterdc a , dc_detail b
	   where a.dcno = b.dcno) qry1,
(select b.dcno, b.dcdate,b.itemcode,b.itemname,
	   b.dcqty IDCQTY
	   from masterinv a , inv_detail b
	   where a.invno = b.invno) qry2
where qry1.dcno = qry2.dcno (+)
--and qry1.itemcode = qry2.itemcode(+)
and nvl(qry2.idcqty,0) < nvl(qry1.dcqty,0)';

and created a block base with this view
it has detail block
I have one column chk varchar2(1)
it is a check box

I need when I check this box chk will be 'Y'

I mean update the view . if I check the box 4 records all the records chk must be 'Y' in view

I write a code on-update trigger;
if :challan1_view.chk = 'Y' then 
update challan_view
set chk ='Y';
end if;

but not saving CHK ='Y'
Re: Update data in view [message #418710 is a reply to message #418671] Tue, 18 August 2009 04:43 Go to previous message
Messages: 12478
Registered: September 2008
Location: Rainy Manchester
Senior Member
Don't create views from forms - especially if you're going to base datablocks on them. Source control exists for a reason.

I suspect the on-update is not firing becuase the view is not updatable.

Have a look at USER_UPDATABLE_COLUMNS and read up on updatable views, but generally if it's a complex query (and yours is) it won't be updatable.

Also your outer-join is wrong - you're missing a (+) on the last line.

If you do manage to get an updatable view you shouldn't need an on-update trigger, forms can update views as though they are tables - no special code required. In fact you should never need to use on-update (or insert or delete).

Finally, if your on-update had fired, it would have updated every row in the view becuase you haven't got a where clause.
Previous Topic: Format Mask
Next Topic: FRM-40010-Cannot read form (merged 3)
Goto Forum:

Current Time: Sun Jan 22 08:39:58 CST 2017

Total time taken to generate the page: 0.19978 seconds