oracle9i 有2个表tab1
(
id varchar2(10), -- 主键
name varchar2(80),
tab2_id varchar2(10) -- 引用tab2.id,有重复引用情况
);tab2
(
id varchar2(10), -- 主键
ref number
);
需求是: 当tab1做 insert 或 delete 时,tab2.ref 值总是自动更新为tab1中引用tab2_id的个数,
如果tab1做insert 时,tab1.tab2_id 在 tab2 中不存在,要自动添加,并且置 ref = 1,
要求对 tab2.ref 的更新是锁定的(即不能减成负数)不知道我说清楚了没 ^_^ , 谢谢了
(
id varchar2(10), -- 主键
name varchar2(80),
tab2_id varchar2(10) -- 引用tab2.id,有重复引用情况
);tab2
(
id varchar2(10), -- 主键
ref number
);
需求是: 当tab1做 insert 或 delete 时,tab2.ref 值总是自动更新为tab1中引用tab2_id的个数,
如果tab1做insert 时,tab1.tab2_id 在 tab2 中不存在,要自动添加,并且置 ref = 1,
要求对 tab2.ref 的更新是锁定的(即不能减成负数)不知道我说清楚了没 ^_^ , 谢谢了
insert就+1
delete就-1
不存在锁定的问题啊
AFTER INSERT or DELETE ON table_name
FOR EACH ROWBEGIN
CASE
WHEN inserting THEN
INSERT INTO TAB2(ID) VALUES(:NEW.ID);
UPDATE INTO TAB2(REF) SET REF = REF + 1 WHERE ID = :NEW.ID
WHEN deleting THEN
DELETE FROM TAB2 WHERE ID = :OLD.ID;
UPDATE TAB2(REF) SET REF = REF - 1 WHERE ID = :OLD.ID
END CASE;
END;试试这个,没调试的,如果正常插入ref的话不会出现负数,所以没有判断。
after insert or delete on tab1
for each row
declare
v_count integer default 0;
begin
if inserting then
select count(1) into v_count from tab2 where id = :new.tab2_id ;
if v_count = 0 then
insert into tab2 (id, ref) values(:new.tab2_id ,1);
else
update tab2 set ref = ref + 1 where id = :new.tab2_id ;
end if;
end if;
if deleting then
select nvl(ref,0) into v_count from tab2 where id = :old.tab2_id ;
if v_count >0 then
update tab2 set ref = ref -1 where id = :old.tab2_id ;
end if;
end if;
end trigger_test;
create or replace trigger trigger_test_1
after insert or delete on tab1
for each row
declare
v_count integer default 0;
begin
if inserting then
select count(1) into v_count from tab2 where id = :new.tab1_id ;
if v_count = 0 then
insert into tab2 (id, ref) values(:new.tab1_id ,1);
else
update tab2 set ref = ref + 1 where id = :new.tab1_id ;
end if;
end if;
if deleting then
select nvl(ref,0) into v_count from tab2 where id = :old.tab1_id ;
if v_count >0 then
update tab2 set ref = ref -1 where id = :old.tab1_id ;
end if;
end if;
end trigger_test;