Update A Table With A Trigger After Update

I have two tables batch (batch_id,start_date,end_date,batch_strength,is_locked) sem (user_id,is_active,no_of_days) I have executed the trigger procedure given below then update

Solution 1:

There are several ways to prevent the infinite recursion you built into your trigger, the most elegant and performant probably adding a WHERE clause to the UPDATE statement in your trigger function:

CREATEOR REPLACE FUNCTION em_batch_update()
  RETURNStriggerAS
$func$
BEGINUPDATE batch b
SET    is_locked =TRUEFROM   sem s
WHERE  s.is_active
AND    s.user_id ='OSEM'AND    b.start_date <= (current_date- s.no_of_days)
AND    b.is_locked ISDISTINCTFROMTRUE; -- prevent infinite recursion!RETURNNULL;

END
$func$  LANGUAGE plpgsql;

CREATETRIGGER em_sem_batch
BEFORE UPDATEON batch
FOREACH STATEMENT
EXECUTEPROCEDURE em_batch_update();

I changed a few other things to move towards sanity:

• Since the trigger function does the same for every row, I changed it into a potentially much cheaper statement-level trigger.

• Consequently, I made the trigger function RETURN NULL, because, I quote the manual here:

Trigger functions invoked by per-statement triggers should always return NULL.

• batch.is_locked and sem.is_active look like boolean columns. Use a proper boolean data type for them. My code is building on it.

• I also rewrote your UPDATE query completely. In particular the condition on batch.start_date so that an index can be used if available.

• If batch.is_locked is defined NOT NULL, the WHERE condition can be simplified to:

      AND    b.is_locked = FALSE;
  

Solution 2:

Your UPDATE trigger runs another UPDATE on the same table, which will fire the trigger again, so you get infinite recursion. You probably need to redesign this a little bit, but it's hard to say how without an explanation of what you're trying to do.

Solution 3:

Infinite recursion in this case because update trigger will do update operation on table batch and the same will triggered after execution of update statement inside em_sem_batch trigger itself.To prevent this add one column in table and in update statement of trigger update that column also to some value and add an if condition to check whether that column has that constant value if so avoid execution of update statement else execute update statement. See example below:

CREATEFUNCTION public.trigger_fuction()
    RETURNStriggerLANGUAGE'plpgsql'NOT LEAKPROOF
AS $BODY$
BEGIN
  IF NEW.data_replicated=trueTHENUPDATE sample SET data_replicated=falseWHERE id=NEW.id;
    raise notice 'changed data replicated of sample with id as %',NEW.ID;
  END IF;
  RETURNNEW;
END;
$BODY$;


CREATETRIGGER data_replication_trigger
  AFTER UPDATEON sample
  FOREACHROWEXECUTEPROCEDURE trigger_fuction();

In this example sample table has data_replicated boolean field which will be updated when trigger is executed.