oracle trigger for table on commit

 

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

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

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