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 the table using query

CREATE OR REPLACE FUNCTION em_batch_update()
  RETURNS trigger AS $em_sem_batch$
BEGIN

UPDATE batch set is_locked='TRUE'
where (start_date
       + (select no_of_days from sem
          WHERE is_active='TRUE' and user_id='OSEM')
      ) <= current_date;

return NEW;

END;
$em_sem_batch$  LANGUAGE plpgsql;

CREATE TRIGGER em_sem_batch
BEFORE UPDATE ON batch FOR EACH ROW EXECUTE PROCEDURE em_batch_update();

update em_batch set batch_strength=20 where batch_id='OD001C001B3';

Error occured:

ERROR: stack depth limit exceeded
HINT: Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.

回答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:

CREATE OR REPLACE FUNCTION em_batch_update()
  RETURNS trigger AS
$func$
BEGIN

UPDATE batch b
SET    is_locked = TRUE
FROM   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 IS DISTINCT FROM TRUE; -- prevent infinite recursion!

RETURN NULL;

END
$func$  LANGUAGE plpgsql;

CREATE TRIGGER em_sem_batch
BEFORE UPDATE ON batch
FOR EACH STATEMENT
EXECUTE PROCEDURE 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;
  

回答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.

回答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:

CREATE FUNCTION public.trigger_fuction()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    NOT LEAKPROOF
AS $BODY$
BEGIN
  IF NEW.data_replicated=true THEN
    UPDATE sample SET data_replicated=false WHERE id=NEW.id;
    raise notice 'changed data replicated of sample with id as %',NEW.ID;
  END IF;
  RETURN NEW;
END;
$BODY$;


CREATE TRIGGER data_replication_trigger
  AFTER UPDATE
  ON sample
  FOR EACH ROW
  EXECUTE PROCEDURE trigger_fuction();

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