tags:

views:

21

answers:

0

Hi, I am trying to create a Delete trigger. I have this navigation table which is called Nemanet_Navigation. The table has a foreign key to itself but when selcting relationship for the table I have in INSERT and UPDATE Specification selected - Delete Rule NO Action. So I do not select Cascading. Then I have this trigger:

CREATE TRIGGER Del_Nemanet_Navigation ON Nemanet_Navigation 
  INSTEAD OF DELETE
AS  

CREATE TABLE #Table(
    Nav_ID uniqueidentifier
    )
    INSERT INTO #Table (Nav_ID)
    SELECT Nav_ID
    FROM deleted

    DECLARE @C uniqueidentifier
    SET @c = 0

    WHILE @C <> (SELECT COUNT(Nav_ID) FROM #Table) BEGIN
        SELECT @c = COUNT(Nav_ID) FROM #Table

        INSERT INTO #Table (Nav_ID)
        SELECT Nemanet_Navigation.Nav_ID
        From Nemanet_Navigation
        LEFT OUTER JOIN #Table ON Nemanet_Navigation.Nav_ID = #Table.Nav_ID
        WHERE   Nemanet_Navigation.Nav_pID IN (SELECT Nav_ID FROM #Table)
        AND     #Table.Nav_ID IS NULL
    END

    DELETE  Nemanet_Navigation
    FROM    Nemanet_Navigation
    INNER JOIN #Table ON Nemanet_Navigation.Nav_ID = #Table.Nav_ID

But I get the following error:

Cannot create INSTEAD OF DELETE trigger 'Del_Nemanet_Navigation' on 'Nemanet_Navigatin'.

This is because table has a FOREIGN KEY WITH cascading DELETE. But my Nemante_Navigation table has delete rule - no action (but is marked gray for some reason I do not know - and I can not change it). The trigger is in folder called Triggers under the Nemanet_Navigation Table. Can anybody help?