On Delete Triggers Question

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.