Post reply

DML delete from stored procedure

  • May 4, 2006 at 8:55 am

    #190480

    Guys,

     

    I have bunch of dml sql statements that I run in particular order like

     

    delete from employee where id < 100

    delete from department where id < 100

    delete from accounts where id < 100

     

    I want this dml statements to run as a part of stored procedure where I pass id as an argument.

     

    execute datatrunc (100)

     

    is it possible to this, any suggestions/inputs regarding how to go about doing it would help.

     

    Thanks

  • May 4, 2006 at 9:51 am

    #636001

    Yes you can do this. Do it just as you wrote it, perhaps using a parameter instead of the scalar value 100.

  • May 5, 2006 at 9:03 am

    #636278

    This looks like an attempt to do a DELETE CASCADE on employee, where the employee's ID (the 100 in the example) appears as a foreign key value in the department and accounts tables.  If this is the case, it would be most advisable to place the three DELETE statements within the scope of a single transaction.  Otherwise, you run the risk of having a one or more "orphaned" departments and/or accounts.

     

    Another approach, depending upon the performance envelope of the application, would be to implement referential integrity rules that force a cascading delete when employee is deleted.

Viewing 3 posts - 1 through 2 (of 2 total)

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