Oracle 触发器

Oracle 11g2：

• DML触发器语法 

  • CREATE [OR REPLACE] TRIGGER trigger_name
    BEFORE|AFTER triggering_event [OF column_name]
    ON table_name
    [FOR EACH ROW]
    [WHEN trigger_condition]
    DECLARE
    /*Declarative section is here */
    BEGIN
    /*Exccutable section is here*/
    EXCEPTION
    /*Exception section is here*/
    END [trigger_name];

• 注意：select 语句查询出来的值必须有使用到：如下 INTO v_sum，否则会报错

• CREATE OR REPLACE TRIGGER trigger_auto_sum
  AFTER INSERT ON ORDERITEM
  FOR EACH ROW
  DECLARE
  v_sum BOOKS.RETAIL%TYPE;
  BEGIN
  IF INSERTING THEN
  SELECT
  SUM(BOOKS.RETAIL*ORDERITEM.QUANTITY) INTO v_sum
  FROM BOOKS,ORDERS,ORDERITEM
  WHERE
  BOOKS.ISBN = ORDERITEM.ISBN
  AND ORDERS.ORDER_ID=ORDERITEM.ORDER_ID
  AND ORDERS.ORDER_ID=:NEW.ORDER_ID;
  END IF;
  DBMS_OUTPUT.PUT_LINE(v_sum);
  END trigger_auto_sum;