Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2003
    Posts
    7

    Unanswered: Create child table records automatically??

    I guess I forgot my foreign key 101 lesson. I have a table with a primary key. I set up another table and added the foreign key that makes the dependency on the first table. I want a record to appear in the second table with my default values when I create a record in the first. When I add a record through Ent Manager this isn't happening. What am I missing? thanks

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What's a default child key?

    Can you post the DDL?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2003
    Posts
    7
    What I meant was the other fields will have default values. The key field will take the value of the parent table.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Then that's not a 1 to many relationship, that's 1 to 1.

    The child would have to have it's own unique value, in addition to the parents keys.

    Called a composite key....

    Can you post the ddl...

    and a trigger would do what you want, but I'm suspicious of your design...

    What's it for?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2003
    Posts
    7
    Yes I want a 1-1. I have one DB that because of the text fields it is near the max record length. I need to pull in other related info about the product depending on what page I'm displaying it on. These are my test DBs below. Thumbstrailer is my child and no I'm not a pro with SQL. I'm a humble ASP coder looking for friendly advice. I want to eventually use a JOIN and pull in a dozen fields from the first with all the fields from the second. Thanks

    CREATE TABLE [dbo].[thumbstrailer] (
    [th_key] [int] NOT NULL ,
    [th_thumbb] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [th_thumbc] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [th_thumbd] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [th_thumbe] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [th_thumbf] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [th_thumbg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [th_thumbh] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[thumbstrailer] WITH NOCHECK ADD
    CONSTRAINT [DF_thumbstrailer_th_thumbb] DEFAULT ('N') FOR [th_thumbb],
    CONSTRAINT [DF_thumbstrailer_th_thumbc] DEFAULT ('N') FOR [th_thumbc],
    CONSTRAINT [DF_thumbstrailer_th_thumbd] DEFAULT ('N') FOR [th_thumbd],
    CONSTRAINT [DF_thumbstrailer_th_thumbe] DEFAULT ('N') FOR [th_thumbe],
    CONSTRAINT [DF_thumbstrailer_th_thumbf] DEFAULT ('N') FOR [th_thumbf],
    CONSTRAINT [DF_thumbstrailer_th_thumbg] DEFAULT ('N') FOR [th_thumbg],
    CONSTRAINT [DF_thumbstrailer_th_thumbh] DEFAULT ('N') FOR [th_thumbh],
    CONSTRAINT [PK_thumbstrailer] PRIMARY KEY CLUSTERED
    (
    [th_key]
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[thumbstrailer] ADD
    CONSTRAINT [FK_thumbstrailer_forums] FOREIGN KEY
    (
    [th_key]
    ) REFERENCES [HORSETRA].[forums] (
    [forum_id]
    ) ON UPDATE CASCADE
    GO

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK...

    CREATE TRIGGER CHILD_INS ON ParentTable
    FOR INSERT
    AS
    BEGIN

    INSERT INTO ChildTable (Col1, col2, ect....
    SELECT Col1, Col2, ect FROM inserted

    END

    GO

    something like that..
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Nov 2003
    Posts
    7
    One more thing. How do I get the value of the parent table primary key into the key of the second table.

    CREATE TRIGGER [thumbsupdate] ON [HORSETRA].[forums]
    FOR INSERT, UPDATE
    AS
    BEGIN

    INSERT INTO thumbstrailer (th_key, th_time) values (???, getdate())

    END

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by rheine
    One more thing. How do I get the value of the parent table primary key into the key of the second table.

    CREATE TRIGGER [thumbsupdate] ON [HORSETRA].[forums]
    FOR INSERT, UPDATE
    AS
    BEGIN

    INSERT INTO thumbstrailer (th_key, th_time) values (???, getdate())

    END
    It doesn't work that way.

    There are 2 virtual tables called inserted and deleted.

    sooo..

    It's like this:

    INSERT INTO thumbstrailer (th_key, th_time)
    SELECT forum_id, getdate() FROM inserted

    EDIT: You don't want to do this for an UPDATE, you get a dup key

    Maybe you want to do a separate one if you want to update the time...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Nov 2003
    Posts
    7

    Works

    That works like a charm. Since it's 1-1 I think Cascade delete would be usefule here.
    Thanks again. RH

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Interesting idea...

    What has more overhead....a delete trigger or cascading deletes?

    Never use cascading...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •