DEV2000-4.5 (BASE A BLOCK ON A JOINED-TABLE VIEW)

From: George Dimopoulos <george_at_cheos.ubc.ca>
Date: Mon, 20 Sep 1999 12:13:22 -0700
Message-ID: <7s60ul$jt$1_at_nntp.itservices.ubc.ca>

I would like to base a block on a joined-table view.

There are some columns that are present just for query ability.

Has anyone done this with success?

I have looked at some examples, and they state to use ON-LOCK, ON-INSERT, ON-UPDATE triggers
which I have implemented, but when I try to update the record I receive following error message

My view

CREATE or replace VIEW medications_v
AS
SELECT m.rowid m_rowid,m.*,dc.chemical_name, d.brand_name from medications m, drugs d, drug_chem dc where d.drug_code = m.drug_code
AND dc.chemical_code = d.chemical_code

My on-lock trigger

SELECT id
  INTO :medications.id
  FROM MEDICATIONS
  WHERE ROWID = :medications.m_rowid
  FOR UPDATE OF MEDICATIONS; My on-insert trigger

DECLARE
  CURSOR get_rowid IS
    select rowid from medications
    where id = :medications.id;

BEGIN
  INSERT INTO MEDICATIONS

  VALUES (:medications.id, :medications.patient_id,


          :medications.drug_code,:medications.route,

          :medications.code_auxil, :medications.other_auxil,


          :medications.discontinue_date,:medications.type_dose);


  OPEN get_rowid;
  FETCH get_rowid INTO :medications.m_rowid;
  CLOSE get_rowid;

END; My on-update trigger

  UPDATE medications
  set discontinue_date = :medications.discontinue_date   WHERE rowid = :medications.m_rowid;

ORA-01003: no statement parsed in procedure medications.on-lock

--
George Dimopoulos
george_at_cheos.ubc.ca
(604)631-5890

Received on Mon Sep 20 1999 - 21:13:22 CEST