问题描述
我有两张桌子
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.
推荐答案
有几种方法可以防止无限递归,你内置在触发器中,最优雅和高性能的可能是添加一个 触发器函数中
子句:UPDATE
语句的 WHERE
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.
如果batch.is_locked
被定义为NOT NULL
,则WHERE
条件可以简化为:
If batch.is_locked
is defined NOT NULL
, the WHERE
condition can be simplified to:
AND b.is_locked = FALSE;
这篇关于更新后用触发器更新表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
更多推荐
[db:关键词]
发布评论