我想在 PostgreSQL 中创建触发器。
逻辑很简单。
我需要触发器,如果published_at更新并且writing_at为空,则将published_at设置为writing_at。
我写了这个,但失败了。有人有想法吗?
CREATE function setWrittenAt() RETURNS trigger;
AS
DECLARE old_id INTEGER;
BEGIN ;
old_id = OLD.id
IF NEW.published_at IS NOT and NEW.written_at IS null
THEN
UPDATE review SET NEW.written_at = NEW.published_at where id = old_id;
END IF ;
RETURN NEW;
END;
LANGUAGE plpgsql;
CREATE TRIGGER update_written_at
AFTER UPDATE OF published_at ON review
WHEN (OLD.published_at IS DISTINCT FROM NEW.published_at)
EXECUTE PROCEDURE setWrittenAt();
Error:
语法错误:7 错误:“DECLARE”处或附近的语法错误
第 3 行:声明 old_id INTEGER;
您的代码中有多个错误:
-
IS NOT
不是您需要的有效表达式IS NOT NULL
.
- After
BEGIN
和returns
条款必须没有;
- 您忘记将函数体作为字符串括起来(如果使用则更容易编写美元报价
- 你也不需要不必要的(额外的)
UPDATE
如果你把它做成before
trigger
CREATE function setwrittenat()
RETURNS trigger
AS
$$
BEGIN
IF NEW.published_at IS NOT NULL and NEW.written_at IS null THEN
NEW.written_at := = NEW.published_at; --<< simply assign the value
END IF;
RETURN NEW;
END;
$$
LANGUAGE plpgsql;
然后使用一个BEFORE
扳机:
CREATE TRIGGER update_written_at
BEFORE UPDATE OF published_at ON review
WHEN (OLD.published_at IS DISTINCT FROM NEW.published_at)
FOR EACH ROW
EXECUTE PROCEDURE setWrittenAt();
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)