Complex SQL Query

Well, at least I think it's complex! It might be easy for you!

I have the following 3 tables:

ContentPages (Primary Key is PageID) stores information about pages, such as where the file is on the server, who created the page etc. ContentProtocols (PK is ProtocolID) stores the names of "protocols". In my content management system, a protocol is made up of a sequence of pages. So "Ben's Protocol" could be a sequence of "BensPage1.html", "BensPage2.html" and "BensPage3.aspx". The order of the pages is important. ContentProtocolsPages. This table stores pageIDs next to protocolIDs so that the pages are "in" protocols. It also stores the order of the pages.

ContentProtocolsPages has three columns:

PageID (foreign key from ContentPages table) ProtocolID (FK from ContentProtocols table) Position. This is an integer column that stores the position of a page within a protocol, so the first page in a protocol has position 1. The second has position 2. The third has position 3 and so on.

The PageID and Position columns combine to make the primary key in the ContentProtocolPages table, because obviously you can't have more than one page occupying a given position in a protocol. e.g. You couldn't have "BenPage1.html" occupying "Ben's Protocol" position 2 and "BenPage2.html" occupying the same position in the same protocol.

The problem occurs when deleting a page.

The logic is:
Before a page can be deleted from the ContentPages table, it has to be taken out of any protocols, so all record with that page in ContentProtocolsPages have to be deleted. This means that any pages in the same protocol AFTER the deleted page need to have their position set to one less (Position=Position-1), to account for the page being removed. So, if you delete the page with position 3 in a six page protocol, the SQL statement has to set the pages with positions 4, 5 and 6 to be 3, 4 and 5 respecively.

You can't do that BEFORE deleting the record of the page in ContentProtocolsPages because that violates the primary key constraint, but you can't do it AFTER either, because then you have no idea what Position that page used to occupy.

Here's what I have so far, but I'm getting an error because of the composite primary key.

UPDATE ContentProtocolsPages
SET Position=Position-1
WHERE Position > (SELECT Position FROM ContentProtocolsPages WHERE PageID=@.PageID)
AND ProtocolID = (SELECT ProtocolID FROM ContentProtocolsPages WHERE PageID=@.PageID)

DELETE FROM ContentProtocolsPages WHERE PageID=@.PageID

DELETE FROM ContentPages WHERE PageID=@.PageID

Any Ideas? Many thanks to anybody who can help out with this problem

Yes absolutely. How silly of me. The ProtocolID and PositionID columns combine to make the Primary Key. Thanks very much for pointing that out. Any ideas regarding the problem, now correctly stated?
|||Ok. I thougt I had jumped the Conclusion. So I deleted the post. If you are using SQL SERVER 2005 try using the OUTPUT CLAUSE of delete statement.|||Thanks very much, but I'm afraid I've never heard of that. Can you recommend an article on how I should use it to achieve my purpose?
|||

you can also try an AFTER TRIGGER. Here is the sample code

ALTER TRIGGER MyDelTRG

ON [ContentProtocolsPages]

AFTER DELETE

AS

DECLARE @.MyDeleteCursor CURSOR

DECLARE @.PageID INT,@.ProtocolID INT,@.Position INT

-- Curosor Definition

SET @.MyDeleteCursor = CURSOR FAST_FORWARD

FOR

SELECT PageID,ProtocolID,Position FROM DELETED

OPEN @.MyDeleteCursor

FETCH NEXT FROM @.MyDeleteCursor

INTO @.PageID,@.ProtocolID,@.Position

--LOOP all records from the inserted table

WHILE @.@.FETCH_STATUS = 0

BEGIN

UPDATE ContentProtocolsPages SET Position=Position-1

WHERE Position>=@.Position

FETCH NEXT FROM @.MyDeleteCursor

INTO @.PageID,@.ProtocolID,@.Position

CLOSE @.MyDeleteCursor

DEALLOCATE @.MyDeleteCursor

END

|||That looks great! I don't understand it, but I'll try and learn it as I go along. On first inspection it looks like either of your suggestions could work. Do you recommend either in particular or doesn't it matter? Anyway, thanks for all your help, I'll see if I can put it into action.
Ben
|||Let me know if the code worked for you