Hibernate对视图的关联删除
场景
业务实体是基于视图加载的,具体业务场景需要对此实体进行删除。Hibernate会有如下执行删除操作的SQL:DELETE FROM V_CPM_ATX;
这样因为视图中的具体表之间存在有主/外键的约束,这样就会抛出异常。
解决
考虑在视图上建立此触发器用来处理,如下:
CREATE OR REPLACE TRIGGER "TI_V_CPM_TX_ATX_D"
INSTEAD OF DELETE ON V_CPM_TX_ATX
FOR EACH ROW
DECLARE
BEGIN
IF :OLD.TX_ID IS NOT NULL THEN
DELETE FROM CPM_ATX A WHERE A.TX_ID = :OLD.TX_ID;
DELETE FROM CPM_TX A WHERE A.TX_ID = :OLD.TX_ID;
ELSE
BEGIN
FOR ATX IN (SELECT TX_ID FROM V_CPM_TX_VTX) LOOP
DELETE FROM CPM_ATX A WHERE A.TX_ID = ATX.TX_ID;
DELETE FROM CPM_TX A WHERE A.TX_ID = ATX.TX_ID;
END LOOP;
END;
END IF;
END TI_V_CPM_TX_ATX_D;