views:

14

answers:

1

I've created a trigger in ms sql server 2005. The trigger is supposed to update orderdata. Example:

table
id order
1  1
2  2
3  3
4  4

Update so that id 4 has order 2 would create.

table
id order
1  1
2  2
3  3
4  2

With my trigger, after this it would automatically create.

table
id order
1  1
2  3
3  4
4  2

My table obviously looks different but that shouldn't matter too much, my trigger looks like this.

ALTER TRIGGER [dbo].[testtrigger] ON [dbo].[pages] AFTER UPDATE, INSERT AS

DECLARE @ordernr numeric(18, 0)
DECLARE @parentid numeric(18, 0)
DECLARE @thisid numeric(18, 0)

SET @ordernr = (SELECT TOP 1 pageOrder FROM Inserted)
SET @parentid = (SELECT TOP 1 pageParent FROM Inserted)
SET @thisid = (SELECT TOP 1 pageId FROM Inserted)

IF EXISTS (SELECT pageOrder FROM dbo.pages WHERE PageParent = @parentid AND pageOrder = @ordernr)
BEGIN
    UPDATE dbo.pages
    SET pageOrder=@ordernr + 1
    WHERE pageOrder=@ordernr
    AND PageId != @thisid
    AND PageParent=@parentid
END

In the table several pages have the same parent so I need the order to keep them in the right place however if the pages are at the top(aka pageParent= NULL) it doesn't work and if it isn't NULL it does work.

So... does PageParent = @parentid equal true if both are NULL? and if yes, why won't this work?

A: 

No, it doesn't. Something that is NULL is not equal to something else that is NULL. It is not not equal to it, either.

Brian Hooper
Thnx, I also found the solution for my problem, had to changeANSI_NULLS to OFFinside the trigger
red-X