Stored Procedure for deleting Parent and children- Urgent 1

[saiza]

hi guys...

I am a newbie to stored procs... My scenario is like this... I have a Parent P1, which has 2 children C1 and C2.
C1 inturn has 2 child nodes C1G1 and C1G2.. liek this the tree can go on...

If I delete P1, the children C1 and C2 including C1G1 nad C1G2 should also be deleted upto the root....

Can anybody tell me as to how I write the stored Procedure for this...

Thanking In advance... This is Urgent and I would appreciate earliest responses...

Saiza

 

[r937]

i think perhaps you are looking for ON DELETE CASCADE instead of a stored procedure

(hmmm, somebody had a SQL/Server question just like yours today...)

rudy

http://rudy.ca/

 

[saiza]

hi..rudy

I am the same person.... Since it is rather urgent i had to post it on the other forum too.. infact I visited sql forum for the first tiem

It is more like cascade delete.... But i have to write a stored procedure cos i can't do a delete on cascade.

 

[sem]

Try to use START WITH ... CONNECT BY clause

 

[SudhanshuKulshreshth]

You have to write these functions

1.CheckAnyChild()
2.DeleteAnyChild()
3.DeleteParent()

You have to call these functions in a procedure and in a loop by checking if conditions.

I hope this will solve your problem.

 

[karluk]

Why can't you do a "on delete cascade"? SQL Server doesn't support this option, but Oracle does. Why not take advantage of it?

 

[saiza]

hi guys..

I thank all u guys for your valuable suggestions.. .I solved this query of mine by writing two functions... I am pasting it here ...just incase if it is of any help to others and secondly i would like to know if i cna improve upon my code

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;