Professional Web Applications Themes

Cascade Deletes - Microsoft SQL / MS SQL Server

PROBLEM: The "deleted" table is empty when a child table's delete trigger fires EXPECTED: The deleted table to contain all the rows from the DB that are being deleted EXPLANATION: If I have two tables (Orders and OrderItems) [Orders:] OrderID OrderDate [OrderItems:] OrderItemID OrderID OrderItemDescription Order Quanity etc... Orders has the cascade delete option set on the OrderItems.OrderID foreign key When I delete and Order the delete trigger fires on the Order table and works as exprected. On cascade the OrderItem table delete trigger fires, but the deleted table is not populated with anything. I need this information... What am ...

  1. #1

    Default Cascade Deletes

    PROBLEM:
    The "deleted" table is empty when a child table's delete trigger fires

    EXPECTED:
    The deleted table to contain all the rows from the DB that are being deleted

    EXPLANATION:
    If I have two tables (Orders and OrderItems)

    [Orders:]
    OrderID
    OrderDate


    [OrderItems:]
    OrderItemID
    OrderID
    OrderItemDescription
    Order Quanity
    etc...

    Orders has the cascade delete option set on the OrderItems.OrderID foreign
    key


    When I delete and Order the delete trigger fires on the Order table and
    works as exprected. On cascade the OrderItem table delete trigger fires,
    but the deleted table is not populated with anything. I need this
    information...

    What am I doing wrong?


    Thanks.....


    msnews.microsoft.com Guest

  2. #2

    Default Re: Cascade Deletes

    Can you post relevant information (DDLs, sample data, code etc..) which can
    allow others to reproduce your problem?

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

  3. #3

    Default Re: Cascade Deletes

    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are. Is this what you wrote?

    CREATE TABLE Orders
    (order_id INTEGER NOT NULL PRIMARY KEY,
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
    .. );

    CREATE TABLE OrderItems
    (order_id INTEGER NOT NULL
    REFERENCES Orders(order_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    item_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    ..);
     [/ref]
    foreign key <<

    Unh? That would be problem since Orders is the referenced table, not
    the referencing table. Orders have details, details do not have orders.

    --CELKO--


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

Similar Threads

  1. Dropdownlist Cascade
    By JimmyB4B in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 1
    Last Post: December 18th, 04:50 AM
  2. possible to DELETE CASCADE?
    By Miles Keaton in forum PostgreSQL / PGSQL
    Replies: 5
    Last Post: December 30th, 10:42 PM
  3. A "cascade on delete" constraints deletes AFTER the source is gone??
    By Vitaly Belman in forum PostgreSQL / PGSQL
    Replies: 5
    Last Post: December 20th, 05:36 PM
  4. Perform cascade delete
    By basidati in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 7th, 09:53 PM
  5. cascade update non-primary key field
    By Calvin in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 2nd, 01:37 PM

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