Firebird - After Insert Or Update Trigger

I have a calculation in my DB need to update 'field1' for 'table1' after the update trigger. The problem that updating that field will cause the after update trigger to fire and ex

Solution 1:

One can use a custom locking mechanism based on context variables which prevent from repeating invocation of AFTER UPDATE trigger.

CREATETRIGGER au FORtable
  AFTER UPDATE
  POSITION 0ASBEGIN
  IF RDB$GET_CONTEXT('USER_TRANSACTION', 'MY_LOCK') ISNULLTHENBEGIN
    RDB$SET_CONTEXT('USER_TRANSACTION', 'MY_LOCK', 1);

    ...
    Do your update operations here
    ...

    RDB$SET_CONTEXT('USER_TRANSACTION', 'MY_LOCK', NULL);
  ENDWHENANY DO
  BEGIN
    RDB$SET_CONTEXT('USER_TRANSACTION', 'MY_LOCK', NULL);
    EXCEPTION;
  ENDEND

Solution 2:

The obvious answer is to switch to the BEFORE UPDATE trigger, as pointed out by J Cooper... however, if there is some reason you absolutely have to use AFTER UPDATE then you have to set up a flag which tells that the field needs to be recalculated and check it in your trigger. One way to do it would be to set the field to NULL in BEFORE trigger and then check against NULL in AFTER trigger, ie

CREATETRIGGER bu_trigger BEFORE UPDATEBEGIN-- mark field for recalculation if needed
  IF(update invalidates the field1 value)THEN
     NEW.field1 =NULL;
ENDCREATETRIGGER au_trigger AFTER UPDATEBEGIN-- only update if the field is NULL
  IF(NEW.field1 ISNULL)THENUPDATE table1 SET field1 = ...;
END

But using this technique you probably have to use lot of IF(OLD.field IS DISTINCT FROM NEW.field)THEN checks in triggers to avoid unnessesary updates in your triggers.

Solution 3:

simple solution...

fire the update only if the NEW.FIELD1 value is really new, like this:

CREATETRIGGER au FOR table1
  AFTER UPDATE
  POSITION 0ASDECLARE VARIABLE TMP AS NUMBER(15,5); -- SAME DATATYPE OF FIELD1BEGIN-- MAKE YOUR CALCULATION
  TMP=CALCULATEDVALUE;
  -- UPDATE THE ROW ONLY IF THE VALUES IS CHANGED
  IF (TMP<>NEW.FIELD1) UPDATE TABLE1 SET FIELD1=:TMP WHERE KEY=NEW.KEY; -- PAY ATTENTION IF TMP OR NEW.FIELD1 CAN BE NULL. IN THIS CASE USE COALESCE OR A DIFFERENCE COMPARISONEND

Solution 4:

Answer : Use BEFORE UPDATE TRIGGER instead of AFTER UPDATE TRIGGER

CREATETRIGGER some_trigger BEFORE UPDATE ... etc