How to implement a custom Order Column?

Hi,
the table look like this:
id, area, areaorder, areatext
1, group1, 1, Group1Text A
2, group1, 2, Group1Text B
3, group2, 1, Group2Text A
4, group2, 2, Group2Text B
We need to be able to keep the order in each group, but also the ability to
insert and update within a group
eg. "INSERT group1, 2, Group1Text NEW" so that it will look like this:
1, group1, 1, Group1Text A
5, group1, 2, Group1Text NEW
2, group1, 3, Group1Text B
The same if we UPDATE "UPDATE SET AreaOrder=1 WHERE ID = 2" (all Order IDs
need to be adjusted to make space I guess).
What would be a good way to go about doing this?
Thanks a lot
PatrickPatrick,see if it helps you
CREATE TABLE #Test
(
col1 INT NOT NULL PRIMARY KEY,
col2 VARCHAR(20),
col3 INT NOT NULL,
col4 VARCHAR(20)
)
INSERT INTO #Test VALUES (1,'group1', 1, 'Group1Text A')
INSERT INTO #Test VALUES (2,'group1', 2, 'Group1Text B')
INSERT INTO #Test VALUES (3,'group2', 1, 'Group1Text A')
INSERT INTO #Test VALUES (4,'group2', 2, 'Group1Text B')
INSERT INTO #Test VALUES (5,'group1', 3, 'Group1Text C')
SELECT * FROM #Test ORDER BY col2
DROP TABLE #Test
"Patrick Wolf" <ppjwolf@.bigfoot.com> wrote in message
news:%23W1JA%23lSFHA.1268@.TK2MSFTNGP14.phx.gbl...
> Hi,
> the table look like this:
> id, area, areaorder, areatext
> 1, group1, 1, Group1Text A
> 2, group1, 2, Group1Text B
> 3, group2, 1, Group2Text A
> 4, group2, 2, Group2Text B
> We need to be able to keep the order in each group, but also the ability
to
> insert and update within a group
> eg. "INSERT group1, 2, Group1Text NEW" so that it will look like this:
> 1, group1, 1, Group1Text A
> 5, group1, 2, Group1Text NEW
> 2, group1, 3, Group1Text B
> The same if we UPDATE "UPDATE SET AreaOrder=1 WHERE ID = 2" (all Order IDs
> need to be adjusted to make space I guess).
> What would be a good way to go about doing this?
> Thanks a lot
> Patrick
>|||The best way to implement this would be a trigger.
Something along the lines of:
CREATE TRIGGER trgi_i_sometable ON some_table INSTEAD OF INSERT
AS
UPDATE sometable s
SET areaorder = areaorder + (SELECT COUNT(*) FROM inserted i
WHERE i.area = s.area AND i.areaorder <= s.areaorder)
INSERT INTO sometable (id, area, areaorder, areatext)
SELECT id, area, areaorder, areatext FROM inserted
CREATE TRIGGER trgi_iu_sometable ON some_table AFTER DELETE
AS
UPDATE sometable s
SET areaorder = areaorder - (SELECT COUNT(*) FROM deleted d
WHERE d.area = s.area AND d.areaorder < s.areaorder)
You can also do this with updates, but that's is going to be a little less
trivial, as you have to take into account rows that swap etc. It would
probably be the easiest if you replaced the one update with a delete and
insert:
CREATE TRIGGER trgi_u_sometable ON some_table INSTEAD OF UPDATE
AS
DELETE s
FROM sometable s
INNER JOIN deleted d
OM s.id = d.id
INSERT INTO sometable (id, area, areaorder, areatext)
SELECT id, area, areaorder, areatext FROM inserted
(You have to test that, because I am not 100 % sure if an instead of trigger
will fire another instead of trigger.)
Jacco Schalkwijk
SQL Server MVP
"Patrick Wolf" <ppjwolf@.bigfoot.com> wrote in message
news:%23W1JA%23lSFHA.1268@.TK2MSFTNGP14.phx.gbl...
> Hi,
> the table look like this:
> id, area, areaorder, areatext
> 1, group1, 1, Group1Text A
> 2, group1, 2, Group1Text B
> 3, group2, 1, Group2Text A
> 4, group2, 2, Group2Text B
> We need to be able to keep the order in each group, but also the ability
> to insert and update within a group
> eg. "INSERT group1, 2, Group1Text NEW" so that it will look like this:
> 1, group1, 1, Group1Text A
> 5, group1, 2, Group1Text NEW
> 2, group1, 3, Group1Text B
> The same if we UPDATE "UPDATE SET AreaOrder=1 WHERE ID = 2" (all Order IDs
> need to be adjusted to make space I guess).
> What would be a good way to go about doing this?
> Thanks a lot
> Patrick
>|||In addition ,I'd create Groups table to be joined with a Group_Text table.
That way you violate 2SF you have a data that not depends on PK.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23BBkvCmSFHA.3296@.TK2MSFTNGP15.phx.gbl...
> Patrick,see if it helps you
> CREATE TABLE #Test
> (
> col1 INT NOT NULL PRIMARY KEY,
> col2 VARCHAR(20),
> col3 INT NOT NULL,
> col4 VARCHAR(20)
> )
> INSERT INTO #Test VALUES (1,'group1', 1, 'Group1Text A')
> INSERT INTO #Test VALUES (2,'group1', 2, 'Group1Text B')
> INSERT INTO #Test VALUES (3,'group2', 1, 'Group1Text A')
> INSERT INTO #Test VALUES (4,'group2', 2, 'Group1Text B')
> INSERT INTO #Test VALUES (5,'group1', 3, 'Group1Text C')
> SELECT * FROM #Test ORDER BY col2
> DROP TABLE #Test
>
> "Patrick Wolf" <ppjwolf@.bigfoot.com> wrote in message
> news:%23W1JA%23lSFHA.1268@.TK2MSFTNGP14.phx.gbl...
> to
IDs
>|||Thanks very much for your input :)
It seems to work (I programmed another trigger for instead of update).
I have trouble though understanding how this works?
UPDATE sometable s
> SET areaorder = areaorder + (SELECT COUNT(*) FROM inserted i
> WHERE i.area = s.area AND i.areaorder <= s.areaorder)
How is this different from this?
DECLARE @.targetOrder as smallint,@.targetArea as int
SELECT @.targetOrder = areaOrder, @.targetArea=area FROM inserted
UPDATE sometable s SET areaorder = areaorder + 1 WHERE areaorder >=
targetOrder and area = targetArea
Thanks and all the best
Patrick|||My statement works when multiple rows are inserted, while your statement
only works for one row. An insert/delete/update statement can affect
multiple rows, but will only fire the trigger once for the statement, not
once for every row. So you have to write trigger so that they can handle
multiple rows, unless you put in some specific code that rolls back
statements that affect multiple rows, like:
CREATE TRIGGER ....
AS
IF @.@.ROWCOUNT > 1
BEGIN
RAISERROR ('No multirow updates allowed!, 16,1)
ROLLBACK TRAN
RETURN
END
... rest of trigger
but you only need that in rare cases. In most cases you can write the
trigger to handle multirow inserts/updates/deletes.
Jacco Schalkwijk
SQL Server MVP
"Patrick Wolf" <ppjwolf@.bigfoot.com> wrote in message
news:O23HD$uSFHA.1896@.TK2MSFTNGP14.phx.gbl...
> Thanks very much for your input :)
> It seems to work (I programmed another trigger for instead of update).
> I have trouble though understanding how this works?
> UPDATE sometable s
> How is this different from this?
> DECLARE @.targetOrder as smallint,@.targetArea as int
> SELECT @.targetOrder = areaOrder, @.targetArea=area FROM inserted
> UPDATE sometable s SET areaorder = areaorder + 1 WHERE areaorder >=
> targetOrder and area = targetArea
> Thanks and all the best
> Patrick
>|||Thanks very much for your answer.
Since I feel that you got a good point here :) I still have one challenge.
I would like to write these statments myself and I dont totaly understand
how yours works:
Would you mind pointing out how the COUNT(*) works here?

> UPDATE sometable s
Thanks a lot
Patrick
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:u17O7bxSFHA.2812@.TK2MSFTNGP09.phx.gbl...
> My statement works when multiple rows are inserted, while your statement
> only works for one row. An insert/delete/update statement can affect
> multiple rows, but will only fire the trigger once for the statement, not
> once for every row. So you have to write trigger so that they can handle
> multiple rows, unless you put in some specific code that rolls back
> statements that affect multiple rows, like:
> CREATE TRIGGER ....
> AS
> IF @.@.ROWCOUNT > 1
> BEGIN
> RAISERROR ('No multirow updates allowed!, 16,1)
> ROLLBACK TRAN
> RETURN
> END
> ... rest of trigger
> but you only need that in rare cases. In most cases you can write the
> trigger to handle multirow inserts/updates/deletes.
> --
> Jacco Schalkwijk
> SQL Server MVP
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
Since there is no such thing in the relational as a magical universal
id, can I assume that you want this column for display purposes, in
violation of the basic principle of a tiered archtiecture? Is the real
key (area, area_order)?
If you keep designing things like this, you will wind up with triggers
(procedural code!!) and Normal Form problems that will destroy your
data integrity.|||Hi,
yes you are right area, areaorder are the real ids. In the real application
area is an int and has foreign key constraint to the parent table which
defines the areas. I just did the sample for simplicity.
The grid I work with has trouble with combined primary keys so it seemed
easier to just use an id column even though it has not much meaning.
Thanks for your suggestions
Patrick
----
---
CREATE TABLE Test3
(
id INT NOT NULL PRIMARY KEY IDENTITY (1,1),
area VARCHAR(10) NOT NULL,
areaorder INT NOT NULL,
areatext VARCHAR(20) NOT NULL
)
INSERT INTO test3 VALUES ('group1', 1, 'Group1Text A')
INSERT INTO Test3 VALUES ('group1', 2, 'Group1Text B')
INSERT INTO Test3 VALUES ('group2', 1, 'Group1Text A')
INSERT INTO Test3 VALUES ('group2', 2, 'Group1Text B')
INSERT INTO Test3 VALUES ('group1', 3, 'Group1Text C')
----
---
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1114612727.507792.92340@.l41g2000cwc.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in your
> schema are. Sample data is also a good idea, along with clear
> specifications.
> Since there is no such thing in the relational as a magical universal
> id, can I assume that you want this column for display purposes, in
> violation of the basic principle of a tiered archtiecture? Is the real
> key (area, area_order)?
> If you keep designing things like this, you will wind up with triggers
> (procedural code!!) and Normal Form problems that will destroy your
> data integrity.
>