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

I'll try it out, thanks again for all your help. This one has been bother me for quite some tie now. I really do appreciate it.

Jeff
"Steve Bell" <swayne.bell_at_sympatico.ca> wrote in message news:3AD326B6.F5B4B331_at_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_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;
> /
>
> 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:26 CDT