我又修改了一下:
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 ERP克隆后,在 solaris 上 tnsping 不通
- 求写oraclesql向一张表循环插入数据,有测试数据
- sql 多条数据合并一条
- ${ORACLE_HOME}/precomp/lib32中的文件有什么作用,能不能举个例子
- oracle11 可以用isqlplus 吗?
- Help! Oracle10g自动管理回滚段模式下,如何新增回滚段。进来有详细说明。送分啦
- 有关full join的问题
- 建立触发器出错。各位大虾救命!!
- 这段SQL语句错在哪里?
- Oracle生僻字乱码怎样解决
- 不太懂数据库,请问如果想在库中增加很多条纪录(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