# Posts Tagged help needed

### Help needed: monotonicity without triggers?

I’ve a problem that’s been gnawing away at me for a few days now. It’s easily stated:

**Without using triggers**, if X and Y are numeric types, can you enforce a rule that says the numerical ordering of column Y must be the same as column X?

That is, `ROW_NUMBER() OVER (ORDER BY X) == ROW_NUMBER() OVER (ORDER BY Y)`

. (In mathematical terms, the relationship between X and Y is monotonically increasing.)

E.g.

```
CREATE TABLE dbo.MyTable
(
X INT NOT NULL
,Y INT NOT NULL
)
INSERT dbo.MyTable(X,Y)
VALUES (1,3), (5,17), (13,54), (17, 65)
```

, and all is well: `order(X) == order(Y) == (1,2,3,4)`

.

But if I then do:

```
INSERT dbo.MyTable(X,Y)
VALUES (23, 51)
```

, the INSERT should fail, as `order(Y) == c(1,2,3,5,4) != order(X)`

.

Approaches that do not work:

- A check constraint that calls a user-defined function. It works on INSERT, but not UPDATE.
- A view with a UNIQUE index on it. In order to define a UNIQUE index on a view, it must already have a clustered index defined on it. But you can’t create a clustered index on a view that contains LEFT JOINs, or window functions like ROW_NUMBER or LEAD/LAG.

Yes, I can achieve the result I want with a trigger; but for a rule that can be explained so simply, it doesn’t feel right.

Someone somewhere has a beautiful solution to this problem, so if you know of any solution (or indeed, can categorically state that it’s impossible to achieve), please let me know in the comments!

## Recent Comments