Re: Trigger double firing apparently double inserts

your doing an insert select. have you checked to see if you are selecting two records? you can add where rownum < 2

and you can verify that two records are being inserted by added a primary key to the recipient table and you should get a constraint error if this is correct.
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, June 13, 2003 3:09 AM

> 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]
> Technical Consultant WWW: http://www.mincom.com
> APAC Technical Services Phone: +61 (0)7 3303 3461
> Brisbane, Australia Mobile: +61 (0)402 094 238
> Facsimile: +61 (0)7 3303 3048
> =================================================
> A great pleasure in life is doing what people say you cannot do.
>
> - Walter Bagehot (1826-1877 British Economist)
> =================================================
> Mincom "The People, The Experience, The Vision"
>
> =================================================
>
> This transmission is for the intended addressee only and is confidential
> information. If you have received this transmission in error, please
> delete it and notify the sender. The contents of this e-mail are the
> opinion of the writer only and are not endorsed by the Mincom Group of
> companies unless expressly stated otherwise.
>
>
> --
> 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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ryan
  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).