Updating an Order Column

fullingdale · October 24th, 2011, 06:57 AM

I have a table which contains a column for the record display order, for example a list of menu items:

Code:

ID  OPTION  ORDER_INDEX
--  ------  -----------
 1  Opt1    10
 2  Opt3    30
 3  Opt2    20

This is then called with "SELECT OPTION FROM OPTIONS_TABLE ORDER BY ORDER_INDEX" and what I am trying to do is put a TRIGGER AFTER INSERT/UPDATE on the table, so that if someone adds a new entry with an ORDER_INDEX that's not a multiple of 10 or that the ORDER_INDEX is NULL then the table will maintain itself

Hope that makes sense and hope someone can help me

I am using SQL2008, btw