Professional Web Applications Themes

Cascading Ref Integrity in 7.0 - Microsoft SQL / MS SQL Server

How can I obtain a "cascading referential integrity constraints" in SQL server 7.0? (delete rows with same foreign key) Thanks!...

  1. #1

    Default Cascading Ref Integrity in 7.0

    How can I obtain a "cascading referential integrity
    constraints" in SQL server 7.0? (delete rows with same
    foreign key)

    Thanks!



    Rulle Guest

  2. #2

    Default Re: Cascading Ref Integrity in 7.0

    No can do. This was introduced in SQL2K. You'd have to write triggers for that (and remove the
    needed FK's or disable then as they would do restrict so the trigger won't fire).

    --
    Tibor Karaszi, SQL Server MVP
    Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver


    "Rulle" <com> wrote in message news:0c8601c368b1$86313de0$gbl... 


    Tibor Guest

  3. #3

    Default Re: Cascading Ref Integrity in 7.0

    Rulle,

    Implementing Referential Integrity and Cascading Actions
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_refintegrity.asp

    --
    Dinesh.
    SQL Server FAQ at
    http://www.tkdinesh.com

    "Rulle" <com> wrote in message
    news:0c8601c368b1$86313de0$gbl... 


    Dinesh.T.K Guest

  4. #4

    Default Re: Cascading Ref Integrity in 7.0

    Hi Rulle,

    Cascading referential integrity doesn't exist in SQL Server 7 as it does in
    SQL Server 2000 where you can declare foreign keys with ON DELETE CASCADE.
    you will have to write triggers to accomplish that, for example:

    USE Northwind
    GO
    CREATE TRIGGER trga_u_orders ON ORDERS FOR UPDATE
    AS
    DELETE FROM [order details]
    WHERE EXISTS(SELECT NULL FROM inserted WHERE inserted.orderid = [order
    details].orderid)

    And you have to implement the Foreign Key as a trigger as well, because
    trigger in SQL 7 always fire after the constraints, including Foreign Keys
    have been checked.

    CREATE TRIGGER trga_ui_orders ON [ORDERS details] FOR UPDATE, INSERT
    AS
    IF EXISTS(SELECT NULL FROM inserted i LEFT OUTER JOIN Orders o
    ON o.orderid = i.orderid WHERE o.orderid IS NULL)
    BEGIN
    RAISERROR ('Foreign key violation', 16, 1)
    ROLLBACK TRAN
    RETURN
    END


    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Rulle" <com> wrote in message
    news:0c8601c368b1$86313de0$gbl... 


    Jacco Guest

Similar Threads

  1. Cascading
    By nk_noorkhan in forum Macromedia ColdFusion
    Replies: 0
    Last Post: May 27th, 11:56 AM
  2. Cascading menus
    By Michael in forum Web Design
    Replies: 1
    Last Post: December 18th, 02:21 AM
  3. Cascading repeaters
    By Mikael Engdahl in forum ASP.NET General
    Replies: 1
    Last Post: August 8th, 03:40 PM
  4. cascading menues with asp.net
    By Yavuz Bogazci in forum ASP.NET General
    Replies: 1
    Last Post: July 25th, 01:47 PM
  5. Looking for inspiration: cascading CGI
    By Purl Gurl in forum PERL Miscellaneous
    Replies: 18
    Last Post: June 30th, 02:59 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