oracle trigger for table on commit

来源：互联网发布：淘宝历史订单无故消失编辑：程序博客网时间：2024/05/21 19:21

1. create a table

create table ShoppingCart

( ProductID number(6),

SalePrice number(8,2),

SaleDate date,

SaleBatchID number(6),

CustomerID number(6)

);

insert into SHOPPINGCART values(11112,23.34,sysdate,11112,11112);

insert into SHOPPINGCART values(11113,55.12,sysdate,11113,11113);

insert into SHOPPINGCART values(11114,27.54,sysdate,11114,11114);

2. create materialized view for table

create materialized view log on ShoppingCart

with rowid

including new values ;

create materialized view ShoppingCart_mvw

refresh fast on commit

with rowid

enable query rewrite

as select * from ShoppingCart;

3. create trigger for table on commit of update

create or replace trigger ShoppingCart_mvw_tri

after update on ShoppingCart_mvw

for each row

begin

PROCSENDEMAIL('new content ','Change ProductID='||:new.ProductID||' SaleDate='||:new.SaleDate,

'ORACLE_DB','xxxx@163.com;yyy@126.com','smtp.163.com');

end;

4. testing

1) don not commit;

update ShoppingCart set SaleDate='16-JUL-08' where ProductID =11112;

2) do commit;

Rollback;

update ShoppingCart set SaleDate='16-JUL-08' where ProductID =11112;

commit;

------------------------------------------------------------

PROCEDURE PROCSENDEMAIL(P_TXT VARCHAR2,

P_SUB VARCHAR2,

P_SENDOR VARCHAR2,

P_RECEIVER VARCHAR2,

P_SERVER VARCHAR2,

P_PORT NUMBER DEFAULT 25,

P_NEED_SMTP INT DEFAULT 0,

P_USER VARCHAR2 DEFAULT NULL,

P_PASS VARCHAR2 DEFAULT NULL,

P_FILENAME VARCHAR2 DEFAULT NULL,

P_ENCODE VARCHAR2 DEFAULT 'bit 7')

------------------------------------------------------------

************************************************************

**** See PROCSENDEMAIL in :

http://blog.csdn.net/hiyu2218/archive/2008/07/17/2666722.aspx

************************************************************

------------------------------------------------------------