表A(
id number primary key,
hitnum number, --使用次数
type char(10)--类型
)
表B(
id munber,--标识
type char(10),类型
num nmber,--总使用次数,为A表中同一类型的使用次数总和
totalNum number,--为A表中同一类型的记录数量
)在oracle中怎样建立触发器可以使往A表中增删改数据都让B中的记录与A同步,并且每次更新使系统开销尽可能小(A表中可能有同万条记录)。根据实际情况,B不能用创建视图的方案代替,在这只能用触发器了,请教各位!
id number primary key,
hitnum number, --使用次数
type char(10)--类型
)
表B(
id munber,--标识
type char(10),类型
num nmber,--总使用次数,为A表中同一类型的使用次数总和
totalNum number,--为A表中同一类型的记录数量
)在oracle中怎样建立触发器可以使往A表中增删改数据都让B中的记录与A同步,并且每次更新使系统开销尽可能小(A表中可能有同万条记录)。根据实际情况,B不能用创建视图的方案代替,在这只能用触发器了,请教各位!
create table A(
id number primary key,
hitnum number,
type char(10)
);create sequence seq_A start with 1;create table B(
id number,
type char(10),
num number,
totalnum number
);create or replace trigger trig_A
after delete or insert or update on A
for each row
declare
v_count integer;
begin
if deleting then
begin
update B set B.num = B.num - :old.hitnum, b.totalnum = b.totalnum -1
where b.type = :old.type;
delete from b where b.num = 0 and b.totalnum = 0;
end;
elsif inserting then
begin
select count(*) into v_count from b where type = :new.type;
if v_count = 0 then
insert into b(type,num,totalnum) values(:new.type,:new.hitnum,1);
elsif v_count = 1 then
update B set B.num = B.num + :new.hitnum, b.totalnum = b.totalnum + 1
where b.type = :new.type;
end if;
end;
elsif updating then
begin
update B set B.num = B.num + :new.hitnum - :old.hitnum
where b.type = :new.type;
end;
end if;
end;
都是更新表B.不是INSERT ONLY的那种。(等于说表B里面id是起到主键的作用)CREATE TRIGGER 表A_TRIG
AFTER INSERT OR UPDATE OR DELETE
ON 表A
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO 表B VALUES(:NEW.id,:NEW.type,:NEW.hitnum,1);
ELSIF UPDATING THEN
UPDATE 表B SET num = num + :NEW.hitnum WHERE ID =:NEW.id;
ELSIF DELETING THEN
DELETE FROM 表B WHERE ID =:NEW.id;
END IF ;
END ;只是INSERT的话:CREATE TRIGGER 表A_TRIG
AFTER INSERT OR UPDATE OR DELETE
ON 表A
FOR EACH ROW
BEGIN INSERT INTO 表B
SELECT ID,MAX(TYPE),SUM(hitnum),COUNT(1)
FROM 表A
WHERE ID = :NEW.id
GROUP BY ID; END ;类似这样?