Can not create Delete trigger because table has a FOREIGN KEY with cascading DELETE
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 selecting relationship for the table I have in INSERT and UPDATE Specification selecte开发者_Go百科d - 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?
INSTEAD OF
triggers are for modifying views which you can't use DML
.
Try with a BEFORE
or AFTER
trigger.
Oracle RDBMS
精彩评论