Post reply

problem when deleting data in a table

  • June 30, 2009 at 3:29 am

    #207694

    i have 2 tables called COACH and the TEAM.

    TEAM table contains a foreign key to its parent table COACH.

    when i try to delete a tuple from the COACH table which has a reference to the TEAM table it gives an error msg.

    what i want to do is, i should be able to delete a row from the COACH table and after deletion the refering column of the TEAM table should display null.

    i think my problem is clear enough. pls help me if u know...

  • June 30, 2009 at 3:41 am

    #1017428

    I dont think you can do that with DRI , but one solution is :

    Create Trigger COACH_DEL on COACH for delete

    as

    Update TEAMTab

    set Coach = NULL

    from TEAM TEAMTab,

    deleted

    where TEAMTab.Coach = deleted.Coach


    Clear Sky SQL
    My Blog[/url]

  • June 30, 2009 at 4:09 am

    #1017440

    hey smsam1,

    TEAM table contains a foreign key to its parent table COACH.

    How about adding an 'ON DELETE SET NULL' constraint on foreign key column in team table ?

  • June 30, 2009 at 4:09 am

    #1017441

    hey smsam1,

    TEAM table contains a foreign key to its parent table COACH.

    How about adding an 'ON DELETE SET NULL' constraint on foreign key column in team table?

  • June 30, 2009 at 4:09 am

    #1017442

    hey smsam1,

    TEAM table contains a foreign key to its parent table COACH.

    How about adding an 'ON DELETE SET NULL' constraint on foreign key column in team table?

  • June 30, 2009 at 7:05 am

    #1017534

    Dave Ballantyne (6/30/2009)

    I dont think you can do that with DRI , but one solution is :

    xcues me..what is DRI.

    i think i can use on delete set null constraint.

    thank you

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply