|
|
 |
 |
 |
 |
Please help with Inserting ID into table from another table in Oracle 9.2
Good day everyone, I have 3 tables. A user table and Hardware table and approver
table(Please correct the design if you see any issues.
[code]
Users Table
User_ID INT,
First_Name VARCHAR2
Last_Name VARCHAR2
Email VARCHAR2
Extension VARCHAR2
Create_Date DATE
Last_Modified_By VARCHAR2
[/code]
Second Table
[code]
Hardware Table
Hardware_ID INT
User_ID INT
Date_Required DATE
Equipment VARCHAR2
Purpose VARCHAR2
Duration DATE
Create_Date DATE
Last_Modified_By VARCHAR2
[/code]
I have been thinking of a third table for normalization called
Approver for approver info as follows:
[code]
Approver
Hardware_ID INT
User_ID INT
Approver_ID INT
Approver_Name VARCHAR2
Status INT
[/code]
A User comes in a fills out a form and then clicks submit. On submit
(this is where I am stuck) I created 2 procedures to do my inserts:
[code]
1st procedure
CREATE OR REPLACE PROCEDURE User_Info_Insert
(User_ID OUT INT,
First_Name IN VARCHAR2,
Last_Name IN VARCHAR2,
Email IN VARCHAR2,
Extension IN VARCHAR2,
Last_Modified_By IN VARCHAR2)
IS
BEGIN
insert into Users values
(Corp_UserID_seq.nextval,First_Name,Last_Name,Email,Extension,sysdate,Last_ Modified_By)
Returning User_ID INTO User_ID;
END;
[/Code]
2nd procedure
[code]
CREATE OR REPLACE PROCEDURE
Hardware_Info_Insert (DateRequired IN DATE,
Equipment IN VARCHAR2,
Purpose IN VARCHAR2,
LoanDuration IN DATE,
Approver IN VARCHAR2,
LastModifiedBy IN VARCHAR2)
IS
begin
insert into Hardware_Desc values
(Corp_HardwareID_seq.nextval,DateRequired,Equipment,Purpose,LoanDuration,Ap prover,sysdate,LastModifiedBy);
end;
3rd will follow and will contain the approver info.
[/code]
I need to get the User_ID( Returning User_ID INTO User_ID) from the
first insert to also be added to the hardware table and the approver
table?. Is this possible? Unless I can just run one big insert that
will complete these 3 inserts. Could someone please show me by
example how to do this? Please please please your help is really
appreciated. Thanks everyone.
MrHelpMe wrote: > Good day everyone, > I have 3 tables. A user table and Hardware table and approver
> table(Please correct the design if you see any issues.
> [code]
> Users Table
> User_ID INT,
> First_Name VARCHAR2
> Last_Name VARCHAR2
> Email VARCHAR2
> Extension VARCHAR2
> Create_Date DATE
> Last_Modified_By VARCHAR2
> [/code]
> Second Table
> [code]
> Hardware Table
> Hardware_ID INT
> User_ID INT
> Date_Required DATE
> Equipment VARCHAR2
> Purpose VARCHAR2
> Duration DATE
> Create_Date DATE
> Last_Modified_By VARCHAR2
> [/code]
> I have been thinking of a third table for normalization called
> Approver for approver info as follows:
> [code]
> Approver
> Hardware_ID INT
> User_ID INT
> Approver_ID INT
> Approver_Name VARCHAR2
> Status INT
> [/code]
> A User comes in a fills out a form and then clicks submit. On submit
> (this is where I am stuck) I created 2 procedures to do my inserts:
> [code]
> 1st procedure
> CREATE OR REPLACE PROCEDURE User_Info_Insert
> (User_ID OUT INT,
> First_Name IN VARCHAR2,
> Last_Name IN VARCHAR2,
> Email IN VARCHAR2,
> Extension IN VARCHAR2,
> Last_Modified_By IN VARCHAR2)
> IS
> BEGIN
> insert into Users values
> (Corp_UserID_seq.nextval,First_Name,Last_Name,Email,Extension,sysdate,Last_ Modified_By)
> Returning User_ID INTO User_ID;
> END;
> [/Code]
> 2nd procedure
> [code]
> CREATE OR REPLACE PROCEDURE
> Hardware_Info_Insert (DateRequired IN DATE,
> Equipment IN VARCHAR2,
> Purpose IN VARCHAR2,
> LoanDuration IN DATE,
> Approver IN VARCHAR2,
> LastModifiedBy IN VARCHAR2)
> IS
> begin
> insert into Hardware_Desc values
> (Corp_HardwareID_seq.nextval,DateRequired,Equipment,Purpose,LoanDuration,Ap prover,sysdate,LastModifiedBy);
> end;
> 3rd will follow and will contain the approver info.
> [/code]
> I need to get the User_ID( Returning User_ID INTO User_ID) from the
> first insert to also be added to the hardware table and the approver
> table?. Is this possible? Unless I can just run one big insert that
> will complete these 3 inserts. Could someone please show me by
> example how to do this? Please please please your help is really
> appreciated. Thanks everyone.
Why two procedures and not one? -- Daniel A. Morgan University of Washington damor @x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org -----------------------------------------------Reply-----------------------------------------------
On Apr 30, 11:29 am, DA Morgan <damor @psoug.org> wrote:
> MrHelpMe wrote: > > Good day everyone, > > I have 3 tables. A user table and Hardware table and approver
> > table(Please correct the design if you see any issues.
> > [code]
> > Users Table
> > User_ID INT,
> > First_Name VARCHAR2
> > Last_Name VARCHAR2
> > Email VARCHAR2
> > Extension VARCHAR2
> > Create_Date DATE
> > Last_Modified_By VARCHAR2
> > [/code]
> > Second Table
> > [code]
> > Hardware Table
> > Hardware_ID INT
> > User_ID INT
> > Date_Required DATE
> > Equipment VARCHAR2
> > Purpose VARCHAR2
> > Duration DATE
> > Create_Date DATE
> > Last_Modified_By VARCHAR2
> > [/code]
> > I have been thinking of a third table for normalization called
> > Approver for approver info as follows:
> > [code]
> > Approver
> > Hardware_ID INT
> > User_ID INT
> > Approver_ID INT
> > Approver_Name VARCHAR2
> > Status INT
> > [/code]
> > A User comes in a fills out a form and then clicks submit. On submit
> > (this is where I am stuck) I created 2 procedures to do my inserts:
> > [code]
> > 1st procedure
> > CREATE OR REPLACE PROCEDURE User_Info_Insert
> > (User_ID OUT INT,
> > First_Name IN VARCHAR2,
> > Last_Name IN VARCHAR2,
> > Email IN VARCHAR2,
> > Extension IN VARCHAR2,
> > Last_Modified_By IN VARCHAR2)
> > IS
> > BEGIN
> > insert into Users values
> > (Corp_UserID_seq.nextval,First_Name,Last_Name,Email,Extension,sysdate,Last_ Modified_By)
> > Returning User_ID INTO User_ID;
> > END;
> > [/Code]
> > 2nd procedure
> > [code]
> > CREATE OR REPLACE PROCEDURE
> > Hardware_Info_Insert (DateRequired IN DATE,
> > Equipment IN VARCHAR2,
> > Purpose IN VARCHAR2,
> > LoanDuration IN DATE,
> > Approver IN VARCHAR2,
> > LastModifiedBy IN VARCHAR2)
> > IS
> > begin
> > insert into Hardware_Desc values
> > (Corp_HardwareID_seq.nextval,DateRequired,Equipment,Purpose,LoanDuration,Ap prover,sysdate,LastModifiedBy);
> > end;
> > 3rd will follow and will contain the approver info.
> > [/code]
> > I need to get the User_ID( Returning User_ID INTO User_ID) from the
> > first insert to also be added to the hardware table and the approver
> > table?. Is this possible? Unless I can just run one big insert that
> > will complete these 3 inserts. Could someone please show me by
> > example how to do this? Please please please your help is really
> > appreciated. Thanks everyone.
> Why two procedures and not one?
> --
> Daniel A. Morgan
> University of Washington
> damor@x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
Hi DA Morgan, Thanks for the reply. I used 2 procedures originally because I wasn't sure how to accomplish this in 1 procedure. To be honest I just managed to figure this out and it looks like it is definitely working now, however, I need your assistance and ask you and other experts what I can do in the below situation. Now that I have the insert working, how can I say the following. Upon
insert if the FirstName, LastName and Email address are the same do
not insert into the Users table but insert into the other 2 tables all
the info. plus the appropriate user id. So if Joe smith is the first
to enter a request his UserID will be 1. Now if Joe smith enters
another request his id should still remain 1 and this 1 will be
captured in the other 2 tables and not UserID 2 as it is currently
working. Here is my final insert that seems to work minus what I am
asking.
[code]
CREATE OR REPLACE PROCEDURE test (User_ID OUT INT,
Hardware_ID OUT INT,
Approver_ID OUT INT,
First_Name IN VARCHAR2,
Last_Name IN VARCHAR2,
Email IN VARCHAR2,
Extension IN VARCHAR2,
Last_Modified_By IN VARCHAR2,
Date_Required IN DATE,
Equipment IN VARCHAR2,
Purpose IN VARCHAR2,
Loan_Duration IN DATE,
Approver_Name IN VARCHAR2)
IS
BEGIN
INSERT INTO Users (User_ID, First_Name, Last_Name, Email, Extension,
Create_Date, Last_Modified_By)
VALUES Corp_UserID_seq.nextval, First_Name, Last_Name, Email,
Extension, sysdate, Last_Modified_By)
RETURNING User_ID INTO User_ID;
INSERT INTO Hardware (Hardware_ID, User_ID, Date_Required,
Description, Purpose, Duration, Create_Date, Last_Modified_By)
VALUES(Corp_HardwareID_seq.nextval, User_ID, Date_Required,
Equipment, Purpose, Loan_Duration, sysdate,Last_Modified_By)
RETURNING Hardware_ID INTO Hardware_ID;
INSERT INTO Approver (Approver_ID, Hardware_ID, User_ID,
Approver_Name, Status, Create_Date, Last_Modified_By)
VALUES(Corp_ApproverID_seq.nextval, Hardware_ID, User_ID,
Approver_Name, NULL, sysdate,Last_Modified_By)
RETURNING Approver_ID INTO Approver_ID;
COMMIT;
END;
[/code]
Thanks again all.
-----------------------------------------------Reply-----------------------------------------------
MrHelpMe wrote: >> Why two procedures and not one? >> -- >> Daniel A. Morgan >> University of Washington >> damor @x.washington.edu >> (replace x with u to respond) >> Puget Sound Oracle Users Group www.psoug.org- Hide quoted text - >>
> Hi DA Morgan,
> Thanks for the reply. I used 2 procedures originally because I wasn't
> sure how to accomplish this in 1 procedure. To be honest I just
> managed to figure this out and it looks like it is definitely working
> now, however, I need your assistance and ask you and other experts
> what I can do in the below situation.
> Now that I have the insert working, how can I say the following. Upon
> insert if the FirstName, LastName and Email address are the same do
> not insert into the Users table but insert into the other 2 tables all
> the info. plus the appropriate user id.
What you are asking is unclear but look at the Merge Statement if the choice in a single table is insert/update. Look at the INSERT FIRST, INSERT ALL, and INSERT WHEN statements. Also consider doing a SELECT COUNT(*) to determine if the record has
already been inserted.
You can find all of these in Morgan's Library at www.psoug.org.
Look up MERGE and INSERT.
--
Daniel A. Morgan
University of Washington
damor@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
-----------------------------------------------Reply-----------------------------------------------
On Apr 30, 12:09 pm, DA Morgan <damor @psoug.org> wrote:
> MrHelpMe wrote: > >> Why two procedures and not one? > >> -- > >> Daniel A. Morgan > >> University of Washington > >> damor @x.washington.edu > >> (replace x with u to respond) > >> Puget Sound Oracle Users Group www.psoug.org-Hide quoted text - > >>
> > Hi DA Morgan,
> > Thanks for the reply. I used 2 procedures originally because I wasn't
> > sure how to accomplish this in 1 procedure. To be honest I just
> > managed to figure this out and it looks like it is definitely working
> > now, however, I need your assistance and ask you and other experts
> > what I can do in the below situation.
> > Now that I have the insert working, how can I say the following. Upon
> > insert if the FirstName, LastName and Email address are the same do
> > not insert into the Users table but insert into the other 2 tables all
> > the info. plus the appropriate user id.
> What you are asking is unclear but look at the Merge Statement if the
> choice in a single table is insert/update. Look at the INSERT FIRST,
> INSERT ALL, and INSERT WHEN statements.
> Also consider doing a SELECT COUNT(*) to determine if the record has
> already been inserted.
> You can find all of these in Morgan's Library atwww.psoug.org.
> Look up MERGE and INSERT.
> --
> Daniel A. Morgan
> University of Washington
> damor@x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
Sorry DA Morgan let me clarify. I'm not sure how much you know about front end coding but I will keep it simple. I have an ASP page that when the user clicks on the page he is asked a serieds of question. Like what is your first name, what is your last name, what is your email adress, what kind of equipment do you want, for what purpose etc. After the user fills in all this information he/she will click a submit button. When they click the submit button, behind the scenes I do an insert into(based on the insert statement that I showed you above) into the users table, hardware table and approver table. Now if Joe Smith fills out this form and then clicks submit and then comes to the page again, fills out the form again and clicks submit, well I would not want to insert his name twice into the users table but rather have unique users being captured in this table. So therefore, how do I code this in the backend to say if the user is not unique don't insert the entry into the users table but all the other info. that he/she submitted will be inserted into the respective tables. I hope this is clear. Please let me know if it is not and in the mean time I will have a look at what you mentioned. -----------------------------------------------Reply-----------------------------------------------
MrHelpMe wrote: > On Apr 30, 12:09 pm, DA Morgan <damor @psoug.org> wrote: >> MrHelpMe wrote: >>>> Why two procedures and not one? >>>> -- >>>> Daniel A. Morgan >>>> University of Washington >>>> damor @x.washington.edu >>>> (replace x with u to respond) >>>> Puget Sound Oracle Users Group www.psoug.org-Hide quoted text - >>>> >>> Hi DA Morgan, >>> Thanks for the reply. I used 2 procedures originally because I wasn't >>> sure how to accomplish this in 1 procedure. To be honest I just >>> managed to figure this out and it looks like it is definitely working >>> now, however, I need your assistance and ask you and other experts >>> what I can do in the below situation. >>> Now that I have the insert working, how can I say the following. Upon >>> insert if the FirstName, LastName and Email address are the same do >>> not insert into the Users table but insert into the other 2 tables all >>> the info. plus the appropriate user id. >> What you are asking is unclear but look at the Merge Statement if the >> choice in a single table is insert/update. Look at the INSERT FIRST, >> INSERT ALL, and INSERT WHEN statements. >> Also consider doing a SELECT COUNT(*) to determine if the record has
>> already been inserted.
>> You can find all of these in Morgan's Library atwww.psoug.org.
>> Look up MERGE and INSERT.
>> --
>> Daniel A. Morgan
>> University of Washington
>> damor@x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>>
> Sorry DA Morgan let me clarify. I'm not sure how much you know about
> front end coding but I will keep it simple. I have an ASP page that
> when the user clicks on the page he is asked a serieds of question.
> Like what is your first name, what is your last name, what is your
> email adress, what kind of equipment do you want, for what purpose
> etc. After the user fills in all this information he/she will click a
> submit button. When they click the submit button, behind the scenes I
> do an insert into(based on the insert statement that I showed you
> above) into the users table, hardware table and approver table. Now
> if Joe Smith fills out this form and then clicks submit and then comes
> to the page again, fills out the form again and clicks submit, well I
> would not want to insert his name twice into the users table but
> rather have unique users being captured in this table. So therefore,
> how do I code this in the backend to say if the user is not unique
> don't insert the entry into the users table but all the other info.
> that he/she submitted will be inserted into the respective tables. I
> hope this is clear. Please let me know if it is not and in the mean
> time I will have a look at what you mentioned.
You can not do it unless you have a way of defining a unique person. There are, for example, two Daniel Morgans teaching at the University of Washington: At least 12 of us in the Seattle phone book. What is it you are going to use to define a unique person?
Once you know that it becomes your primary key and then a simple
SELECT COUNT(*)
INTO i
FROM ....;
answers the question.
IF i = 0 THEN
... new customer
ELSIF i = 1 THEN
... existing customer
ELSE
... raise an exception
END IF;
--
Daniel A. Morgan
University of Washington
damor@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
-----------------------------------------------Reply-----------------------------------------------
On Apr 30, 12:38 pm, MrHelpMe <clintto @hotmail.com> wrote:
> On Apr 30, 12:09 pm, DA Morgan <damor @psoug.org> wrote: > > MrHelpMe wrote:
> > >> Why two procedures and not one?
> > >> --
> > >> Daniel A. Morgan
> > >> University of Washington
> > >> damor@x.washington.edu
> > >> (replace x with u to respond)
> > >> Puget Sound Oracle Users Groupwww.psoug.org-Hidequoted text -
> > >>
> > > Hi DA Morgan,
> > > Thanks for the reply. I used 2 procedures originally because I wasn't
> > > sure how to accomplish this in 1 procedure. To be honest I just
> > > managed to figure this out and it looks like it is definitely working
> > > now, however, I need your assistance and ask you and other experts
> > > what I can do in the below situation.
> > > Now that I have the insert working, how can I say the following. Upon
> > > insert if the FirstName, LastName and Email address are the same do
> > > not insert into the Users table but insert into the other 2 tables all
> > > the info. plus the appropriate user id.
> > What you are asking is unclear but look at the Merge Statement if the
> > choice in a single table is insert/update. Look at the INSERT FIRST,
> > INSERT ALL, and INSERT WHEN statements.
> > Also consider doing a SELECT COUNT(*) to determine if the record has
> > already been inserted.
> > You can find all of these in Morgan's Library atwww.psoug.org.
> > Look up MERGE and INSERT.
> > --
> > Daniel A. Morgan
> > University of Washington
> > damor@x.washington.edu
> > (replace x with u to respond)
> > Puget Sound Oracle Users Groupwww.psoug.org-Hide quoted text -
> >
> Sorry DA Morgan let me clarify. I'm not sure how much you know about
> front end coding but I will keep it simple. I have an ASP page that
> when the user clicks on the page he is asked a serieds of question.
> Like what is your first name, what is your last name, what is your
> email adress, what kind of equipment do you want, for what purpose
> etc. After the user fills in all this information he/she will click a
> submit button. When they click the submit button, behind the scenes I
> do an insert into(based on the insert statement that I showed you
> above) into the users table, hardware table and approver table. Now
> if Joe Smith fills out this form and then clicks submit and then comes
> to the page again, fills out the form again and clicks submit, well I
> would not want to insert his name twice into the users table but
> rather have unique users being captured in this table. So therefore,
> how do I code this in the backend to say if the user is not unique
> don't insert the entry into the users table but all the other info.
> that he/she submitted will be inserted into the respective tables. I
> hope this is clear. Please let me know if it is not and in the mean
> time I will have a look at what you mentioned
Such is the folly of ID columns as the primary key. Change your data model!
-----------------------------------------------Reply-----------------------------------------------
On Apr 30, 3:08 pm, Ed Prochak <edproc @gmail.com> wrote:
> On Apr 30, 12:38 pm, MrHelpMe <clintto @hotmail.com> wrote: > > On Apr 30, 12:09 pm, DA Morgan <damor@psoug.org> wrote:
> > > MrHelpMe wrote:
> > > >> Why two procedures and not one?
> > > >> --
> > > >> Daniel A. Morgan
> > > >> University of Washington
> > > >> damor@x.washington.edu
> > > >> (replace x with u to respond)
> > > >> Puget Sound Oracle Users Groupwww.psoug.org-Hidequotedtext -
> > > >>
> > > > Hi DA Morgan,
> > > > Thanks for the reply. I used 2 procedures originally because I wasn't
> > > > sure how to accomplish this in 1 procedure. To be honest I just
> > > > managed to figure this out and it looks like it is definitely working
> > > > now, however, I need your assistance and ask you and other experts
> > > > what I can do in the below situation.
> > > > Now that I have the insert working, how can I say the following. Upon
> > > > insert if the FirstName, LastName and Email address are the same do
> > > > not insert into the Users table but insert into the other 2 tables all
> > > > the info. plus the appropriate user id.
> > > What you are asking is unclear but look at the Merge Statement if the
> > > choice in a single table is insert/update. Look at the INSERT FIRST,
> > > INSERT ALL, and INSERT WHEN statements.
> > > Also consider doing a SELECT COUNT(*) to determine if the record has
> > > already been inserted.
> > > You can find all of these in Morgan's Library atwww.psoug.org.
> > > Look up MERGE and INSERT.
> > > --
> > > Daniel A. Morgan
> > > University of Washington
> > > damor@x.washington.edu
> > > (replace x with u to respond)
> > > Puget Sound Oracle Users Groupwww.psoug.org-Hidequoted text -
> > >
> > Sorry DA Morgan let me clarify. I'm not sure how much you know about
> > front end coding but I will keep it simple. I have an ASP page that
> > when the user clicks on the page he is asked a serieds of question.
> > Like what is your first name, what is your last name, what is your
> > email adress, what kind of equipment do you want, for what purpose
> > etc. After the user fills in all this information he/she will click a
> > submit button. When they click the submit button, behind the scenes I
> > do an insert into(based on the insert statement that I showed you
> > above) into the users table, hardware table and approver table. Now
> > if Joe Smith fills out this form and then clicks submit and then comes
> > to the page again, fills out the form again and clicks submit, well I
> > would not want to insert his name twice into the users table but
> > rather have unique users being captured in this table. So therefore,
> > how do I code this in the backend to say if the user is not unique
> > don't insert the entry into the users table but all the other info.
> > that he/she submitted will be inserted into the respective tables. I
> > hope this is clear. Please let me know if it is not and in the mean
> > time I will have a look at what you mentioned
> Such is the folly of ID columns as the primary key.
> Change your data model!- Hide quoted text -
>
Sorry guys I am still a bit confused. I was hoping for my primary key to be the next value in oracle. This is an auto incremented number. DA morgan what would the select count(*) do for me and where would I include this(before the insert). Sorry I am just a bit confused as I have never done this before. Ed Prochak could you give me a sample idea of the data model. I have
no problem in changin it as I want to build this application right.
Sorry just confused on the answers. One is saying to change the data
model and the other to add a select count.
-----------------------------------------------Reply-----------------------------------------------
MrHelpMe wrote: > On Apr 30, 3:08 pm, Ed Prochak <edproc @gmail.com> wrote: >> On Apr 30, 12:38 pm, MrHelpMe <clintto @hotmail.com> wrote: >>> On Apr 30, 12:09 pm, DA Morgan <damor@psoug.org> wrote:
>>>> MrHelpMe wrote:
>>>>>> Why two procedures and not one?
>>>>>> --
>>>>>> Daniel A. Morgan
>>>>>> University of Washington
>>>>>> damor@x.washington.edu
>>>>>> (replace x with u to respond)
>>>>>> Puget Sound Oracle Users Groupwww.psoug.org-Hidequotedtext -
>>>>>>
>>>>> Hi DA Morgan,
>>>>> Thanks for the reply. I used 2 procedures originally because I wasn't
>>>>> sure how to accomplish this in 1 procedure. To be honest I just
>>>>> managed to figure this out and it looks like it is definitely working
>>>>> now, however, I need your assistance and ask you and other experts
>>>>> what I can do in the below situation.
>>>>> Now that I have the insert working, how can I say the following. Upon
>>>>> insert if the FirstName, LastName and Email address are the same do
>>>>> not insert into the Users table but insert into the other 2 tables all
>>>>> the info. plus the appropriate user id.
>>>> What you are asking is unclear but look at the Merge Statement if the
>>>> choice in a single table is insert/update. Look at the INSERT FIRST,
>>>> INSERT ALL, and INSERT WHEN statements.
>>>> Also consider doing a SELECT COUNT(*) to determine if the record has
>>>> already been inserted.
>>>> You can find all of these in Morgan's Library atwww.psoug.org.
>>>> Look up MERGE and INSERT.
>>>> --
>>>> Daniel A. Morgan
>>>> University of Washington
>>>> damor@x.washington.edu
>>>> (replace x with u to respond)
>>>> Puget Sound Oracle Users Groupwww.psoug.org-Hidequoted text -
>>>>
>>> Sorry DA Morgan let me clarify. I'm not sure how much you know about
>>> front end coding but I will keep it simple. I have an ASP page that
>>> when the user clicks on the page he is asked a serieds of question.
>>> Like what is your first name, what is your last name, what is your
>>> email adress, what kind of equipment do you want, for what purpose
>>> etc. After the user fills in all this information he/she will click a
>>> submit button. When they click the submit button, behind the scenes I
>>> do an insert into(based on the insert statement that I showed you
>>> above) into the users table, hardware table and approver table. Now
>>> if Joe Smith fills out this form and then clicks submit and then comes
>>> to the page again, fills out the form again and clicks submit, well I
>>> would not want to insert his name twice into the users table but
>>> rather have unique users being captured in this table. So therefore,
>>> how do I code this in the backend to say if the user is not unique
>>> don't insert the entry into the users table but all the other info.
>>> that he/she submitted will be inserted into the respective tables. I
>>> hope this is clear. Please let me know if it is not and in the mean
>>> time I will have a look at what you mentioned
>> Such is the folly of ID columns as the primary key.
>> Change your data model!- Hide quoted text -
>>
> Sorry guys I am still a bit confused. I was hoping for my primary key
> to be the next value in oracle.
There is no such concept in relational databases. > This is an auto incremented number.
These kudges exist in some products but not Oracle. > DA morgan what would the select count(*) do for me and where would I
> include this(before the insert). Sorry I am just a bit confused as I
> have never done this before.
I would suggest you push back from the keyboard and taking a beginning class in relational databases. I don't mean to be rude here but if you have to ask that question you
are just not qualified to do the work indicated.
Assuming you are, in fact, a student here is the answer:
If the count is zero then no corresponding record exists. If the count
is one then one record already exists. If the count is two or more you
have a violation of your stated business rule.
If you are a student you need to say that. Otherwise you really need
to hand this project to someone qualified to do it. Right now you
look like an bartender trying to design a suspension bridge.
--
Daniel A. Morgan
University of Washington
damor@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
-----------------------------------------------Reply-----------------------------------------------
On May 1, 4:42 am, MrHelpMe <clintto @hotmail.com> wrote:
> On Apr 30, 3:08 pm, Ed Prochak <edproc @gmail.com> wrote: > > On Apr 30, 12:38 pm, MrHelpMe <clintto @hotmail.com> wrote: [] > > > Sorry DA Morgan let me clarify. I'm not sure how much you know about
> > > front end coding but I will keep it simple. I have an ASP page that
> > > when the user clicks on the page he is asked a serieds of question.
> > > Like what is your first name, what is your last name, what is your
> > > email adress, what kind of equipment do you want, for what purpose
> > > etc. After the user fills in all this information he/she will click a
> > > submit button. When they click the submit button, behind the scenes I
> > > do an insert into(based on the insert statement that I showed you
> > > above) into the users table, hardware table and approver table. Now
> > > if Joe Smith fills out this form and then clicks submit and then comes
> > > to the page again, fills out the form again and clicks submit, well I
> > > would not want to insert his name twice into the users table but
> > > rather have unique users being captured in this table. So therefore,
> > > how do I code this in the backend to say if the user is not unique
> > > don't insert the entry into the users table but all the other info.
> > > that he/she submitted will be inserted into the respective tables. I
> > > hope this is clear. Please let me know if it is not and in the mean
> > > time I will have a look at what you mentioned
> > Such is the folly of ID columns as the primary key.
> > Change your data model!
> Sorry guys I am still a bit confused. I was hoping for my primary key
> to be the next value in oracle. This is an auto incremented number.
> DA morgan what would the select count(*) do for me and where would I
> include this(before the insert). Sorry I am just a bit confused as I
> have never done this before.
> Ed Prochak could you give me a sample idea of the data model. I have
> no problem in changin it as I want to build this application right.
> Sorry just confused on the answers. One is saying to change the data
> model and the other to add a select count.
Daniel has addressed the question of using count(*). I just will add a little about the datamodel. Try to remember this
phrase:
the key, the whole key, and nothing but the key, so help me Codd.
There comes a point where a pseudokey is useful, but new DB developers
often rush to using one rather than going thru the work necessary to
identify a true Primary Key. Take a step back from tables and columns.
Go back to a higher, logical layer, like an ERD. Ask yourself: exactly
what entity am I modelling here? what makes it uniquely identifiable
(IOW what are its key attributes). For example your User table has
both name and email attributes. Can a user of your system have more
than one email address? If so, are they treated as different users?
Then maybe email is the Primary Key (PK). Or is the user a single
person? then maybe firstname/lastname forms the PK. Or based on one of
your comments,
>>>>> ... if the FirstName, LastName and Email address are the same do
>>>>> not insert into the Users table ", then those three form the PK.
Once you have a proper PK then you will never have duplicates. You don't need User_ID to be the PK. But if you use a pseudokey, like "ID", then you might get duplicates
easily. Not all agree with me, but I claim that a data model like
yours, converts a powerful Relational Model DBMS into a Network Model
DBMS. You abandon the power of the database in favor of maintaining
the connections between entities in the application code.
So as Daniel suggests, get some on-site help from a more experienced
developer. Talk to your DBA. If appropriate, take a class on
relational database design. (If you happen to be in the Northeast Ohio
area, I might be available to help a little. send me an email if it
seems reasonable.)
Daniel and I are not being rude. We are sincerely trying to help you
to fish.
Ed
|
 |
 |
 |
 |
|
Add to del.icio.us |
Digg this |
Stumble it |
Powered by Megasolutions Inc