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

The only problem is that the before insert trigger actually creates and inserts the "SSCA" number. I can actually get the after trigger to insert everything but the SSCA number. I think I am calling the variable wrong. Hmmmm.......... I'll spend some more time with it today.

Thanks alot for your help.
"Steve Bell" <swayne.bell_at_sympatico.ca> wrote in message news:3AD324B7.C0D48A2E_at_sympatico.ca...
> 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:32:08 CDT