May 4, 2006 at 8:55 am
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
Yes you can do this. Do it just as you wrote it, perhaps using a parameter instead of the scalar value 100.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 5, 2006 at 9:03 am
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