Post reply

HELP

  • August 27, 2006 at 5:59 am

    #170000

     

    hello all
     
    urgent
     
    With relationships defined between tables, is there a way to know if it is safe to delete a row, i.e. that even though the table is related to other tables, this row is NOT used.

  • August 28, 2006 at 4:18 am

    #657037

    You can use EXISTS keyword.

    N 56°04'39.16"
    E 12°55'05.25"

  • August 28, 2006 at 11:38 pm

    #657230

    could you give me more details

    thank you

  • August 28, 2006 at 11:43 pm

    #657231

    IF NOT EXISTS (SELECT * FROM OtherTable WHERE ParentIdCol = SomeValue)

        DELETE FROM FirstTable WHERE SomeIDCol = SomeValue

     

    N 56°04'39.16"
    E 12°55'05.25"

  • August 29, 2006 at 2:00 am

    #657234

    thank you but

    I want to check automaticaly to see if that record used in other tables and alert the user with a user friendly message

    since i have 60 table that has a foreign key from one table

    I can not rememper them all

     

  • August 29, 2006 at 9:43 am

    #657296

    Use an instead of delete trigger.

  • August 31, 2006 at 3:46 am

    #657757

    please give me more details or a reference

  • September 1, 2006 at 1:45 pm

    #658143

    I presume that the other tables have foreign keys on this table. Are the cascade deletes implemented to automatically delete the referenced rows and you just want to tell them that they might be deleting all this other data too?

    If not and you just want your application to report a friendly message, you can implement an exception handler, check to see whether your message has a "foreign key constraint" (or some other such text) and provide a friendly error instead.

  • September 2, 2006 at 1:00 am

    #658165

    sory for my too much questions

    the system is multilingual system and I want to display the error message in the proper langauge

    cascade delete is not implemented here

  • September 3, 2006 at 2:05 pm

    #658228

    What I usually do is have two delete procedures: one that tries and deletes only the row from the referenced table (usp_User_Delete)  and another called usp_User_CascadeDelete which deletes all the referenced rows as well (you'll have a lot of typing to do for the 60 or so tables if you don't have cascade deletes implemented). In your code, place a try catch around your code (if the language supports it), and if the error message code or text indicates that the error was due to referential constraints send a user friendly error to the ui. You can ask them to confirm if you want to delete all the other data too and if so call the cascadedelete stored procedure.

    As far as language support this is something that is done at the application level and depends on the language settings.

Viewing 10 posts - 1 through 10 (of 10 total)

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