更新后使用触发器更新表 [英] Update a table with a trigger after update

问题描述

我有两个桌子

 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';

发生错误:

错误:超出堆栈深度限制
提示:增加配置参数"max_stack_depth"(当前为2048kB)， 在确保平台的堆栈深度限制足够之后.

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.

推荐答案

有几种方法防止在触发器中内置的无限递归，最优雅，最有效的方法可能是添加WHERE触发函数中UPDATE语句的子句:

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:

• 由于触发器函数对每一行都执行相同的操作，因此我将其更改为可能更便宜的 因此，我制作了触发函数RETURN NULL，因为

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

  按语句触发的触发器函数应始终 返回NULL.

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

  • batch.is_locked和sem.is_active看起来像布尔列.为它们使用正确的 boolean数据类型.我的代码正在此基础上构建.

    • 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.

      我也完全重写了您的UPDATE查询.特别是batch.start_date上的条件，以便可以使用索引(如果可用).

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

      如果在NOT NULL中定义了batch.is_locked，则可以将WHERE条件简化为:

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

          AND    b.is_locked = FALSE;
      

    这篇关于更新后使用触发器更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！