URGENT Stored Procedure-deleting parent & children

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

URGENT Stored Procedure-deleting parent & children

Author

Topic

sarah_shirin
Starting Member

5 Posts

Posted - 2002-08-05 : 08:06:45

hi guys,

I am a newbie to stored procedures....

Scenario:::
I ahve a parent task p1, which has 2 children c1 and c2. c1 inturn has 2 children g1 and g2. The tree can go on...

Now If i delete p1, all teh children and grand children for that p1 (upto its root) should get deleted.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-05 : 08:08:08

You'll have to post your table(s) structure(s) before anyone can help you write a procedure. Also include some sample data.

sarah_shirin
Starting Member

5 Posts

Posted - 2002-08-05 : 10:30:33

I have three tables, TASK, TASKEMP and TASKRELATION.
TASK table has all the tasks and other major task related details, TASKEMP has other details of tasks stored in them while TASKRELATION table has 2 fields called as parentid and taskid... this table has the relation ship. If suppose there is no parent for a particular task then the parentid in the TASKRELATION table will be -1.

I should pass the task id as the parameter for my stored Proc.

Now when I delete a particular task, a field tr_delete should be equal to 1, in TASK and TASKEMP table.

Hope this is sufficient.

Thanking you in advance

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-05 : 10:39:29

quote:
Hope this is sufficient.

Unfortunately it isn't. Post the exact DDL (CREATE TABLE statement) for each of the three tables. And POST SOME SAMPLE DATA, say about 10 rows for each table. It helps to be able to visualize exactly what the data looks like, and unfortunately the information you've provided doesn't do that.

r937
Posting Yak Master

112 Posts

Posted - 2002-08-05 : 11:47:42

quote:
If i delete p1, all teh children and grand children for that p1 (upto its root) should get deleted

sounds a lot like ON DELETE CASCADE

do your tables have foreign keys defined, sarah?

you may not need a stored proc at all

quote:
when I delete a particular task, a field tr_delete should be equal to 1, in TASK and TASKEMP table.

okay, maybe you do... that's not an actual deletion, is it

rudy
http://rudy.ca/

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-08-05 : 12:16:48

#1. Can each task only have one parent? If so, why do you need a TASKRELATION table, if not do all parents need to be deleted before any child can be deleted.

#2. We need the DDL to help you out. Right click on the database in SQL Enterprise Manager and do the following All Tasks -> Generate SQL Script...

Got SQL?

sarah_shirin
Starting Member

5 Posts

Posted - 2002-08-09 : 05:59:49

hello,

I thank all u guys who responded to my query....I am sorry that I jsut couldn't reply back to the clarifications that u guys asked for... Was a little under pressure to perform :)

In any case.. I solved this problem of mine by writing two functions... I am psting it here ..jsut incase it can be of any help to others or if u guys have improvements or suggestions.... I would gladly do it.. so please do let me know on this id of mine
sarah_shirin@hotmail.com

-- Entry by Sarah to Delete a Task with all its subtasks. 2 functions GETCHILDTASKIDS and GETALLTASKIDS

FUNCTION GETCHILDTASKIDS (vTaskID varchar2)
RETURN varchar2 IS
Search_Result pmp_returntypes.Search_Result;
search_res_rec NUMBER(18);
cDRID NUMBER;
nChilTaskID NUMBER;
nCTaskID NUMBER;
tskIDs VARCHAR2(100);
flag NUMBER;
The_Query VARCHAR2(3000);
Select_Clause VARCHAR2(1000);
Constant_Clause VARCHAR2(1000);
End_Clause VARCHAR2(1000);
Search_Result1 VARCHAR2(3000);
err_num NUMBER;
err_msg varchar2(150);
BEGIN
select_clause := 'select tr_id from w2w_taskrelation where tr_parentid in (';
constant_clause := vTaskID;
end_Clause := ')';
flag := 0;
tskIDs := '';

the_query := Select_Clause || Constant_Clause || End_Clause;
OPEN Search_Result FOR The_Query;
LOOP
fetch search_result into search_res_rec;
exit when search_result%NOTFOUND;
flag := flag + 1;
IF(flag = 1) THEN
tskIDs := to_char(search_res_rec);
Else
tskIDs := tskIDs ||','|| to_char(search_res_rec);
End if;
dbms_output.put_line(search_res_rec);

END LOOP;
RETURN (tskIDs);
EXCEPTION
WHEN OTHERS THEN
err_num := SQLCODE;
err_msg := SUBSTR(SQLERRM, 1, 100);
INSERT INTO errors VALUES (err_num, err_msg);
commit;
END;

FUNCTION GETALLTASKIDS (vTaskID varchar2)
RETURN varchar2 IS
tskIDs Varchar2(3000);
chtskIDs Varchar2(3000);
fin_tskIDs Varchar2(3000);
Update_Clause1 Varchar2(3000);
Update_Clause2 Varchar2(3000);
Constant_Clause Varchar2(3000);
End_Clause Varchar2(1000);
The_Query Varchar2(4000);
BEGIN
tskIDs := GETCHILDTASKIDS(vTaskID);
IF (tskIDs is not null) THEN
fin_tskIDs := vTaskID||','||tskIDs;
ELSE
fin_tskIDs := vTaskID;
END IF;

While (tskIDs is not null)
LOOP
tskIDs := GETCHILDTASKIDS(tskIDs);

IF (tskIDs is not null) THEN
fin_tskIDs := fin_tskIDs ||',' || tskIDs;
ELSE
fin_tskIDs := fin_tskIDs;
END IF;

END LOOP;
-- This is soft delete of the tasks, fields TS_DELETEDAND TSER_DELETED
-- WILL BE SET TO 1 FROM 0 IN W2W_TASKS AND W2W_TASKEMPROLE.

Update_Clause1 := 'UPDATE W2W_TASKS SET TS_DELETED=1 WHERE TS_ID IN (';
constant_clause := fin_tskIDs;
End_Clause := ')';
The_Query := Update_Clause1 || Constant_Clause || End_Clause;
EXECUTE IMMEDIATE The_Query ;
Update_Clause2 := 'UPDATE W2W_TASKEMPROLE SET TSER_DELETED=1 WHERE TSER_TASKID IN (';
constant_clause := fin_tskIDs;
End_Clause := ')';
The_Query := Update_Clause2 || Constant_Clause || End_Clause;
EXECUTE IMMEDIATE The_Query;
COMMIT;
RETURN (fin_tskIDs);

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
ROLLBACK;
END;

rihardh
Constraint Violating Yak Guru

307 Posts

Posted - 2002-08-09 : 07:38:34

Like Rudy said: "Smells like CASCADE DELETE RELATED RECORDS!"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources