是这样的:
由于两张基表的字段比较多,这里就不创建基表了。--创建物化视图日志
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;上面的语句是没有问题的,都可以成功创建,但是当我测试的时候,我在基本表WCMMETATABLEBOOK_BASEINFO中修改了一条数据: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输出的结果很是诧异,找了一个下午也不知道是什么原因,这种情况会是什么原因呢?
由于两张基表的字段比较多,这里就不创建基表了。--创建物化视图日志
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;上面的语句是没有问题的,都可以成功创建,但是当我测试的时候,我在基本表WCMMETATABLEBOOK_BASEINFO中修改了一条数据: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输出的结果很是诧异,找了一个下午也不知道是什么原因,这种情况会是什么原因呢?
并不是update物化视图
1 row deleted
SQL> commit;
Commit complete
SQL> EXEC DBMS_MVIEW.REFRESH('MV_bookexpinfo', 'f');
删除
PL/SQL procedure successfully completed
SQL>
删除竟然是对的,难道真是你所说,修改时先删除后添加。那要是这样的话,修改该如何处理呢?