Regards rafiq
Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Fri, 13 Jun 2003 13:49:37 -0800
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).
_________________________________________________________________
STOP MORE SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: M Rafiq 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).
