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

You should consider an INSTEAD OF TRIGGER

CREATE OR REPLACE TRIGGER xxx
INSTEAD OF INSERT
ON view_name
BEGIN
DECLARE
Cursor on first table
BEGIN
insert into first table
insert into second table
END;
END xxx;

George Dimopoulos <george_at_cheos.ubc.ca> wrote in message news: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 Sat Oct 02 1999 - 22:54:30 CEST