Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2007
    Posts
    96

    Unanswered: Problem with relationship

    Hello everyone,

    I'm trying to keep the data integrity in a database I'm creating (see diagram). 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 advance

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    DDL please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jul 2007
    Posts
    96
    Quote Originally Posted by blindman
    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

    Quote Originally Posted by r937
    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
    Attached Files Attached Files

  5. #5
    Join Date
    Jul 2007
    Posts
    96
    I've created the following trigger to solve the problem. If someone has any other suggestion please feel free to share

    Best regards

    Code:
    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

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jul 2007
    Posts
    96
    Quote Originally Posted by blindman
    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

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

    Code:
    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.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Code:
    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
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Jul 2007
    Posts
    96
    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.

  10. #10
    Join Date
    Jul 2007
    Posts
    96
    Nevermind me, the error was due to a typo

    cheers mate

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •