Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2005
    Posts
    2

    Unanswered: conflict between (cascade) DELETE Trigger and Foreign Key Constrain

    I'm trying to create relational database with some triggers in SQL Server 7.0, but it doesn't work as expected. Let's say that I have 'Office' database with two tables, 'Users' and 'UserRights' (userRights table should have much more rights, but that's not relevant for this problem):

    CREATE TABLE [Users] (
    [FS_Username] [nvarchar] (8) NOT NULL ,
    [FS_Password] [nvarchar] (32) NOT NULL ,
    CONSTRAINT [PK_Users] PRIMARY KEY NONCLUSTERED
    (
    [FS_Username]
    ) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    CREATE TABLE [UserRights] (
    [FS_Username] [nvarchar] (8) NOT NULL ,
    [FI_UserType] [int] NOT NULL CONSTRAINT [DF_UserRights_FI_UserType] DEFAULT (1),
    [FI_AllowLogin] [int] NOT NULL CONSTRAINT [DF_UserRights_FI_AllowLogin] DEFAULT (1),
    CONSTRAINT [PK_UserRights] PRIMARY KEY NONCLUSTERED
    (
    [FS_Username]
    ) ON [PRIMARY] ,
    CONSTRAINT [FK_UserRights_Users] FOREIGN KEY
    (
    [FS_Username]
    ) REFERENCES [Users] (
    [FS_Username]
    )
    ) ON [PRIMARY]
    GO

    Foreign Key CONSTRAIN above is created by adding both tables to the diagram and defining relationship between these two tables FS_Username field, where 'Enable relationship for INSERT and UPDATE' option is turned ON. You can easily see this if you create diagram youself and insert these two tables in it.
    Next to this, I created two triggers that should handle inserting/deleting rows in UserRights table as consequence of inserting/deleting rows in Users table:

    CREATE TRIGGER InsertUserRights ON Users
    FOR INSERT
    AS
    BEGIN
    INSERT INTO UserRights (FS_Username) (SELECT FS_Username FROM Inserted)
    END

    CREATE TRIGGER DeleteUserRights ON Users
    FOR DELETE
    AS
    BEGIN
    DELETE UserRights WHERE FS_Username IN
    (SELECT FS_Username FROM Users)
    END

    Now, when (manually) I insert row in Users table, UserRights table gets updated accordingly. HOWEVER, when I try to delete one or more entries from Users table, I get error report. For example, if you try to execute following two commands:

    Insert Into Users (FS_Username, FS_Password) VALUES ('John', 's')

    Delete from Users

    ... first command will succede, but second one will fail with message:

    DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_UserRights_Users'. The conflict occurred in database 'Office', table 'UserRights', column 'FS_Username'.
    The statement has been terminated.

    Does anyone know how to resolve this problem without loosing constrains and triggers ? (If I turn off 'Enable relationship for INSERT and UPDATE' option for relationship, things will work fine, but than I can make inconsistent data in UserRights table).

    tnx a lot,
    Dejan

  2. #2
    Join Date
    Oct 2005
    Posts
    2

    correction

    Sorry, second trigger should be:

    CREATE TRIGGER DeleteUserRights ON Users
    FOR DELETE
    AS
    BEGIN
    DELETE UserRights WHERE FS_Username IN
    (SELECT FS_Username FROM deleted)
    END

    This is just the correct trigger, it doesn't solve the problem in hand...

    Dejan

Posting Permissions

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