Rbac Foreign Key Sql Server

So I’m using Microsoft SQL Serve Management Studio 2008. The Foreign Key CASCADE statements for auth_item are causing me issues. I get the following error:


Introducing FOREIGN KEY constraint 'FK_auth_item_child_child' on table 'auth_item' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

I’ve found out I can solve this using triggers, but I’m not exactly experienced with triggers. Anyone know how you would simulate a fake cascade using triggers for these tables and constraints?

auth_item


[name] VARCHAR(64) PRIMARY KEY,

[type] INT NOT NULL,

[description] TEXT,

[bizrule] TEXT,

[data] TEXT

auth_item_child


[parent] VARCHAR(64) NOT NULL,

[child] VARCHAR(64) NOT NULL


CONSTRAINT FK_auth_item_child_parent

	FOREIGN KEY([parent])

	REFERENCES auth_item([name])

	ON DELETE CASCADE

	ON UPDATE CASCADE


CONSTRAINT FK_auth_item_child_child

	FOREIGN KEY([child])

	REFERENCES auth_item([name])

	ON DELETE CASCADE

	ON UPDATE CASCADE

I’ve implemented both tables at the moment, and to avoid the CASCADE issue I have not implemented the constraint FK_auth_item_child_child. Please excuse any syntax errors above, I typed it up a little fast. Any help would be much appreciated.