Professional Web Applications Themes

DELETED Table - Microsoft SQL / MS SQL Server

Hi, I don't quite understand how the DELETED table works in conjunction with referential integrity. Let's say I have two related tables. I delete a record in the the primary table, hence deleting the related records in my sub table. How does the DELETED process function? Does it create a DELETED table for the primary table and the sub table? Or is there only one DELETED table at all times? If it does create a DELETED table for the primary table and one for the sub table, is there a way to access the values in the different DELETED tables? ...

  1. #1

    Default DELETED Table

    Hi,

    I don't quite understand how the DELETED table works in
    conjunction with referential integrity.

    Let's say I have two related tables. I delete a record in
    the the primary table, hence deleting the related records
    in my sub table. How does the DELETED process function?

    Does it create a DELETED table for the primary table and
    the sub table? Or is there only one DELETED table at all
    times?

    If it does create a DELETED table for the primary table
    and one for the sub table, is there a way to access the
    values in the different DELETED tables?

    TIA,
    Eric
    Eric Guest

  2. #2

    Default Re: DELETED Table

    If you are using triggers, each trigger has access to the deleted virtual table for the particular trigger. IOW, there is a deleted table for your primary table and another for the sub table. The delete trigger on the primary table has access to the deleted table for the primary table and so on.

    If you are using declarative RI (DRI), you can simply create the FK with ON DELETE CASCADE and not have to deal with delete triggers unless you have some specific business requirement.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Eric" <com> wrote in message news:023601c36b3f$deb0db70$gbl...
    Hi,

    I don't quite understand how the DELETED table works in
    conjunction with referential integrity.

    Let's say I have two related tables. I delete a record in
    the the primary table, hence deleting the related records
    in my sub table. How does the DELETED process function?

    Does it create a DELETED table for the primary table and
    the sub table? Or is there only one DELETED table at all
    times?

    If it does create a DELETED table for the primary table
    and one for the sub table, is there a way to access the
    values in the different DELETED tables?

    TIA,
    Eric
    Tom Guest

  3. #3

    Default Re: DELETED Table

    >> I don't quite understand how the DELETED table works in
    conjunction with referential integrity. <<

    It does not work with it at all; the DELETED and INSERTED tables are
    part of the trigger mechanisms in T-SQL. The DRI actions are the
    Standard SQL model for data integrity.

    The rest of your questions apply to proceudral solutions in SQL, and
    surely you do not write code like that!!

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!

    Joe Guest

Similar Threads

  1. Table deleted from data directory
    By vijay in forum MySQL
    Replies: 2
    Last Post: March 13th, 02:43 PM
  2. deleted files reappearing (not really deleted)
    By Mike in forum Windows Server
    Replies: 0
    Last Post: July 13th, 12:50 PM
  3. how many deleted records has a table
    By Ronald in forum Oracle Server
    Replies: 4
    Last Post: October 31st, 08:01 AM
  4. Replies: 2
    Last Post: August 12th, 07:55 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139