Trigger en cascada pq no funciona?

Paul,

Que version de SQL Server estas usando?


AMB

"Paul" wrote:

Hola,

Tengo una tabla cde la siguiente forma:

Compos (id int, Nombre varchar(30), idPadre int)

Donde idPadre apunta a un Compos.id

Quiero que cuando se elimine un Compos.id se elimen sus hijos
Ejemplo:
Compos (1, "Uno", NULL)
Compos (2, "Dos", 1)
Compos (3, "Tres", 1)
Compos (4, "Cuatro", 2)

Por consiguiente si eliman el Compos.id=1 se deben de eliminar los
id=2,3,4. El 4 se debe eliminar pq su padre es el 2 y el dos pq su
padre es el 1.

El trigger es el siguiente:

ALTER TRIGGER [dbo].[DelCompos]
ON [dbo].[Compos]
FOR DELETE
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM Compos WHERE idPadre IN (SELECT id FROM DELETED);
END

Pero con este triger, al eliminar el Compos.id=1 se eliman el
Compos.id=2 y el Compos.id=3, pero NO se elimina el Compos.id=4.

Alguien puede darme una solución para que se eliminen realmente todos
sus hijos?

Gracias

On 22 feb, 16:09, Alejandro Mesa
wrote:

Paul,

Que version de SQL Server estas usando?

AMB



"Paul" wrote:
> Hola,

> Tengo una tabla cde la siguiente forma:

> Compos (id int, Nombre varchar(30), idPadre int)

> Donde idPadre apunta a un Compos.id

> Quiero que cuando se elimine un Compos.id se elimen sus hijos
> Ejemplo:
> Compos (1, "Uno", NULL)
> Compos (2, "Dos", 1)
> Compos (3, "Tres", 1)
> Compos (4, "Cuatro", 2)

> Por consiguiente si eliman el Compos.id=1 se deben de eliminar los
> id=2,3,4. El 4 se debe eliminar pq su padre es el 2 y el dos pq su
> padre es el 1.

> El trigger es el siguiente:

> ALTER TRIGGER [dbo].[DelCompos]
> ON [dbo].[Compos]
> FOR DELETE
> AS
> BEGIN
> SET NOCOUNT ON;
> DELETE FROM Compos WHERE idPadre IN (SELECT id FROM DELETED);
> END

> Pero con este triger, al eliminar el Compos.id=1 se eliman el
> Compos.id=2 y el Compos.id=3, pero NO se elimina el Compos.id=4.

> Alguien puede darme una solución para que se eliminen realmente todos
> sus hijos?

> Gracias- Ocultar texto de la cita -

- Mostrar texto de la cita -

Sql Server 2205
Y si activo la propiedad de recursive trigger, no elimina nada y me da
el error siguiente error:
"Maximum stored procedure, function trigger, or view nesting level
exceeded (limit 32)

Y solo existe esta tabla con relaciones (para hacer la prueba)

Paul,

Si tratas de hacer uso de la recursividad de los triggers, recuerda que el
maximo nivel de recursion es 32 y cuando te pasas de ese nivel, sql server da
error. Debes controlar cuando parar el trigger usando la funcion
TRIGGER_NESTLEVEL.

Puedes hacer uso de una CTE recursiva para navegar la jerarquia y asi te
evitas tener que depender de la recursividad de los triggers. Aca te paso un
ejemplo pero debes incluir manejo de errores.

create table dbo.t1 (
id int not null primary key,
Nombre varchar(30),
idPadre int null references dbo.t1([id])
)
go

insert into dbo.t1 values(1, 'Uno', NULL)
insert into dbo.t1 values(2, 'Dos', 1)
insert into dbo.t1 values(3, 'Tres', 1)
insert into dbo.t1 values(4, 'Cuatro', 2)
go

create trigger tr_t1_io_del on dbo.t1
instead of DELETE
AS
BEGIN
SET NOCOUNT ON;

with cte
as
(
select
a.[id]
from
dbo.t1 as a
where
exists (
select *
from deleted as d
where d.[id] = a.[id]
)

union all

select h.[id]
from dbo.t1 as h inner join cte as p on h.idPadre = p.[id]
)
delete a
from dbo.t1 as a inner join cte as b on a.[id] = b.[id]
option (maxrecursion 0)

END
go

delete dbo.t1
where [id] = 2
go

select * from dbo.t1
go

drop table dbo.t1
go


AMB


"Paul" wrote:

On 22 feb, 16:09, Alejandro Mesa
wrote:
> Paul,
>
> Que version de SQL Server estas usando?
>
> AMB
>
>
>
> "Paul" wrote:
> > Hola,
>
> > Tengo una tabla cde la siguiente forma:
>
> > Compos (id int, Nombre varchar(30), idPadre int)
>
> > Donde idPadre apunta a un Compos.id
>
> > Quiero que cuando se elimine un Compos.id se elimen sus hijos
> > Ejemplo:
> > Compos (1, "Uno", NULL)
> > Compos (2, "Dos", 1)
> > Compos (3, "Tres", 1)
> > Compos (4, "Cuatro", 2)
>
> > Por consiguiente si eliman el Compos.id=1 se deben de eliminar los
> > id=2,3,4. El 4 se debe eliminar pq su padre es el 2 y el dos pq su
> > padre es el 1.
>
> > El trigger es el siguiente:
>
> > ALTER TRIGGER [dbo].[DelCompos]
> > ON [dbo].[Compos]
> > FOR DELETE
> > AS
> > BEGIN
> > SET NOCOUNT ON;
> > DELETE FROM Compos WHERE idPadre IN (SELECT id FROM DELETED);
> > END
>
> > Pero con este triger, al eliminar el Compos.id=1 se eliman el
> > Compos.id=2 y el Compos.id=3, pero NO se elimina el Compos.id=4.
>
> > Alguien puede darme una solución para que se eliminen realmente todos
> > sus hijos?
>
> > Gracias- Ocultar texto de la cita -
>
> - Mostrar texto de la cita -

Sql Server 2205
Y si activo la propiedad de recursive trigger, no elimina nada y me da
el error siguiente error:
"Maximum stored procedure, function trigger, or view nesting level
exceeded (limit 32)

Y solo existe esta tabla con relaciones (para hacer la prueba)

Hola,

He probado el ejemplo que me has pasado y funciona perfectamente.

Pero tengo dos dudas: (Ten en cuenta que soy nuevo en SQL Server)
1) No entiendo que es: CTE recursiva
2) Como podría hacerlo con recusividad normal, pq tengo sólo esta
tabla y me da el error:
"Maximum stored procedure, function trigger, or view nesting level
exceeded (limit 32) "

Y es mas, desde que he activado lo de RECURSIVE_TRIGGERS no me deja
eliminar ningún registro, siempre me da el error que te he citado.

Muchas gracias por la ayuda

Paul,

1) No entiendo que es: CTE recursiva

Query recursivo, cuyo comportamineto esta basado en el resultado previo de
cada pasada. Esta utilidad es nueva en la version 2005. Puedes leer mas sobre
el tema en tus libros en linea.

2) Como podría hacerlo con recusividad normal, pq tengo sólo esta
tabla y me da el error:
"Maximum stored procedure, function trigger, or view nesting level
exceeded (limit 32) "

Como te comente en el post anterior, SQL Server permite que el maximo nivel
de anidamiento sea 32. A partir de ahi, SLQ Server da error. El trigger se
dispara por una sentencia (en tu caso delete) y este a su vez, ejecuta una
sentencia del mismo tipo que lo disparo, generando por lo tanto que este sea
disparado nuevamente. Un trigger se dispara aunque no hayan filas afectadas:

use northwind
go

if exists (
select is_recursive_triggers_on
from sys.databases
where [name] = 'northwind' and is_recursive_triggers_on = 0
)
alter database northwind
set RECURSIVE_TRIGGERS ON
go

create table dbo.t1 (
c1 int
)
go

create trigger tr_t1_del on dbo.t1
for delete
as
print TRIGGER_NESTLEVEL(object_id('tr_t1_del'), 'AFTER', 'DML')

delete dbo.t1 where c1 > 1
go

delete dbo.t1 where c1 > 1
go

drop table dbo.t1
go

alter database northwind
set RECURSIVE_TRIGGERS OFF
go

Por lo que si no controlas esto, ademas de controlar el nivel de
anidamiento, entonces tu trigger sobrepasara el maximo permitido.

ALTER TRIGGER [dbo].[DelCompos]
ON [dbo].[Compos]
FOR DELETE
AS
BEGIN

if @@rowcount = 0
return

if (TRIGGER_NESTLEVEL(object_id('DelCompos'), 'AFTER', 'DML')) = 32
begin
raiserror('NO PUEDO SEGUIR ELIMINANDO DE ESTA FORMA', 16, 1)
rollback transaction
return
end

DELETE dbo.Compos
WHERE idPadre IN (SELECT id FROM DELETED);
END
GO


AMB

"Paul" wrote:

Hola,

He probado el ejemplo que me has pasado y funciona perfectamente.

Pero tengo dos dudas: (Ten en cuenta que soy nuevo en SQL Server)
1) No entiendo que es: CTE recursiva
2) Como podría hacerlo con recusividad normal, pq tengo sólo esta
tabla y me da el error:
"Maximum stored procedure, function trigger, or view nesting level
exceeded (limit 32) "

Y es mas, desde que he activado lo de RECURSIVE_TRIGGERS no me deja
eliminar ningún registro, siempre me da el error que te he citado.

Muchas gracias por la ayuda