130826触发器

创建触发器表

SQL> create table product_price_audit (
  2   product_id integer
  3   constraint price_audit_fk_products
  4   references store.products(product_id),old_price number(5,2),
  5   new_price number(5,2)
  6  );
 
Table created
 

创建触发器，当价格低于修改前价格.075倍之后，记录到audit表；

SQL> create or replace trigger before_product_price_update
  2  before update of price
  3  on store.products
  4  for each row when ( new.price < old.price * 0.75)
  5  begin
  6    dbms_output.put_line('Product_id = ' || :old.product_id);
  7    dbms_output.put_line('old price = ' || :old.price);
  8    dbms_output.put_line('new price = ' || :new.price);
  9  
 10  insert into calvin.product_price_audit(
 11  product_id, old_price, new_price ) values (
 12  :old.product_id, :old.price, :new.price );
 13  
 14  end before_product_price_update;
 15  /
 
Trigger created
 
SQL> select product_id, price from store.products order by product_id;
 
                             PRODUCT_ID   PRICE
--------------------------------------- -------
                                      1   29.93
                                      2   30.00
                                      3   32.49
                                      4   13.95
                                      5   49.99
                                      6   14.95
                                      7   13.49
                                      8   12.99
                                      9   10.99
                                     10   15.99
                                     11   14.99
                                     12   13.49
                                     13   13.50
                                     14   16.50
 
14 rows selected
 

测试触发器，查看触发器信息，需要使用该选项；
SQL> set serveroutput on;
SQL> update store.products set price = price * 0.7 where product_id in (5,10);
 
Product_id = 5
old price = 49.99
new price = 34.99
product_id = 5
Old price = 49.99
New price = 34.99
The price reduction is more than 25%
Product_id = 10
old price = 15.99
new price = 11.19
product_id = 10
Old price = 15.99
New price = 11.19
The price reduction is more than 25%
 
2 rows updated
 

查看触发器生成的价格表；
SQL> select * from product_price_audit;
 
                             PRODUCT_ID OLD_PRICE NEW_PRICE
--------------------------------------- --------- ---------
                                      5     49.99     34.99
                                     10     15.99     11.19

查看触发器表；

SQL> select * from user_triggers;
 
TRIGGER_NAME                   TRIGGER_TYPE     TRIGGERING_EVENT                                                                 TABLE_OWNER                    BASE_OBJECT_TYPE TABLE_NAME                     COLUMN_NAME                                                                      REFERENCING_NAMES                                                                WHEN_CLAUSE                                                                      STATUS   DESCRIPTION                                                                      ACTION_TYPE TRIGGER_BODY                                                                     CROSSEDITION BEFORE_STATEMENT BEFORE_ROW AFTER_ROW AFTER_STATEMENT INSTEAD_OF_ROW FIRE_ONCE APPLY_SERVER_ONLY
------------------------------ ---------------- -------------------------------------------------------------------------------- ------------------------------ ---------------- ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------- -------------------------------------------------------------------------------- ----------- -------------------------------------------------------------------------------- ------------ ---------------- ---------- --------- --------------- -------------- --------- -----------------
BEFORE_PRODUCT_PRICE_UPDATE    BEFORE EACH ROW  UPDATE                                                                           STORE                          TABLE            PRODUCTS                                                                                                        REFERENCING NEW AS NEW OLD AS OLD                                                 new.price < old.price * 0.75                                                    ENABLED  before_product_price_update                                                      PL/SQL      begin                                                                            NO           NO               NO         NO        NO              NO             YES       NO
                                                                                                                                                                                                                                                                                                                                                                                                                                                                            before update of price                                                                         dbms_output.put_line('Product_id = ' || :old.product_id);                                                                                                                  
                             

禁用和启用触发器；

SQL> alter trigger calvin.before_product_price_update disable;
 
Trigger altered
 
SQL> alter trigger calvin.before_product_price_update enable;
 
Trigger altered
 

删除触发器；
SQL> drop trigger calvin.before_product_price_update;
 
Trigger dropped