Oracle數(shù)據(jù)創(chuàng)建虛擬列和復合觸發(fā)器的方法

create table sales 
 
( 
 
sales_id number, 
 
cust_id number, 
 
sales_amt number, 
 
sale_category varchar2(6) /* 虛擬列名及數(shù)據(jù)類型*/ 
 
generated always as 
 
( 
 
case when sales_amt = 10000 then 'LOW' 
 
when sales_amt > 10000 and sales_amt = 100000 then 'MEDIUM' 
 
when sales_amt > 100000 and sales_amt = 1000000 then 'HIGH' else 'ULTRA' end 
 
) virtual /*虛擬列值函數(shù)內(nèi)容*/ 
 
); 

create or replace trigger tri_sales 
 
for update of sales_amt on SALES 
 
compound trigger 
 
type ty_sales_log is table of sales_log%rowtype 
 
index by pls_integer; 
 
coll_sales_log ty_sales_log; 
 
ctr pls_integer:=0; 
 
before statement is 
 
begin 
 
dbms_output.put_line('in before statement'); 
 
end before statement; 
 
before each row is 
 
begin 
 
dbms_output.put_line('in before each row'); 
 
end before each row; 
 
after each row is 
 
begin 
 
ctr := ctr+1; 
 
dbms_output.put_line('in after each row.sales_amt'||:new.sales_amt); 
 
coll_sales_log(ctr).sales_id := :old.sales_id; 
 
coll_sales_log(ctr).cust_id := :old.cust_id; 
 
coll_sales_log(ctr).sales_amt := :new.sales_amt; 
 
end after each row; 
 
after statement is 
 
begin 
 
dbms_output.put_line('in after statement'); 
 
forall counter in 1..coll_sales_log.count() 
 
insert into sales_log(sales_id,cust_id,sales_amt) 
 
values (coll_sales_log(counter).sales_id,coll_sales_log(counter).cust_id,coll_sales_log(counter).sales_amt); 
 
end after statement;    /* 不能使用Insert into sales_log values 
 
(coll_sales_log(counter));添加*/ 
 
end tri_sales;