How to prevent recursive trigger call in DB2

Question

I have an update statement, nested in an update trigger:

CREATE TRIGGER "BAD_RECURSIVE_TRIGGER" 
    AFTER UPDATE ON "MYTABLE"
    REFERENCING  NEW AS NEW_ROW
    FOR EACH ROW
WHEN (NEW_ROW.ORDER IS NOT NULL)
BEGIN ATOMIC
    IF <SOMECONDITION> THEN
    UPDATE "MYTABLE" SET ORDER=ORDER+1 // This "update" fires the recursion.
    WHERE <OTHERCONDITION>
END IF;
END;

I want to prevent the recursive execution of the trigger, this is on DB2 (v9.7), I've seen similar questions for SQL-Server and ORACLE databases:

Prevent recursive trigger in PostgreSQL

How do I prevent a database trigger from recursing?

But I can't find a way to prevent this over DB2. Is there a way to prevent a recursive trigger call over DB2?

Does it have to be `AFTER UPDATE`? What happens if the trigger happens _before_ the actual update, and modifies `ORDER` at that point? And I would have assumed that you only want to update the 'current' row, but that statement is going to update the _entire_ table... Alternatively, only update if `ORDER` _hasn't_ changed from it's current value (if the new value is the same as the old, in-table value). — Clockwork-Muse, Dec 12 '12 at 22:04
@Clockwork-Muse, desired behavior is: IF "this record gets updated" THEN "update other records" (from the same table). The issue is that, on the first "update trigger", it does another update, which fires the trigger again. And there doesn't seem to be a way to know whether is the first call or not, which would solve my problem. and I've posted another question for that: http://stackoverflow.com/questions/13845776/db2-equivalent-of-sql-servers-trigger-nestlevel, but I'm still open to any other good alternative for doing it. — Edgar Catalán, Dec 13 '12 at 21:52
... _Why_ do you want to update other records in the table? What is the actual problem you're trying to solve? That statement is going to be a huge bottleneck, as you're going to end up with serial access to the table. — Clockwork-Muse, Dec 13 '12 at 23:08
I'll try to summarize the original requirement: Have a column "ORDER" on the table, to allow users to modify the "order" of items as desired, that order should be kept on the DB because queries will be made based on it. Order must be mantained (as a consecutive list of numbers). So small "lists" will be kept into the DB. As an example: having a list of records with orders: 1, 2, 3, 4. If user inserts a new record with order = 2, records 3 and 4 will be updated to "order = order + 1". Similar behavior when a record gets deleted (order of "upper" records will be updated to "order = order - 1"). — Edgar Catalán, Dec 14 '12 at 23:55

score 0 · Answer 1 · edited Dec 13 '12 at 21:12

0

CREATE TRIGGER NO CASCADE BEFORE UPDATE ...

edited Dec 13 '12 at 21:12

Edgar Catalán

65
8

answered Dec 13 '12 at 06:18

Alexey

1,826
3
17
20

Unfortunately, creating a "before update" trigger with an "update" statement inside is not possible, AND creating an "after trigger" with an "update" inside does not allow the use of "NO CASCADE" statement (http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/), this is sad, because it seems like they specifically wanted to avoid the behavior I'm looking for =( – Edgar Catalán Dec 13 '12 at 21:01

Clockwork-Muse · Answer 2 · 2012-12-15T01:50:33.637

You don't want to use an integer-type column for something like this, as it requires (eventually) re-ordering all other entries in a given 'group'.

When you want to allow entries to be re-position, used a floating type, instead. This will allow changing the order by simply modifying the desired entry.
With a statement along these lines:

UPDATE Example SET ordering = (((SELECT COALESCE(ordering, FLOAT_MAX_VALUE)
                                FROM Example 
                                WHERE id = @entry_above_insertion_point)
                               -
                               (SELECT COALESCE(ordering, FLOAT_MIN_VALUE)
                                FROM Example 
                                WHERE id = @entry_below_insertion_point))
                               / 2)
                              + (SELECT COALESCE(ordering, FLOAT_MIN_VALUE)
                                   FROM Example 
                                   WHERE id = @entry_below_insertion_point)
WHERE id = @entry

This will place the entry 'midway' between the two current entries. Due to the way floating point works, you want to start your value at 0; it's doubtful you'd ever need to normalize the data.

How to prevent recursive trigger call in DB2

2 Answers2