我用了一个N土的办法解决了问题,但是觉得太土,实在不爽,究竟是Oracle太笨还是我太笨?有没有好的办法,高手们帮忙想点办法啊!create or replace trigger TG_CheckPropCapTypeName after insert or update on TB_NetworkPropType declare nCount int; begin select max(nCount1) into nCount from (select count(*) as nCount1 from TB_NetworkPropType group by nNetworkTypeID,UPPER(sName)); if (nCount > 1) then RAISE_APPLICATION_ERROR(-20000, '属性或技术指标不允许重名'); end if; end;
刚才又把代码优化了一下,不过还是N土:create or replace trigger TG_CheckPropCapTypeName after insert or update on TB_NetworkPropType declare nCount int; begin select max(count(*)) into nCount from TB_NetworkPropType group by nNetworkTypeID,UPPER(sName); if (nCount > 1) then RAISE_APPLICATION_ERROR(-20000, '属性或技术指标不允许重名'); end if; end;
可以先插入或修改,然后删除表中重复项 delete from TB_NetworkPropType a where a.rowid !=( select max(rowid) from a b where a.nNetworkTypeID =b.nNetworkTypeID and a.sName=b.sName);
after insert or update...
不然,你的TRIGGER失效了怎么办
或者把判断放到前端
declare
nCount int;
begin
select max(nCount1) into nCount from (select count(*) as nCount1 from TB_NetworkPropType group by nNetworkTypeID,UPPER(sName));
if (nCount > 1)
then
RAISE_APPLICATION_ERROR(-20000, '属性或技术指标不允许重名');
end if;
end;
declare
nCount int;
begin
select max(count(*)) into nCount from TB_NetworkPropType group by nNetworkTypeID,UPPER(sName);
if (nCount > 1)
then
RAISE_APPLICATION_ERROR(-20000, '属性或技术指标不允许重名');
end if;
end;
delete from TB_NetworkPropType a where a.rowid !=( select max(rowid)
from a b where a.nNetworkTypeID =b.nNetworkTypeID and a.sName=b.sName);