Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    34

    Unanswered: How do I delete the info in this table?

    Hi,

    I have 3 tables, Clients, Own and Dogs

    Clients has client_id as the primary key
    Dogs has dog_id as the primary key
    Own is the relationship between the two tables, it has client_id and dog_id as Foreign Keys

    If I delete a client I want all of the corresponding entries in the Own and Dogs tables to be deleted aswell.

    If I delete a dog I want the corresponding entries to be deleted from the Own table but not the Clients table.

    I am using ON CASCADE DELETE on both the foreign keys in the Own table, this deletes the entries out of the Own table both ways. I tried to use a trigger on the client table ON DELETE to delete the Dogs table entries, but with the ON CASCADE DELETE deleting the entries out of the Own relationship table I have no way of matching the client_id to the dog_id.

    I hope this makes sense, please help!

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    My suggestion, - do not rely on declarative propagation of data changes. Have a stored procedure(-s) that will do that for you. GRANT EXECUTE on that (those) stored procedure(-s) to appropriate parties and lock out the underlying tables from being accessed directly (I'd say including SELECT's).

  3. #3
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    You could take off the ON CASCADE DELETE and run all your logic in the trigger. You might consider a status change rather than a delete and then periodically dump the flagged entries. Just my .02

    HTH
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

Posting Permissions

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