Problem with relationship

Hello everyone,

I'm trying to keep the data integrity in a database I'm creating (see diagram (http://www.gnomeslab.com/DataBaseDiagram.png)). I've already created all the relations. The problem is that when I add the cascading effect to the relation FK_Usergroup_Role_RoleId_Application_Role_Id SQL Server, naturally, throws me a multiple paths error.

My question is how can I work arround this? Any design change suggestion? The only way it to preserve the integrity programmaticaly?

Thanks in advanceDDL please|||When you have multiple paths from one table to another, only one of the paths can be set up for cascading updates and deletes.
The work-around for this is to create table triggers for implementing insert/update/delete cascading on the other paths.|||When you have multiple paths from one table to another, only one of the paths can be set up for cascading updates and deletes.
The work-around for this is to create table triggers for implementing insert/update/delete cascading on the other paths.

Figured. If it could be solved by some sort of design change I'd rather keep triggers / tsql as last option ;)

DDL please
You can view the tables diagram here: http://www.gnomeslab.com/DataBaseDiagram.png

The tables SQL can be found in this post attachments. The file name is DbTables.txt

Best regards|||I've created the following trigger to solve the problem. If someone has any other suggestion please feel free to share ;)

Best regards

USE [BF_Dev]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [Application].[Role_OnDelete]
ON [Application].[Role]
AFTER DELETE
AS
BEGIN
SET NOCOUNT ON;

BEGIN TRY -- Enclosing the transaction in the main try / catch
BEGIN TRANSACTION; -- Begin main transaction

-- Declaring the local variables
DECLARE @.RoleId AS Int;

-- Fetching the ID of the deleted role
SELECT
@.RoleId = Id
FROM
deleted;

-- Deleting the rows associated with this role id from the Security.Usergroup_Role
DELETE FROM
Security.Usergroup_Role
WHERE
RoleId = @.RoleId;

COMMIT TRANSACTION; -- Commit main transaction
END TRY -- End main try
BEGIN CATCH -- Handeling the error
IF (@.@.TRANCOUNT > 0)
BEGIN
ROLLBACK TRANSACTION; -- Rollback main transaction
END -- End transaction count

EXECUTE [dbo].[uspLogInternalDbError];
END CATCH; -- End main catch

END|||Bzzzzt! Try again.
Your trigger does not support multi-record transactions.
You need to read up on Books Online about using the virtual INSERTED and DELETED tables for proper trigger coding.|||Bzzzzt! Try again.
Your trigger does not support multi-record transactions.
You need to read up on Books Online about using the virtual INSERTED and DELETED tables for proper trigger coding.

Indeed, I didn't think of the multi-record situation.

I did find some information about the inserted and deleted tables on online books, however none of the links I found was directly related to this kind of issues. Perhaps I miss searched :rolleyes:

Would this change fix the problem? (I'm not very used to triggers yet :cool: )

DELETE FROM
Security.Usergroup_Role
WHERE
RoleId IN (
SELECT
d.Id
FROM
DELETED d
);

By the way, thanks for your help and patience blindman.

Best regards.|||USE [BF_Dev]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [Application].[Role_OnDelete]
ON [Application].[Role]
AFTER DELETE
AS
BEGIN
delete UserGroup_Role
from UserGroup_Role
inner join deleted on UserGroup_Role.RoleID = deleted.Role_ID
END|||Not quite sure I understand your sql statement.

Even if I change the Usergroup_Role to [Security].[Usergroup_Role] it would return an invalid object error.|||Nevermind me, the error was due to a typo :angel:

cheers mate :beer: