From: "Christian Danner"

> is there a reason why INSERT/UPDATE/DELETE don't support a specific
> ordering in accordance with the SELECT statement?

Because it has no sense in database that strictly conforms SQL-standard rules.
I.e. constraints have to be valid before the statement (or transaction)
starts, and after it ends, but can be temporarily broken in the middle.
But you are right that triggers do break that rule since they are executed
immediately for each modified row, and the result can depend on their order.

You can speed up the code a bit by using cursor (which means DELETE
will not have to locate the row that was already located by SELECT), i.e.


FOR SELECT TABLE.ID
FROM TABLE
ORDER BY TABLE.TIMSTAMP DESC
INTO :T_ID
AS CURSOR C
DO
DELETE
FROM TABLE
WHERE CURRENT OF C;

Ivan

>
> At first sight this question might look strange. In the end the job is
> done - basta.
>
> But imagine a table with LIFO stored rows, where triggers watch over
> this business rule of an I/O in chronological order.
>
> To clear the table
>
> DELETE FROM TABLE
>
> would raise an exception, whereas a loop like
>
> FOR SELECT TABLE.ID
> FROM TABLE
> ORDER BY TABLE.TIMSTAMP DESC
> INTO :T_ID
> DO
> DELETE
> FROM TABLE
> WHERE TABLE.ID = :T_ID
>
> would be way too slow.
>
> Why not
>
> DELETE FROM TABLE
> ORDER BY TABLE.TIMSTAMP DESC ?
>
> Is it to be SQL-92/99 compliant?