一种选择是创建一个名为user_product_history
的表,该表由对user_product
的触发器填充,然后定义一个触发器,如果随后插入行,则将历史表中旧的“删除”行转换为update
。
CREATE TABLE user_product_history (
user_id number,
product_id number,
cost number,
operation_type varchar2(1),
operation_date date
);
CREATE TRIGGER trg_user_product_history
AFTER INSERT OR UPDATE OR DELETE ON user_product
FOR EACH ROW
DECLARE
l_cnt integer;
BEGIN
IF( deleting )
THEN
insert into user_product_history( user_id, product_id, cost, operation_type, operation_date )
values( :old.user_id, :old.product_id, :old.cost, 'D', sysdate );
ELSIF( updating )
THEN
insert into user_product_history( user_id, product_id, cost, operation_type, operation_date )
values( :new.user_id, :new.product_id, :new.cost, 'U', sysdate );
ELSIF( inserting )
THEN
select count(*)
into l_cnt
from user_product_history
where operation_type = 'D'
and user_id = :new.user_id
and product_id = :new.product_id;
if( l_cnt > 0 )
then
update user_product_history
set operation_type = 'U',
operation_date = sysdate,
cost = :new.cost
where operation_type = 'D'
and user_id = :new.user_id
and product_id = :new.product_id;
else
insert into user_product_history( user_id, product_id, cost, operation_type, operation_date )
values( :new.user_id, :new.product_id, :new.cost, 'I', sysdate );
end if;
END IF;
END;
然而从效率角度考虑,删除和插入数据而不是更新数据将会给数据库带来比必要更大的负载。这样做将需要进行大量的I/O。并且你的代码将变得更加复杂以处理这些变化。因此,最好找出哪些行发生了变化,然后只更新这些行。