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?