AFTER DELETE Trigger

最新推荐文章于 2024-07-28 21:42:00 发布

最新推荐文章于 2024-07-28 21:42:00 发布 · 657 阅读

文章标签：

#Oracle #SQL

Oracle 专栏收录该内容

35 篇文章

订阅专栏

本文介绍了Oracle中AFTERDELETE触发器的概念与用法，包括其语法结构及限制条件，并通过一个具体的订单表删除触发器示例说明如何实现数据审计。

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;