Oracle/PLSQL: AFTER DELETE Trigger

 

Oracle/PLSQL: AFTER DELETE Trigger

---

An AFTER DELETE Trigger means that Oracle will fire this trigger after the DELETE operation is executed.

译： AFTER DELETE 表示在 DELETE 操作执行后， ORACLE 会引发该触发器

The syntax for an AFTER DELETE Trigger is:

译： AFTER DELETE 触发器的语法如下：

CREATE or REPLACE TRIGGER trigger_name
AFTER DELETE
    ON table_name
    [ FOR EACH ROW ]
DECLARE
    -- variable declarations
BEGIN
    -- trigger code
EXCEPTION
    WHEN ...
    -- exception handling
END;

trigger_name is the name of the trigger to create.

译： trigger_name 表示创建的触发器名

Restrictions:

·   You can not create an AFTER trigger on a view.

·   You can not update the :NEW values.

·   You can not update the :OLD values.

译：

限制：

·   不能够在视图上创建 AFTER 触发器。

·   不能够更新 :NEW 的值。

·   不能够更新 :OLD 的值。

For example:

If you had a table created as follows:

译：如果你有一个如下的表：

CREATE TABLE orders
(	order_id	number(5),
	quantity	number(4),
	cost_per_item	number(6,2),
	total_cost	number(8,2)
);

We could then create an DELETE UPDATE trigger as follows:

译：我们像下面这样创建一个 DELETE UPDATE 触发器：

CREATE OR REPLACE TRIGGER orders_after_delete
AFTER DELETE
    ON orders
    FOR EACH ROW

DECLARE
    v_username varchar2(10);

BEGIN

    -- Find username of person performing the DELETE on the table
    SELECT user INTO v_username
    FROM dual;

    -- Insert record into audit table
    INSERT INTO orders_audit
     ( order_id,
       quantity,
       cost_per_item,
       total_cost,
       delete_date,
       deleted_by)
    VALUES
     ( :old.order_id,
       :old.quantity,
       :old.cost_per_item,
       :old.total_cost,
       sysdate,
       v_username );

END;