Hello,
I've a 2 tables that would store Role/RoleMember The definition for those table is the following
Table Role Definition:
Code Snippet
CREATE TABLE [dbo].[Role](
[id] [int] IDENTITY(1,1) NOT NULL,
[isAdministratorRole] [bit] NOT NULL CONSTRAINT [DF_Role_idAdminRole] DEFAULT ((0)),
[isUserRole] [bit] NOT NULL CONSTRAINT [DF_Role_isUserRole] DEFAULT ((0)),
[isSystemRole] [bit] NOT NULL CONSTRAINT [DF_Role_isSystemRole] DEFAULT ((0)),
CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Table RoleMember Definition:
Code Snippet
CREATE TABLE [dbo].[RoleMember](
[role_id] [int] NOT NULL,
[member_id] [int] NOT NULL,
CONSTRAINT [PK_RoleMember] PRIMARY KEY CLUSTERED
(
[role_id] ASC,
[member_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[RoleMember] WITH CHECK ADD CONSTRAINT [FK_RoleMember_Role] FOREIGN KEY([role_id])
REFERENCES [dbo].[Role] ([id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[RoleMember] CHECK CONSTRAINT [FK_RoleMember_Role]
GO
ALTER TABLE [dbo].[RoleMember] WITH CHECK ADD CONSTRAINT [FK_RoleMember_Role1] FOREIGN KEY([member_id])
REFERENCES [dbo].[Role] ([id])
GO
ALTER TABLE [dbo].[RoleMember] CHECK CONSTRAINT [FK_RoleMember_Role1]
The foreign key on RoleMember table points both to id Field in Role. I've been able to define ON DELETE CASCADE to one of the ForeignKey Constraint but obviously not to the other one! I've desided to trick this by setting a DELETE Triger to delete RoleMember records whose member_id match deleted Role.id. The records whose id match deleted Role.id are deleted by the foreign key constraint.
The Trigger is define as follow:
Code Snippet
CREATE TRIGGER [dbo].[OnRoleDelete]
ON [dbo].[Role]
FOR DELETE
AS
BEGIN
SET NOCOUNT ON
DELETE [RoleMember] WHERE member_id IN (SELECT id FROM deleted)
END
Suprisingly whenever I delete an entry from the Role table the deletion failed with the message
The DELETE statement conflicted with the REFERENCE constraint "FK_RoleMember_Role1". The conflict occurred in database "edh", table "dbo.RoleMember", column 'member_id'.
Is seems that the trigger is never called! Whats wrong with this?
Thanks for help
mavrj
You have to instead of trigger in your case...
Code Snippet
Create TRIGGER [dbo].[OnRoleDelete]
ON [dbo].[Role]
INSTEAD OF DELETE
AS
BEGIN
SET NOCOUNT ON
DELETE [RoleMember] WHERE member_id IN (SELECT id FROM deleted)
DELETE Role WHERE Id IN (SELECT id FROM deleted)
END
|||Thanks for the quick answer
So what is the "FOR DELETE" for?
I assume that the "INSTEAD OF" triggers disable table trigger for the scope
|||For Delete only executed when the delete operation is performaing or just perfomed (with out any error). In your case because of the Foreign key the delete operation is not happening.
Instead of trigger means, Instead of doing the given query operation (insert/update/delete), do the operation which is written in my trigger body. So that helped you to complete your requirement.
