Error in my trigger

Question

Hello everyone. I am working on a project using SqlSiteMapProvider. I have two tables Articles and SiteMap

Articles table:

CREATE TABLE [dbuser].[Articles](
 [ArticleId] [uniqueidentifier] NOT NULL,
 [Title] [nvarchar](500) NULL,
 [Description] [ntext] NULL,
 [CategoryId] [int] NULL,
 [pubDate] [datetime] NULL,
 [Author] [nvarchar](255) NULL,
 [Hit] [int] NULL,
 [Auth] [bit] NULL
)...

And SiteMap Table:

CREATE TABLE [dbuser].[SiteMap](
 [ID] [int] IDENTITY(0,1) NOT NULL,
 [Title] [nvarchar](50) NULL,
 [Description] [nvarchar](512) NULL,
 [Url] [nvarchar](512) NULL,
 [Roles] [nvarchar](512) NULL,
 [Parent] [int] NULL,
 CONSTRAINT [PK_SiteMap] PRIMARY KEY CLUSTERED 
(...

When I insert an Article my asp.net page also inserts that articles url and such information into SiteMap table. What I am trying to do is when I delete an Article from my Articles table (from asp.net page) the related row from SiteMap table with a trigger.

My asp.net page inserts the Article info into Sitmap table in this format:

Dim SMUrl As String = "~/c.aspx?g=" & ddlCategoryId.SelectedValue & "&k=" & BaslikNo.ToString

I mean there is no one column exactly matchin in two tables.

My Trigger is as follows:

USE [MyDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON
GO
SET ROWCOUNT 1
GO
ALTER TRIGGER [dbuser].[trig_SiteMaptenSil] ON [dbuser].[Articles]
AFTER DELETE
AS
BEGIN
DECLARE  @AID UNIQUEIDENTIFIER, @ttl NVARCHAR,@CID INT
SELECT @AID=ArticleId, @ttl = Title, @CID=CategoryId  FROM DELETED
IF EXISTS(SELECT * FROM dbuser.SiteMap WHERE Url = N'~/c.aspx?g=' + CONVERT(nvarchar(5), @CID) + N'&k=' + CONVERT(nvarchar(36), @AID))
BEGIN
 DELETE FROM dbuser.SiteMap WHERE Url = N'~/c.aspx?g=' + CONVERT(nvarchar(5), @CID) + N'&k=' + CONVERT(nvarchar(36), @AID)
END
END
GO

I am using SSMSE 2008 and my remote db server's version is 8.0

The Error I get is:

No rows were deleted. A problem occurred attempting to delete row 1. Error Source: Microsoft.VisualStudio.DataTools. Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(2 rows). Correct the errors and attempt to delete the row again or press ESC to cancel the change(s).

May you help me how to get this working? I have searched for this for about a few days.. Couldn't find a solution for my case...

Thanks in advance

Answer 1

To deal with the original issue and cope with deletions of multiple rows your trigger could be rewritten as follows.

ALTER TRIGGER [dbuser].[trig_SiteMaptenSil] ON [dbuser].[Articles]
AFTER DELETE
AS
BEGIN
 SET NOCOUNT ON

DELETE
FROM   s
FROM   dbuser.SiteMap     AS s
       INNER JOIN Deleted AS d
       ON     s.Url = N'~/c.aspx?g=' + CONVERT(nvarchar(5), d.CategoryId) + N'&k=' 
                                     + CONVERT(nvarchar(36), d.ArticleId)

END