Re: Using a trigger to insert into a child table

it seems to me, just from what you have given here, that perhaps the foriegn key constraint is inappropriate. a foreign key is a rule stating the parent MUST EXIST for a child to exist. what you want to do is say, "but not really."...so why is there a foreign key?

steve long
"The world's best technology strategist." (TM)

"Jeff Boyer" <jdboyer_at_(remove)icomproductions.ca> wrote in message news:9asmpr$4bs$1_at_news3.cadvision.com...
> Basically what I have found out is that because this is happening before
> this insert there is no primary key in the parent table to relate to the
> child table. What I need to be able to do it use a before insert for the
> parent table and an after insert for the child table. Can I do this in
 the
> same trigger? If not, can anyone suggest a better way for me to
 accomplish
> this?
>
> Thanks again,
> Jeff
> "Jeff Boyer" <jdboyer_at_(remove)icomproductions.ca> wrote in message
> news:9askss$3dm$1_at_news3.cadvision.com...
> > 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 - 03:18:06 CEST