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
– Clockwork-Muse, Commented Dec 12, 2012 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: stackoverflow.com/questions/13845776/…, but I'm still open to any other good alternative for doing it. — Edgar Catalán
– Edgar Catalán, Commented Dec 13, 2012 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
– Clockwork-Muse, Commented Dec 13, 2012 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
– Edgar Catalán, Commented Dec 14, 2012 at 23:55

Edgar Catalán · Accepted Answer · 2012-12-13 21:12:26Z

0

CREATE TRIGGER NO CASCADE BEFORE UPDATE ...

edited Dec 13, 2012 at 21:12

Edgar Catalán

658 bronze badges

answered Dec 13, 2012 at 6:18

Alexey

1,9243 gold badges17 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Edgar Catalán Over a year ago

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 (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 =(

Clockwork-Muse · Accepted Answer · 2012-12-15 01:50:33Z

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.

Collectives™ on Stack Overflow

How to prevent recursive trigger call in DB2

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related