我又修改了一下:
create or replace trigger AU_I_UNIT_IS_HAVE_ACC
after insert or delete on au_i_bank
for each row
declare
rscount int;
begin
if INSERTING THEN
update au_i_unit set IS_HAVE_ACC='√' where unit_code=:new.unit_code;
end if;
if DELETING THEN
select count(*) into rscount from au_i_bank where unit_code=:old.unit_code;
if rscount =0 then
update au_i_unit set IS_HAVE_ACC='X' where unit_code=:old.unit_code;
end if;
end if;
end AU_I_UNIT_IS_HAVE_ACC;
这样INSERTING可以了,但DELETING报错如何修改?
create or replace trigger AU_I_UNIT_IS_HAVE_ACC
after insert or delete on au_i_bank
for each row
declare
rscount int;
begin
if INSERTING THEN
update au_i_unit set IS_HAVE_ACC='√' where unit_code=:new.unit_code;
end if;
if DELETING THEN
select count(*) into rscount from au_i_bank where unit_code=:old.unit_code;
if rscount =0 then
update au_i_unit set IS_HAVE_ACC='X' where unit_code=:old.unit_code;
end if;
end if;
end AU_I_UNIT_IS_HAVE_ACC;
这样INSERTING可以了,但DELETING报错如何修改?
解决方案 »
- oracle中创建触发器错误,
- ORA-12170:TNS:连接超时(急)
- 包含与不包含有交集怎么做?
- 这个更新太诡异了。高手进
- CLOB、BLOB字段的表无法导入,ORA-01536: 超出表空间 'SYSTEM' 的空间限额。
- 关于PL/SQL组合查询结果集处理问题!
- 如何得到schema下每张表的记录数以及表所占用的空间?
- 程序能够运行, 但当连接到SQL/PLUS出现提示:ORA-12514 : TNS: listener could not resolve SERVICE_NAME given in connect
- 从多台PC读取表同一字段,每台PC所得到得值不同,一条纪录只能被一台PC读取,请问如何解决同步问题?急!
- 大神们来给我看看这是什么问题啊 ,弄了两天了,不知道为什么
- 不太懂数据库,请问如果想在库中增加很多条纪录(10万条),怎么做
- 这个触发器如何写?急问!!!!!!
你要的功能就是当某类信息au_i_bank 不存在时,au_i_unit中IS_HAVE_ACC='X'
用job应该可以达到你的目的,定时监测au_i_unit中unit_code与au_i_unit中的对应不起来的
项目,update IS_HAVE_ACC='X'
function getRsCount(unitcode varchar2)
return int; end AU_I_UNIT_pack;
create or replace package body AU_I_UNIT_pack is function getRsCount(unitcode varchar2)
return int
is
rscount int;
begin
select count(*) into rscount from au_i_bank where unit_code = unitcode;
return rscount;
end getRsCount;
end AU_I_UNIT_pack;
create or replace trigger AU_I_UNIT_IS_HAVE_ACC
after insert or delete on au_i_bank
for each row
declare
rscount int;
unitcode varchar2(30);
begin
if INSERTING THEN
update au_i_unit set IS_HAVE_ACC='√' where unit_code=:new.unit_code;
end if;
if DELETING THEN
unitcode:=:old.unit_code;
rscount :=AU_I_UNIT_pack.Getrscount(unitcode);
if rscount =0 then
update au_i_unit set IS_HAVE_ACC='X' where unit_code=unitcode;
end if;
end if;
end AU_I_UNIT_IS_HAVE_ACC;
还是不行,错在哪?
rscount int := 0;
end AU_I_UNIT_pack;create or replace trigger AU_I_UNIT_IS_HAVE_ACC
after insert or delete on au_i_bank
for each row
declare
unitcode varchar2(30);
begin
if INSERTING THEN
AU_I_UNIT_pack.rscount :=AU_I_UNIT_pack.rscount + 1;
update au_i_unit set IS_HAVE_ACC='√' where unit_code=:new.unit_code;
end if;
if DELETING THEN
AU_I_UNIT_pack.rscount :=AU_I_UNIT_pack.rscount - 1;
unitcode:=:old.unit_code;
if AU_I_UNIT_pack.rscount =0 then
update au_i_unit set IS_HAVE_ACC='X' where unit_code=unitcode;
end if;
end if;
end AU_I_UNIT_IS_HAVE_ACC;
我是想知道表AU_I_BANK在UNIT_CODE字段条件时是否有数据
Step 1: 建立表au_i_bank的一个before insert or delete 行级触发器,将insert 或delete的
unit_code记录下来,可以使用包中的嵌套表
Step2 : 建立表au_i_bank的一个after insert or delete 语句级触发器,对本次操作的每个unit_code查询表au_i_bank,看看是否还有记录,然后更改表au_i_unit