This Oracle tutorial explains how to create an AFTER UPDATE Trigger in Oracle with syntax and examples.
An AFTER UPDATE Trigger capacity that Oracle will furnace this trigger after the UPDATE operation is executed.
The syntax to create an AFTER UPDATE Trigger in Oracle/PLSQL is:
CREATE [ OR REPLACE ] TRIGGER trigger_name AFTER UPDATE ON table_name [ FOR EACH ROW ] DECLARE -- variable declarations BEGIN -- trigger code EXCEPTION WHEN ... -- exception handling END;
Parameters or Arguments
OR REPLACE Optional. If specified, it permits you to re-create the trigger is it already exists so that you can exchange the set off definition without issuing a DROP TRIGGER statement. trigger_name The name of the set off to create. AFTER UPDATE It suggests that the trigger will fireplace after the UPDATE operation is executed. table_name The identify of the desk that the trigger is created on.
You can now not create an AFTER trigger on a view. You can now not update the :NEW values. You can now not replace the :OLD values.
See additionally how to create AFTER DELETE, AFTER INSERT, BEFORE DELETE, BEFORE INSERT, and BEFORE UPDATE triggers. See also how to drop a trigger.
Let’s appear at an instance of how to create an AFTER UPDATE set off the use of the CREATE TRIGGER statement.
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 ought to then use the CREATE TRIGGER announcement to create an AFTER UPDATE trigger as follows:
TIP: When using SQLPlus, you want to enter curb on a new line after the trigger. Otherwise, the script won’t execute.
CREATE OR REPLACE TRIGGER orders_after_update AFTER UPDATE ON orders FOR EACH ROW DECLARE v_username varchar2(10); BEGIN -- Find username of person performing UPDATE into table SELECT user INTO v_username FROM dual; -- Insert record into audit table INSERT INTO orders_audit ( order_id, quantity_before, quantity_after, username ) VALUES ( :new.order_id, :old.quantity, :new.quantity, v_username ); END; /