新手问个insert or delete 触发器的写法

[myy]

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 的更新是锁定的（即不能减成负数）

不知道我说清楚了没 ^_^ , 谢谢了

[AFIC]

ref最开始是0
insert就+1
delete就-1
不存在锁定的问题啊

[wiler]

不明白

[Eric_1999]

CREATE OR REPLACE TRIGGER trigger_name
AFTER INSERT or DELETE ON table_name
FOR EACH ROW

BEGIN
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的话不会出现负数，所以没有判断。

[xiaoxiao1984]

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.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;

[ximan1984]

笨猫写的应该不错呀。。。。。
高手我喜欢：）

[bingqian]

笨猫应该是这样吧：
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;