I have one table that point to themselve.
the PK is n_id and the FK is n_parent_id.
I have a trigger on that table after delete statement.
When a node is deleted I also delete childnode but if the child node have childnode I also need to delete them.
The trigger works fine for the first delete but if the first child have childnode the tigger is not fired.
I set the nested_trigger to 1 with sp_configure and when I execute sp_dboptions I see : "recursive triggers"
Did I forgot something ? Can you help me
ALTER TRIGGER [dbo].[OnNodeDelete]
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @deleted_node_id int
select @deleted_node_id = n_id from deleted
delete from syn_nodes where syn_nodes.n_parent_id = @deleted_node_id //shoul trigger the samme trigger but does not