January 5, 2011 at 2:19 am
Hi,
I am having 2 tables namely Table1 and Table 2
Table 1
ID PK
Name
Desc
Table2
ChildID ->FK to ID in Table1
ParentID->FK to ID in Table1
All the main details shall be available in Table 1
and for each ID in Table 1 there may be Childrens in Table 2
These Childrens may again have Childrens
Table 1
0
1
2
Table2
ChildID ParentID
1 0
2 1
I want to delete an ID 0 from Table 1 and delete the Childrens(1) of ID 0 from table 2 ,subchildrens of children(1) that is 2 from table 2 and finally delete all the childrens details available in Table 1
I wrote a Instead of delete trigger on Table1 and Table2
Trigger on Table 1
Delete ChildID from Table 2 where ParentID in (Select ID from Deleted)
Delete ID from Table 1 where (Select ID from deleted)
Trigger on Table 2
delete dbo.Table2 where ChildID IN (select ChildID from deleted);
delete dbo.Table1 where ID in (select ChillID from deleted);
But i am getting a error as
Maximum stored procedure, function, trigger or view nesting level exceeded(32)
Could any one help me with this
January 5, 2011 at 2:36 am
You're looking to recursion a cascade delete? Yikes.
Does this HAVE to be a trigger? Can you do this from a called proc level or are you doing this for data integrity because you get dynamic SQL?
If you can do this from the proc level, look into using a recursive CTE to populate a temp table with the values for all the nested children levels, then return and do a single pass delete.
If you can't... I ... eesh. I don't know. There's a limited to the # of nested calls that can occur, and you're hitting it because of the deletes calling deletes.
I'd have to experiment, I'm not sure if Foreign Key Cascading Deletes will handle something like this.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 3, 2011 at 3:47 am
try this for date experiance,
--By Rajat Bhalla
ALTER FUNCTION [dbo].[fnGetDateDiffAsYMD] (@FromDate AS DateTime,@ToDate AS DATETIME)
--Year,Date and Month section modified By ---------------Rajat Bhalla--------------------
RETURNS VARCHAR(30)
AS
BEGIN
DECLARE @date datetime,
@tmpdate datetime,
@years int,
@months int,
@days int,
@exp varchar(30),
@mm int,
@experiance datetime
if (datediff(dd,@FromDate ,@ToDate)< 0) or (@FromDate='') or (@ToDate is null)
select @exp ='Invalid joining date'
else
begin
select @experiance=Dateadd(yy,Datediff(yy,@FromDate,@ToDate),@fromDate)
select @years=Datediff(yy,@FromDate,@ToDate) - (CASE
WHEN @experiance > @ToDate THEN 1
ELSE 0
END)
select @months=Month(@ToDate - @experiance) -1
select @days = Day(@ToDate - @experiance) - 1
if @years<=0 and @months<=0 and @days<=0
set @exp = '0';
else if @years<=0
begin
if @months>0
begin
if @days>0
begin
if @months>1
begin
if @days>1
set @exp= CAST(@months as varchar) + ' months' +' and ' + CAST(@days as varchar) + ' Days'
else if @days=1
set @exp= CAST(@months as varchar) + ' months' +' and ' + CAST(@days as varchar) + ' Day'
end
else if @months=1
begin
if @days>1
set @exp= CAST(@months as varchar) + ' month' +' and ' + CAST(@days as varchar) + ' Days'
else if @days=1
set @exp= CAST(@months as varchar) + ' month' +' and ' + CAST(@days as varchar) + ' Day'
end
end
else if @days<=0
begin
if @months>1
set @exp= CAST(@months as varchar) + ' months'
else if @months=1
set @exp= CAST(@months as varchar) + ' month'
end
end
else if @months<=0
if @days>1
set @exp = CAST(@days as varchar) + ' Days'
else if @days=1
set @exp = CAST(@days as varchar) + ' Day'
end
else if @years>0 and @months>0 and @days>0
begin
if @years>1
begin
if @months>1
begin
if @days>1
set @exp= CAST(@years as varchar) +' Years' + ', '+ CAST(@months as varchar) + ' months' +' and ' + CAST(@days as varchar) + ' Days'
else if @days=1
set @exp= CAST(@years as varchar) +' Years' + ', '+ CAST(@months as varchar) + ' months' +' and ' + CAST(@days as varchar) + ' Day'
end
else if @months=1
begin
if @days>1
set @exp= CAST(@years as varchar) +' Years' + ', '+ CAST(@months as varchar) + ' month' +' and ' + CAST(@days as varchar) + ' Days'
else if @days=1
set @exp= CAST(@years as varchar) +' Years' + ', '+ CAST(@months as varchar) + ' month' +' and ' + CAST(@days as varchar) + ' Day'
end
end
else if @years=1
begin
if @months>1
begin
if @days>1
set @exp= CAST(@years as varchar) +' Year' + ', '+ CAST(@months as varchar) + ' months ' +'and ' + CAST(@days as varchar) + ' Days'
else if @days=1
set @exp= CAST(@years as varchar) +' Year' + ', '+ CAST(@months as varchar) + ' months ' +'and ' + CAST(@days as varchar) + ' Day'
end
else if @months=1
begin
if @days>1
set @exp= CAST(@years as varchar) +' Year' + ', '+ CAST(@months as varchar) + ' month ' +'and ' + CAST(@days as varchar) + ' Days'
else if @days=1
set @exp= CAST(@years as varchar) +' Year' + ', '+ CAST(@months as varchar) + ' month ' +'and ' + CAST(@days as varchar) + ' Day'
end
end
end
else if @years>0 and @days>0 and @months<=0
begin
if(@years>1)
begin
if(@days>1)
set @exp = CAST(@years as varchar) +' Years' +' and ' + CAST(@days as varchar) + ' Days'
else if(@days=1)
set @exp = CAST(@years as varchar) +' Years' +' and ' + CAST(@days as varchar) + ' Day'
end
else if(@years=1)
begin
if(@days>1)
set @exp = CAST(@years as varchar) +' Year' +' and ' + CAST(@days as varchar) + ' Days'
else if(@days=1)
set @exp = CAST(@years as varchar) +' Year' +' and ' + CAST(@days as varchar) + ' Day'
end
end
else if @years>0 and @days<=0 and @months<=0
begin
if @years>1
set @exp = CAST(@years as varchar) +' Years'
else if @years=1
set @exp = CAST(@years as varchar) +' Year'
end
else if @years>0 and @days<=0 and @months>0
begin
if @years>1
begin
if @months>1
set @exp = CAST(@years as varchar) +' Years' +' and ' + cast(@months as varchar) + ' months'
else if @months=1
set @exp = CAST(@years as varchar) +' Years' +' and ' + cast(@months as varchar) + ' month'
end
else if @years=1
begin
if @months>1
set @exp = CAST(@years as varchar) +' Year' +' and ' + cast(@months as varchar) + ' months'
else if @months=1
set @exp = CAST(@years as varchar) +' Year' +' and ' + cast(@months as varchar) + ' month'
end
end
end
return @exp
END
February 3, 2011 at 4:09 am
Hi Guys,
I finally got it through a simple CTE expression and inserting the data into a temp variable and finally deleting the ids present in the temp variable.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply