How do I create a composite sequential child id based upon the parent id?

From: Brian Lavender <blavender_at_spk.usace.army.mil>
Date: Wed, 30 Jun 1999 23:51:28 GMT
Message-ID: <7leahs$tv5$1@nnrp1.deja.com>

I want to make detail table line items so they have a composite key based upon the parentid and the next childid for that parentid.

SQL> describe parent

 Name                            Null?    Type
 ------------------------------- -------- ----
 PARENTID                                 NUMBER
 FIRST                                    VARCHAR2(8)
 LAST                                     VARCHAR2(8)
 SEX                                      VARCHAR2(1)

SQL> describe children
 Name                            Null?    Type
 ------------------------------- -------- ----
 CHILDID                                  NUMBER
 PARENTID                                 NUMBER
 FIRST                                    VARCHAR2(8)
 LAST                                     VARCHAR2(8)
 SEX                                      VARCHAR2(1)

Say I have PARENTID 432 and I have childid 1 and childid 2. How do I create a trigger so when a child is inserted with the parentid it automaitcally inserts the next childid?

Here is the sample data

parent

---

1 smith larry M
2 bunker fred M

children

---

1 1 smith billy M
2 1 smith angel F
1 2 bunker sally F
2 2 bunker frank M
3 1 smith andy M
^ How do I create a trigger to insert the next child number based upon the number of children for that parent already in the children table?

brian
--
Brian E. Lavender
US Army Corps of Engineers -- Programmer / Systems Analyst Sacramento, CA (916) 557-6623

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Wed Jun 30 1999 - 18:51:28 CDT