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

That worked amazingly man thanks. All my greif because I was using quotes around my variable. I have a question for you. Why if it is a string do I not use quotes?

Thanks again,
Jeff

"Jeff Boyer" <jdboyer@(remove)icomproductions.ca> wrote in message news:9av9r8$23s$1_at_news3.cadvision.com...
> 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 - 12:45:11 CDT