CREATE OR REPLACE TRIGGER HRBEURS.TR_ADD_XXY_KH_GZL_TY
BEFORE INSERT
ON HRBEURS.XXY_KH
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
declare
tmp int;
begin
GZL:= 100*Z/A+N;
select (select count(*) from XXY_KH where JGH = :new.JGH and ND = :new.ND) into tmp from dual;
if (tmp > 0) then
update XXY_KH set GZL = '54' where JGH = :new.JGH and ND = :new.ND;
DBMS_OUTPUT.PUT_LINE('updatesql');
raise_application_error(-20009,'The application not alow execute this action');
else
null;
DBMS_OUTPUT.PUT_LINE('ok');
end if;
end;
/就是向一个表中插入记录,如果已经存在就更新原先的记录 如果不存在就插入
我这个触发器插入一条新的记录之后(记录已经存在) 是没有插入新的记录但也没有更新原先符合条件的记录
大家帮我看看有什么毛病
为什么更新语句
update XXY_KH set GZL = '54' where JGH = :new.JGH and ND = :new.ND; 不执行呢
BEFORE INSERT
ON HRBEURS.XXY_KH
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
declare
tmp int;
begin
GZL:= 100*Z/A+N;
select (select count(*) from XXY_KH where JGH = :new.JGH and ND = :new.ND) into tmp from dual;
if (tmp > 0) then
update XXY_KH set GZL = '54' where JGH = :new.JGH and ND = :new.ND;
DBMS_OUTPUT.PUT_LINE('updatesql');
raise_application_error(-20009,'The application not alow execute this action');
else
null;
DBMS_OUTPUT.PUT_LINE('ok');
end if;
end;
/就是向一个表中插入记录,如果已经存在就更新原先的记录 如果不存在就插入
我这个触发器插入一条新的记录之后(记录已经存在) 是没有插入新的记录但也没有更新原先符合条件的记录
大家帮我看看有什么毛病
为什么更新语句
update XXY_KH set GZL = '54' where JGH = :new.JGH and ND = :new.ND; 不执行呢
则执行这个触发器是插入之前触发,那么
tmp 就永远为0
PS:oracle9i及以上版本