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

Hi Jeff,
*phew*..glad it worked.
You don't use quotes because you are referring to a variable. As you know, the datatype of the variable could be anything...some requiring quotes and some not.

As we saw, by putting quotes around it you're saying that the variable name IS a string so it inserts it literally, just as if you had typed 'Smith'.

I'm glad it's working..

Steve

Jeff Boyer wrote:

> 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 - 13:37:11 CDT