SQL Apprentice Question
I Have Table Num As Int , Name As NvarChar(20) I Need Trigger to Resort The
Field Num When I Change The num
Num Name
1 aaaaa
2 bbbbb
3 cccccc
4 ddddd
I Want when I Change the Num 1 To 2 Came Like
Num Name
1 bbbbb
2 aaaaaa
3 cccccc
4 ddddd
Celko Answers
Let's make this problem more concrete. You want to put automobiles
into numbered parking spaces and move them around.
CREATE TABLE Motorpool
(parking_space INTEGER NOT NULL PRIMARY KEY
CHECK (parking_space > 0),
vin CHAR(170) NOT NULL);
Re-arrange the display order based on the parking_space column:
CREATE PROCEDURE SwapVehicles (@old_parking_space INTEGER,
@new_parking_space INTEGER)
AS
UPDATE Motorpool
SET parking_space
= CASE parking_space
WHEN @old_parking_space
THEN @new_parking_space
ELSE parking_space + SIGN(@old_parking_space - @new_pos)
END
WHERE parking_space BETWEEN @old_parking_space AND @new_parking_space
OR parking_space BETWEEN @new_parking_space AND @old_parking_space;
When you want to drop a few rows, remember to close the gaps with this:
CREATE PROCEDURE CloseMotorpoolGaps()
AS
UPDATE Motorpool
SET parking_space
= (SELECT COUNT (M1.parking_space)
FROM Motorpool AS M1
WHERE M1.parking_space <= Motorpool.parking_space);
Thursday, June 15, 2006
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment