17,377
社区成员
发帖
与我相关
我的任务
分享
--创建物化视图日志
create materialized view log on WCMMETATABLEBOOK_BASEINFO with rowid;
create materialized view log on WCMMETATABLEBOOK_EXTINFO with rowid;
--创建物化视图
create materialized view MV_BOOKEXPINFO
refresh force on demand
start with sysdate next sysdate + 5 / (24 * 60)
as
select t1.rowid as baserowid,t2.rowid as exprowid,t1.metadataid,t1.RESOURCEUNIQUEID,t1.pubdate,t1.creator,t1.issets,t1.setsname,t1.setsorder,t1.title,t2.hasflip,t2.sellprice,t2.price,t2.isbn,t2.canprint from WCMMETATABLEBOOK_BASEINFO t1,WCMMETATABLEBOOK_EXTINFO t2 where t1.RESOURCEUNIQUEID = t2.RESOURCEUNIQUEID;
--创建触发器
create or replace trigger TRI_bookexpinfo
after insert or update or delete on mv_bookexpinfo
for each row
declare
bid int;
begin
if deleting then
dbms_output.put_line('删除');
update bookexpinfo set BOOK_ISDEL='1' where BOOK_TRSID=:old.RESOURCEUNIQUEID;
end if;
if inserting then
dbms_output.put_line('插入');
select bookid.nextval into bid from dual;
insert into bookexpinfo(book_id,BOOK_TRSID,BOOK_DATE,BOOK_AUTHOR,BOOK_ISSETS,BOOK_SETSNAME,BOOK_SETSORDER,BOOK_TYPE,BOOK_NAME,BOOK_SALEPRICE,BOOK_PRICE,BOOK_ISBN,canPrint)
values(bid,:new.RESOURCEUNIQUEID,:new.PUBDATE,:new.CREATOR,:new.ISSETS,:new.SETSNAME,:new.SETSORDER,:new.HASFLIP,:new.TITLE,:new.SELLPRICE,:new.PRICE,:new.ISBN,:new.CANPRINT);
end if;
if updating then
dbms_output.put_line('修改');
update bookexpinfo set BOOK_DATE=:new.PUBDATE,BOOK_AUTHOR=:new.CREATOR,BOOK_ISSETS=:new.ISSETS,BOOK_SETSNAME=:new.SETSNAME,BOOK_SETSORDER=:new.SETSORDER,BOOK_TYPE=:new.HASFLIP,BOOK_NAME=:new.TITLE,BOOK_SALEPRICE=:new.SELLPRICE,BOOK_PRICE=:new.PRICE,BOOK_ISBN=:new.ISBN,canPrint=:new.CANPRINT where book_trsid=:old.RESOURCEUNIQUEID;
end if;
end TRI_bookexpinfo;
SQL> update wcmmetatablebook_baseinfo set creator='bbbb' where RESOURCEUNIQUEID='B_50602850_001';
1 row updated
SQL> commit;
Commit complete
SQL> EXEC DBMS_MVIEW.REFRESH('MV_bookexpinfo', 'f');
删除
插入
PL/SQL procedure successfully completed
delete from wcmmetatablebook_baseinfo where RESOURCEUNIQUEID='B_50602850_001';
1 row deleted
SQL> commit;
Commit complete
SQL> EXEC DBMS_MVIEW.REFRESH('MV_bookexpinfo', 'f');
删除
PL/SQL procedure successfully completed
SQL>