I have come across situation like this earlier. Looks like "at runtime"
two rows are getting selected. create a dummy table and insert the key
values/relevant values to see what "extra" row is being selected.
HTH
GovindanK
> Hi Listers
>
> I have a trigger that is an on update trigger that is somehow writing two
> records when a record is updated in the table that the trigger is
> associated with.
> I have run the select statement to see if it will retrieve multiple
> records and the msf071 table that has the record that causes the trigger
> to fire has only on record for the equip_no
> Why does it do this and how do I fix it.
> As you might notice it inserts across a link
> I have had a loom at Metalink but wasn't able to locate anything
>
> Trigger code below
>
> create or replace trigger msf071_archibus_update
> after update on msf071
> for each row
> declare
> -- local variables here
> v_equip_number msf071.ENTITY_value%TYPE;
> v_equip_class msf600.equip_class%TYPE;
> v_attrib_value MSF6A4.Attrib_value%TYPE;
> v_ref_value msf071.Ref_code%TYPE;
> v_entity_type msf071.entity_type%TYPE;
>
>
>
> begin
>
> v_equip_number := :old.Entity_value;
> v_entity_type := :old.entity_type;
> v_ref_value := :new.ref_code;
> IF v_ref_value = ('U') AND v_entity_type = 'EQP' THEN
>
> INSERT INTO [EMAIL PROTECTED] (equip_no,
> dstrct_code,
> NAME,
> assoc_value,
> equip_status,
> active_flag,
> parent_equip,
> equip_classif_3,
> sizecell,
> sizem,
> aream,
> suburb,
> postcode,
> street_no,
> street_name,
> state)
> SELECT c.equip_no,
> dstrct_code,
> c.item_name_1,
> substr(h.assoc_rec, 1, 1),
> equip_status,
> active_flg,
> parent_equip,
> equip_classifx3,
> to_number(d.attrib_value_num_9),
> To_number(e.attrib_value_num_9) "SIZE",
> to_number(f.attrib_value_num_9) "AREASQM",
> substr(g.suburb, 1, 30),
> substr(g.zip_code, 1, 4),
> street_no,
> substr((g.street_name|| ' ' || a.table_desc)
> ,1,50)"STREET_TYPE",
> substr(b.table_desc, 1, 30) "STATE"
> FROM MSF600 c, view_MSF6a4_cellsize d, view_msf6a4_size e,
> view_msf6a4_area f, MSF011 g, view_msf010_assoc_value h,
> view_msf010_streettype a, view_msf010_state b
> WHERE c.equip_no = v_equip_number AND
> c.Equip_no = d.equip_no(+) AND
> c.Equip_no = e.equip_no(+) AND
> c.Equip_no = f.equip_no(+)AND
> c.equip_class = h.table_code AND
> c.location = g.location(+) AND
> g.street_type = a.table_code(+) AND
> g.state = b.table_code(+) ;
>
> /*end loop;*/
>
> END IF;
>
> end msf071_archibus_update;
> =================================================
> Peter McLarty E-mail: [EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
