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;

 

 

再分享一下我老师大神的人工智能教程吧。零基础！通俗易懂！风趣幽默！还带黄段子！希望你也加入到我们人工智能的队伍中来！