Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: using a trigger to enter data into a child table.

Re: using a trigger to enter data into a child table.

From: Steve Bell <swayne.bell_at_sympatico.ca>
Date: Tue, 10 Apr 2001 15:38:08 GMT
Message-ID: <3AD326B6.F5B4B331@sympatico.ca> 






Jeff,


This works fine:


Before Insert:



CREATE OR REPLACE TRIGGER Comp_Id_Trg


  BEFORE INSERT on Company_reg


   FOR EACH ROW



   DECLARE



   Temp Varchar2(30);


   Temp2 Number;


   BEGIN



    SELECT Comp_ID_SEQ.NEXTVAL


   INTO Temp FROM DUAL;


   Temp := 'SSCA-'||Temp;

   :New.Company_id := Temp;
   :New.Phasestat := 'Phase1Begin';
   :New.Phase := 'Choose One';
   :New.Reason := 'Choose One';




end;


/


After insert:



CREATE OR REPLACE TRIGGER CON_INFO_TRG



 AFTER INSERT on COMPANY_REG


  FOR EACH ROW



  DECLARE



   Temp Number;


   Temp2 varchar2(30);


  BEGIN



   Temp2 := :New.COMPANy_Id;


   SELECT Con_ID_SEQ.NEXTVAL


   INTO Temp FROM DUAL;


   Insert INTO CON_INFO


(CON_ID,COMPANY_ID,CON_MAIL1,CON_MAIL2,CON_DIRCALL,PHS1FORM_FAX,PHS1FORM_EMAIL,

 UNSOLCALL,



   PHSFORM_COURIER, PHSFORM_RECBACK, PHSFORM_INPUTED, PHSFORM_WEBSITE,

CON_VALQ1INFO, CON_PARTICIPATION, PHASE2PRE_CALLEDCOMP,

   CALLCOMPLETED, PHS2FAX, PHS2EMAIL, PHS2COURIER, PHS2RECBACK,

PHS2INPUTTED, PHS2WEBSITE) VALUES (Temp, Temp2,
1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
END;



/



I think I see a problem.....on your original script you have quotes around 'Temp2'
in the VALUES


clause of your insert statement...you'll be inserting a literal which will violate
the constraint..


Dump the quotes so it inserts the variable value and you should be home and dry.



Steve Bell wrote:



> I'm doing the insert without the before insert trigger as you can see..other

> than that, the 'after insert ' trigger is cut and paste from your posting.

>

> So, I'll try it using your before insert trigger and see if that screws it up?

>

> Jeff Boyer wrote:

>

> > Well thats strange, I wonder why it would work for you and not for me.  Did

> > you alter the code in any way?

> > "Steve Bell" <swayne.bell_at_sympatico.ca> wrote in message

> > news:3AD3211A.FFD9F91D_at_sympatico.ca...

> > > Jeff,

> > > Okay..the code in your posting works if I insert into company_reg

 manually.


> > >

> > > i.e.

> > >

> > > SQL> insert into company_reg

> > >   2  values('SSCA-19','Phase1Begin','Choose One','Choose One');

> > >

> > > SQL> select * from company_reg;

> > >

> > > COMPANY_ID           PHASESTAT            PHASE


> > > -------------------- -------------------- --------------

> > > REASON


> > > ------------------------------

> > > SSCA-19              Phase1Begin          Choose One

> > > Choose One

> > >

> > >

> > > SQL> select con_id, company_id from con_info;

> > >

> > >     CON_ID COMPANY_ID


> > > ---------- --------------------

> > >         15 SSCA-19              - -  inserted by after insert trigger

> > >

> > > Is this the result you're aiming for?

> > >

> > > Jeff Boyer wrote:

> > >

> > > > I have used this code and combined them both only to get the same

 results.


> > > >

> > > > CREATE OR REPLACE TRIGGER CON_INFO_TRG


> > > >  AFTER INSERT on COMPANY_REG

> > > >   FOR EACH ROW


> > > >   DECLARE


> > > >    Temp Number;

> > > >    Temp2 varchar2(30);

> > > >   BEGIN


> > > >    Temp2 := :New.COMPANy_Id;

> > > >    SELECT Con_ID_SEQ.NEXTVAL

> > > >    INTO Temp FROM DUAL;

> > > >    Insert INTO CON_INFO

> > > >



 (CON_ID,COMPANY_ID,CON_MAIL1,CON_MAIL2,CON_DIRCALL,PHS1FORM_FAX,PHS1FORM_EMA



> > > > IL, UNSOLCALL,


> > > >    PHSFORM_COURIER, PHSFORM_RECBACK, PHSFORM_INPUTED, PHSFORM_WEBSITE,


> > > > CON_VALQ1INFO, CON_PARTICIPATION, PHASE2PRE_CALLEDCOMP,


> > > >    CALLCOMPLETED, PHS2FAX, PHS2EMAIL, PHS2COURIER, PHS2RECBACK,


> > > > PHS2INPUTTED, PHS2WEBSITE) VALUES (Temp, 'Temp2',

> > > > 1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);

> > > >

> > > >  END CON_INFO_TRG;


> > > > /

> > > > "Steve Bell" <swayne.bell_at_sympatico.ca> wrote in message

> > > > news:3AD2F201.C62474BE_at_sympatico.ca...

> > > > > Okay.......can you post the revised trigger code Jeff?

> > > > >

> > > > >

> > > > >

> > > > > Jeff Boyer wrote:

> > > > >

> > > > > > Already done that, I get the same error.

> > > > > >

> > > > > > Thanks anyway!

> > > > > >

> > > > > > "Steve Bell" <swayne.bell_at_sympatico.ca> wrote in message

> > > > > > news:3AD20AFE.A3D9819A_at_sympatico.ca...

> > > > > > > Doh !

> > > > > > >

> > > > > > > Could you make the second part of the trigger an after insert

 trigger?


> > > > > > >

> > > > > > > Jeff Boyer wrote:

> > > > > > >

> > > > > > > > The exact error that I am getting is "Unique constraint error -

 parent


 key


> > > > > > > > not found"  I am assuming that this is happening because the

 child


 table


> > > > > > > > isn't receiving the Primary Key from the Parent table in time.

 They


 all


> > > > > > > > compile fine but when I actually do an insert on the Company_Reg

 table


> > > > > > > > (which would fire the trigger) I get this error.

> > > > > > > >

> > > > > > > > I appreciate your help, I have been banging my head against this

 for


 hours


> > > > > > > > now and can't seem to find the solution.

> > > > > > > >

> > > > > > > > "Steve Bell" <swayne.bell_at_sympatico.ca> wrote in message

> > > > > > > > news:3AD208D4.B71A640B_at_sympatico.ca...

> > > > > > > > > Hi Jeff,

> > > > > > > > > I've been playing with this one for a while!

> > > > > > > > > I created the tables and triggers as you've written them by

 cutting


 and


 pasting


> > > > > > > > > and it works fine..can you include further detail on the

 unique


 constraint


> > > > > > > > > violation?

> > > > > > > > >

> > > > > > > > > Steve

> > > > > > > > >

> > > > > > > > > Jeff Boyer wrote:

> > > > > > > > >

> > > > > > > > > > I have a trigger that fires whenever an insert in done on

 one of


 my


 tables.


> > > > > > > > > > It has been working great, but now I want to use this

 trigger to


 insert


 a


> > > > > > > > > > some values into a child table.  I don't really understand

 how


 to do


 this.


> > > > > > > > > > This is the code I am using:

> > > > > > > > > >

> > > > > > > > > > Working trigger before the added code:

> > > > > > > > > >

> > > > > > > > > > CREATE OR REPLACE TRIGGER Comp_Id_Trg

> > > > > > > > > >   BEFORE INSERT on Company_reg

> > > > > > > > > >    FOR EACH ROW


> > > > > > > > > >    DECLARE


> > > > > > > > > >    Temp Varchar2(30);

> > > > > > > > > >    BEGIN


> > > > > > > > > >     SELECT Comp_ID_SEQ.NEXTVAL

> > > > > > > > > >    INTO Temp FROM DUAL;

> > > > > > > > > >    Temp := 'SSCA-'||Temp;

> > > > > > > > > >    :New.Company_id := Temp;

> > > > > > > > > >    :New.Phasestat := 'Phase1Begin';

> > > > > > > > > >    :New.Phase := 'Choose One';

> > > > > > > > > >    :New.Reason := 'Choose One';

> > > > > > > > > >  END Comp_Id_Trg;

> > > > > > > > > > /

> > > > > > > > > >

> > > > > > > > > > Now This is what I am trying to do but can't seem to get the

 code


 right:


> > > > > > > > > >

> > > > > > > > > > CREATE OR REPLACE TRIGGER Comp_Id_Trg

> > > > > > > > > >   BEFORE INSERT on Company_reg

> > > > > > > > > >    FOR EACH ROW


> > > > > > > > > >    DECLARE


> > > > > > > > > >    Temp Varchar2(30);

> > > > > > > > > >    Temp2 Number;

> > > > > > > > > >    BEGIN


> > > > > > > > > >     SELECT Comp_ID_SEQ.NEXTVAL

> > > > > > > > > >    INTO Temp FROM DUAL;

> > > > > > > > > >    Temp := 'SSCA-'||Temp;

> > > > > > > > > >    :New.Company_id := Temp;

> > > > > > > > > >    :New.Phasestat := 'Phase1Begin';

> > > > > > > > > >    :New.Phase := 'Choose One';

> > > > > > > > > >    :New.Reason := 'Choose One';

> > > > > > > > > >     SELECT Con_ID_SEQ.NEXTVAL

> > > > > > > > > >     INTO Temp2 FROM DUAL;

> > > > > > > > > >     Insert INTO CON_INFO

> > > > > > > > > >

> > > > > > > >

> > > > > >

> > > >



 (CON_ID,COMPANY_ID,CON_MAIL1,CON_MAIL2,CON_DIRCALL,PHS1FORM_FAX,PHS1FORM_EMA



> > > > > > > > > > IL, UNSOLCALL,


> > > > > > > > > >     PHSFORM_COURIER, PHSFORM_RECBACK, PHSFORM_INPUTED,


 PHSFORM_WEBSITE,



> > > > > > > > > > CON_VALQ1INFO, CON_PARTICIPATION, PHASE2PRE_CALLEDCOMP,


> > > > > > > > > > CALLCOMPLETED, PHS2FAX, PHS2EMAIL, PHS2COURIER, PHS2RECBACK,


 PHS2INPUTTED,



> > > > > > > > > > PHS2WEBSITE) VALUES (Temp2, 'Temp',

> > > > > > > > > > 1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);

> > > > > > > > > >

> > > > > > > > > >  END Comp_Id_Trg;

> > > > > > > > > > /

> > > > > > > > > >

> > > > > > > > > > Can anyone tell me what I am doing wrong.  This gives me a

 unique


 constraint


> > > > > > > > > > error.

> > > > > > > > > >

> > > > > > > > > > Thanks,

> > > > > > > > > > Jeff

> > > > > > > > >

> > > > > > >

> > > > >

> > >

Received on Tue Apr 10 2001 - 10:38:08 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US