Re: Using a trigger to insert into a child table

You've probably solved this by now but if not..

You could retain the old before trigger and implement the insert as an after trigger on the parent table.

Cheer

Simon Irvin

"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 Thu Apr 12 2001 - 16:30:51 CEST