62,074
社区成员
发帖
与我相关
我的任务
分享
用户表user
ID name bumen
1 tome 1
2 kate 2
3 james 1
部门表bumen
ID name
1 内务部
2 事务部
user表有个外键fk_UB,指向bumen表的主键
这样的话,当我删除bumen表中的数据的时候,数据库就会报错,因为user表里面还有对应的数据
--下面是现在项目中用的插入约束
create or replace trigger TIB_TSCC0101 before insert
on CSAS.TSCC0101 for each row
declare
integrity_error exception;
errno integer;
errmsg char(200);
dummy integer;
found boolean;
-- Declaration of InsertChildParentExist constraint for the parent "TXCD0003"
cursor cpk1_tscc0101(var_cost_center varchar,
var_product_code varchar) is
select 1
from TXCD0003
where COST_CENTER = var_cost_center
and PRODUCT_CODE = var_product_code
and var_cost_center is not null
and var_product_code is not null;
begin
-- Parent "TXCD0003" must exist when inserting a child in "CSAS.TSCC0101"
if :new.COST_CENTER is not null and
:new.PRODUCT_CODE is not null then
open cpk1_tscc0101(:new.COST_CENTER,
:new.PRODUCT_CODE);
fetch cpk1_tscc0101 into dummy;
found := cpk1_tscc0101%FOUND;
close cpk1_tscc0101;
if not found then
errno := -20002;
errmsg := 'Parent does not exist in "TXCD0003". Cannot create child in "CSAS.TSCC0101".';
raise integrity_error;
end if;
end if;
-- Errors handling
exception
when integrity_error then
raise_application_error(errno, errmsg);
end;