java PostgreSQL/Derby/Hibernate插入触发器检索插入的记录,外键始终为空
我有一个名为Category with children字段的树结构实体
@OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
@JoinColumn(name = "parent_id")
@OrderColumn
@JsonProperty("children")
private List<Category> children = new LinkedList<Category>();
我还有一个谱系字段,有助于列出树。(看这里:https://stackoverflow.com/a/6277217)
@JsonIgnore
@Column(name = "LINEAGE")
private String lineAge = new String("/");
在类别创建之后,我希望附加到插入类别的沿袭parentId。我为此创建了after insert触发器,但插入行的外键(PARENT_ID)在触发器上下文中始终为null
create trigger add_category_lineage
after insert on CATEGORY
referencing new as insertedrow
for each row
update CATEGORY set LINEAGE = LINEAGE || TRIM(CAST((insertedrow.PARENT_ID AS CHAR(10)))) || '/' where ID = insertedrow.ID and insertedrow.PARENT_ID is not null
我使用的是10.11.1.1德比。为什么插入行中的外键(仅在触发器上下文中)始终为null,它适用于任何其他字段,例如name。也许我可以从hibernate级别执行此操作,而不是使用after insert触发器将父id追加到沿袭字段
编辑1
我用postgreSQL 9.4测试了这个例子,结果是一样的
CREATE OR REPLACE FUNCTION after_insert_category_trigger_function()
RETURNS trigger AS '
BEGIN
UPDATE category set lineage = lineage || new.parent_id::text || ''/'' where id = new.id;
RETURN NEW;
END' LANGUAGE 'plpgsql';
CREATE TRIGGER after_insert_category_trigger
AFTER INSERT ON category
FOR EACH ROW
EXECUTE PROCEDURE after_insert_category_trigger_function();
实体创建后的结果(id=25)。正如您所看到的,它与名称字段(id=23)配合得很好:
它不适用于插入前触发器:
CREATE OR REPLACE FUNCTION after_insert_category_trigger_function()
RETURNS trigger AS '
BEGIN
NEW.LINEAGE := NEW.LINEAGE || new.parent_id::text || ''/'';
RETURN NEW;
END' LANGUAGE 'plpgsql';
CREATE TRIGGER after_insert_category_trigger
BEFORE INSERT ON category
FOR EACH ROW
EXECUTE PROCEDURE after_insert_category_trigger_function();
共 (0) 个答案